We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Return 0 for hours with no sales

Medium Priority
296 Views
Last Modified: 2012-05-07
I've got the following query:


SELECT DATEPART(hour, TimeStamp) AS Hour
, SUM(cast(Amount as money)) AS Sales
FROM billingTransactions
WHERE DATEADD(day, 0, DATEDIFF(day, 0, TimeStamp)) = '07/06/2009'
GROUP BY DATEPART(hour, TimeStamp)
ORDER BY hour

I want it to return a 0 for hours where there are no sales.....  This works well, but only returns those hours with actual sales.
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
;with cte (i) as (
Select 1 as i
union all
select i+1 as i
from cte )

SELECT i AS Hour
, ISNULL( SUM(cast(Amount as money)),0) AS Sales
FROM billingTransactions
LEFT Join CTE c ON c.i = DATEPART(hour, TimeStamp)
WHERE DATEADD(day, 0, DATEDIFF(day, 0, TimeStamp)) = '07/06/2009'
GROUP BY DATEPART(hour, TimeStamp)
order by hour
OPTION (MAXRECURSION 24)

Author

Commented:
Not working... First it said that cte.i not in group by, when I added it, we got max recursions...

Author

Commented:
The statement terminated. The maximum recursion 24 has been exhausted before statement completion.
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
SELECT CTE.HR, ISNULL(SS.TOTAL,0) AS TOTAL
FROM (
SELECT DISTINCT DATEPART(hour, TimeStamp) AS HR
FROM billingTransactions
) CTE
LEFT OUTER JOIN (
SELECT DATEPART(hour, TimeStamp) AS HR, SUM(CAST(Amount AS money)) AS TOTAL
FROM billingTransactions
WHERE DATEADD(day, 0, DATEDIFF(day, 0, TimeStamp)) = '07/06/2009'
GROUP BY DATEPART(hour, TimeStamp)
) SS
ON CTE.HR = SS.HR
order by hr
Rob FarleyConsultant
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Rob FarleyConsultant
CERTIFIED EXPERT

Commented:
pazzaTim should assign some points here...
Rob FarleyConsultant
CERTIFIED EXPERT

Commented:
I think if pazzaTim found that the answer he wrote was the best, then it's fine to Accept that one as the answer, but as the Asker, he should've given the points to those that helped.

So... Accept with 50, and give 450 away to the other experts who contributed.

Rob
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.