Click here to Skip to main content
15,902,876 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi! I have at least 15 years experience with SQL but I'm drowning on this one :(
Would like to always retrieve 24 rows (from hour 0 to 23) even if there are no sales, hence the left join. I'd expect an hour with no sales to show NULL on column Sales.
So trying to retrieve Sales by Hour.

Left table:
Dim_Hours
Id - int
0
1
2
3
...
23

Right table:
Sales
DateTimeSale - datetime
Sale - int
1/3/2022 9:05, 100
1/4/2022 10:45, 200

SQL
SELECT          Dim_Hours.Id, SUM(Sales.Sale) AS Sales
FROM            Dim_Hours
LEFT OUTER JOIN Sales ON Dim_Hours.Id = DATEPART(hour, Sales.DateTimeSale)
WHERE           Sales.DateTimeSale BETWEEN '2022-1-1' AND '2022-1-31'
GROUP BY        Dim_Hours.Id
ORDER BY        Id


but I'm only getting the hours where there were sales therefore there must be something wrong with my LEFT OUTER JOIN above. Query was written using "Design Query in Editor" in SSMS. I explicitly marked "Select All Rows from Dim_Hours". Tables above were simplified for illustration purpose.
Could you please provide a hint on where I'm missing the whole point of this Left Join?
Thanks!
ST

What I have tried:

Tried query above, read lots of good articles on JOINS but still stuck. I must have written thousands of joins but this one is killing me.
Posted
Updated 7-Feb-22 0:41am

IMHO it is the WHERE clause that causes you to get only hours with sales.

I'd try something like
WHERE Sales.DateTimeSale IS NULL OR Sales.DateTimeSale BETWEEN ...


:)
 
Share this answer
 
As Luc said, the filter in the WHERE clause turns your LEFT JOIN into an INNER JOIN.

The simplest solution is to put the filter in the join conditions rather than the WHERE clause:
SQL
SELECT          Dim_Hours.Id, SUM(Sales.Sale) AS Sales
FROM            Dim_Hours
LEFT OUTER JOIN Sales 
                ON Dim_Hours.Id = DATEPART(hour, Sales.DateTimeSale)
                AND Sales.DateTimeSale BETWEEN '2022-1-1' AND '2022-1-31'
GROUP BY        Dim_Hours.Id
ORDER BY        Id
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900