LEFT Joins... or something else? - 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)

LEFT Joins... or something else?

Author

Topic

redherring
Starting Member

12 Posts

Posted - 2011-01-17 : 19:04:09

Hi there. I've been beating this around by myself for enough hours now that I need to ask for help.

Here's the gist of it.

I want to display a unique client count that purchased a certain type of product per day, and want to show 0's for days where applicable. (To simplify the example, just assume that i only have one month of data.)

I have:

tblDaysofMonth - dayofmonth (which holds integer values for each day of the month in question)

tblOrder - customernumber, orderdate, orderid

tblOrderDetail - orderid, inventoryID

tblInventory - inventoryID, producttype

Basically I would think something like this would work:

select tblDaysofMonth.dayofmonth, coalesce(COUNT(DISTINCT tblOrder.clientid),0) AS countofproduct

from tblDaysofMonth
left join tblOrder on datepart(day,tblOrder.orderdate) = tblDaysofMonth.dayofmonth
left JOIN tblOrderDetail ON tblOrderDetail.orderID = tblOrder.orderID
left JOIN tblInventory ON tblInventory.inventoryID = tblOrderDetail.inventoryID

and (tblInventory.producttype = 1)

group by tblDataSnapshots_DaysofMonth.dayofmonth
order by tblDataSnapshots_DaysofMonth.dayofmonth

However, this disregards the "tblInventory.producttype = 1" criteria and gives me unique client counts per day regardless of product type. (It does properly give me 0's for days with no sales at all.)

I thank you in advance for your insight.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-01-18 : 05:30:42

[code]
;WITH DayCounts
AS
(
SELECT O.orderdate
,COUNT(DISTINCT O.clientid) AS ClientCount
FROM tblOrder O
WHERE EXISTS
(
SELECT *
FROM tblOrderDetail D
WHERE D.orderID = O.orderID
AND EXISTS
(
SELECT *
FROM tblInventory I
WHERE I.inventoryID = D.inventoryID
AND I.producttype = 1
)
)
GROUP BY O.orderdate
)
SELECT D.[dayofmonth]
,COALESCE(C.ClientCount, 0) AS countofproduct
FROM tblDaysofMonth D
LEFT JOIN DayCounts C
ON D.[dayofmonth] = C.orderdate
[/code]

redherring
Starting Member

12 Posts

Posted - 2011-01-18 : 09:30:55

Hi there. Thanks so much for the reply. This is where I get smacked on the back of the head for posting this in the wrong forum...

It seems as if this syntax is incompatible with SQL 2000, which is what I am forced to work with.

Anyone able to "translate" this into something that will work with SQL 2000?

Sorry, and thanks.

redherring
Starting Member

12 Posts

Posted - 2011-01-18 : 11:14:26

fyi - this has now been resolved in the SQL 2000 forum, where I should have posted it in the first place. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources