SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 case when left join or inner join?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

adbasanta
Posting Yak Master

120 Posts

Posted - 11/04/2012 :  20:45:03  Show Profile  Reply with Quote
Good day!

I have a running query that displays the summary of sales of a given date. However if salestype is a 'Bad Order' the query should look into supplier table instead of customer table. This is my query so far:


ALTER PROCEDURE [dbo].[display_sales_detail_bydate] 
@fromdate AS Varchar(max), @todate AS Varchar(max)

As
Begin
	
SELECT transdate,[Last Name] + ', ' + [First Name] + ' ' + [Middle Name] AS FullName,
       salesid,salestype,casheirid,terminal,totalsales
       ,costofsales=(SELECT SUM(sd.supprice * sd.qty) 
                     FROM dbo.tbl_pos_sales_detail sd 
                     WHERE sd.salesid=sm.salesid AND sd.salestype=sm.salestype)
                      
FROM tbl_pos_sales_summary sm

LEFT JOIN tbl_costumers tc
ON tc.[Last Name]=(SELECT tc.[Last Name]
                   WHERE tc.[ID No.] = sm.costumerid)
                                   
WHERE (sm.salestype='cash' or sm.salestype='credit' or sm.salestype='pay' OR sm.salestype='Bad Order')
--AND (sm.transdate >= @fromdate AND transdate < DATEADD(dd,1,@todate))
AND (CONVERT(Date, sm.transdate)) BETWEEN  @fromdate AND @todate
 
ORDER BY sm.transdate,sm.salesid asc

End	


If its bad order, the left join must be replace with this query:

INNER JOIN tbl_supplier sp
ON sp.suppliername=(SELECT sp.suppliername
                   WHERE sp.supplierID = sm.costumerid)


This is what i want to incorporate on the query above!

Thank you for helping!



-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Edited by - adbasanta on 11/04/2012 20:48:54

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 11/04/2012 :  20:59:39  Show Profile  Reply with Quote

SELECT transdate,[Last Name] + ', ' + [First Name] + ' ' + [Middle Name] AS FullName,
       salesid,salestype,casheirid,terminal,totalsales
       ,costofsales=(SELECT SUM(sd.supprice * sd.qty) 
                     FROM dbo.tbl_pos_sales_detail sd 
                     WHERE sd.salesid=sm.salesid AND sd.salestype=sm.salestype)
                      
FROM tbl_pos_sales_summary sm

LEFT JOIN tbl_costumers tc
ON tc.[Last Name]=(SELECT tc.[Last Name]
                   WHERE tc.[ID No.] = sm.costumerid)
                                   
WHERE (sm.salestype='cash' or sm.salestype='credit' or sm.salestype='pay' )
AND (CONVERT(Date, sm.transdate)) BETWEEN  @fromdate AND @todate

UNION ALL

SELECT transdate,[Last Name] + ', ' + [First Name] + ' ' + [Middle Name] AS FullName,
       salesid,salestype,casheirid,terminal,totalsales
       ,costofsales=(SELECT SUM(sd.supprice * sd.qty) 
                     FROM dbo.tbl_pos_sales_detail sd 
                     WHERE sd.salesid=sm.salesid AND sd.salestype=sm.salestype)
                      
FROM tbl_pos_sales_summary sm

INNER JOIN tbl_supplier sp
ON sp.suppliername=(SELECT sp.suppliername
                   WHERE sp.supplierID = sm.costumerid)
                                   
WHERE (sm.salestype='Bad Order')
AND (CONVERT(Date, sm.transdate)) BETWEEN  @fromdate AND @todate

ORDER BY sm.transdate,sm.salesid asc



KH
Time is always against us

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 11/04/2012 :  22:03:48  Show Profile  Reply with Quote
thank you khtan!

It works as expected. I did not notice using Union All.



-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000