Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Spackle - Find 0 values in sums by date Expand / Collapse
Author
Message
Posted Thursday, March 10, 2011 9:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
If I have a series of data recorded over time, let's call it widgets sold. If I want to know how many items were sold per hour, that's easy. However a simple SUM, GROUP BY hour, doesn't give me 0 sales.

Can someone write a short article that shows how to join with some calendar or tally table (or both) and show sales across hours like:

Hour (time) Sales
-------------- ---------
9 12
10 8
11 0
12 3
1 15
2 14


Here's some DDL


CREATE TABLE Sales
( SalesID INT IDENTITY(1,1)
, SaleTime DATETIME
, SaleQty INT
)
GO

insert sales select 'Mar 8 2011 4:21PM', 38
insert sales select 'Mar 8 2011 12:40AM', 15
insert sales select 'Mar 10 2011 1:14PM', 28
insert sales select 'Mar 9 2011 7:43PM', 41
insert sales select 'Mar 9 2011 11:16PM', 5
insert sales select 'Mar 10 2011 11:48AM', 35
insert sales select 'Mar 8 2011 2:34AM', 24
insert sales select 'Mar 8 2011 5:12PM', 37
insert sales select 'Mar 8 2011 10:04PM', 21
insert sales select 'Mar 9 2011 10:07AM', 42
insert sales select 'Mar 9 2011 4:46PM', 15
insert sales select 'Mar 10 2011 4:16PM', 34
insert sales select 'Mar 10 2011 6:00PM', 30
insert sales select 'Mar 10 2011 5:55AM', 13
insert sales select 'Mar 8 2011 7:49PM', 49
insert sales select 'Mar 10 2011 6:05PM', 27
insert sales select 'Mar 10 2011 10:53AM', 12
insert sales select 'Mar 8 2011 4:59PM', 43
insert sales select 'Mar 10 2011 2:22PM', 45
insert sales select 'Mar 9 2011 10:05PM', 1
insert sales select 'Mar 9 2011 8:11PM', 38
insert sales select 'Mar 9 2011 3:52AM', 32
insert sales select 'Mar 10 2011 2:49PM', 2
insert sales select 'Mar 10 2011 11:32AM', 8
insert sales select 'Mar 10 2011 10:52AM', 46







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1076379
Posted Thursday, March 10, 2011 10:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 2,986, Visits: 761
I'll take this one.
Post #1076410
Posted Thursday, March 10, 2011 10:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
Excellent, and I'll try to edit it in a timely manner






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1076414
Posted Thursday, March 10, 2011 8:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
Patrick Cahill (3/10/2011)
I'll take this one.


I'll be happy to provide a technical review on this one, Patrick. Drop me an email, if you'd like.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1076657
Posted Thursday, March 10, 2011 9:04 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 2,986, Visits: 761
Jeff Moden (3/10/2011)

I'll be happy to provide a technical review on this one, Patrick. Drop me an email, if you'd like.


Thanks Jeff, I'll send you an email when it is done.
Post #1076660
Posted Sunday, May 8, 2011 5:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
Patrick Cahill (3/10/2011)
Jeff Moden (3/10/2011)

I'll be happy to provide a technical review on this one, Patrick. Drop me an email, if you'd like.


Thanks Jeff, I'll send you an email when it is done.


Patrick,

Just thought I'd check because I get a lot of email and I may have missed yours. Did you ever send an email on this? I also don't see an article on the subject. Can you still do it or would you rather hand it off?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1105168
Posted Monday, May 9, 2011 8:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 2,986, Visits: 761
Jeff,

I got side tracked for the last few weeks. It is almost done. I should have something to you by the end of the week.

Thanks

Patrick
Post #1105447
Posted Monday, May 9, 2011 10:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
Patrick Cahill (5/9/2011)
Jeff,

I got side tracked for the last few weeks. It is almost done. I should have something to you by the end of the week.

Thanks

Patrick


Not a problem. I was just checking. I've got one still outstanding, myself.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1105531
Posted Monday, May 9, 2011 10:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 5,370, Visits: 9,010
Jeff Moden (5/9/2011)
I've got one still outstanding, myself.


Plus a rewrite an expansion of Tally OH! based on new findings, plus your book, plus the varchar(max) splitter, plus the Running Totals rewrite, plus...

(Don't hold your breath! - This guy is seriously overbooked...)



Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1105561
Posted Monday, May 9, 2011 5:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
WayneS (5/9/2011)
Jeff Moden (5/9/2011)
I've got one still outstanding, myself.


Plus a rewrite an expansion of Tally OH! based on new findings, plus your book, plus the varchar(max) splitter, plus the Running Totals rewrite, plus...

(Don't hold your breath! - This guy is seriously overbooked...)


Plus I'm doing 10 one man only lightning rounds for my local PASS group on the 19th
Plus I've submitted to SQLSaturday #75 on a new subject which needs to be written
Plus I've submitted a 3.5 hour session similar to lightning rounds but longer for PASS 2011 which needs to be written
Plus I've submitted a 1 hour session on a new subject to PASS which needs to be written
Plus.... etc, etc, etc.



Heh... I'm not complaining... but it's no longer a matter of how many sticks I have in the fire... it's now a matter of how many fires I have sticks in and the bloody dust bunnies took the month off!

Oh yeah... almost forgot... I actually do work for a living, as well.



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1105789
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse