case when left join or inner join? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

case when left join or inner join?

Author

Topic

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-11-04 : 20:45:03

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-04 : 20:59:39

[code]
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
[/code]

KH
[spoiler]Time is always against us[/spoiler]

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-11-04 : 22:03:48

thank you khtan!

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources