SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Spackle - Find 0 values in sums by date


SQL Spackle - Find 0 values in sums by date

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63336 Visits: 19115
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
My Blog: www.voiceofthedba.com
Patrick Cahill
Patrick Cahill
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3815 Visits: 994
I'll take this one.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63336 Visits: 19115
Excellent, and I'll try to edit it in a timely manner Hehe

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87088 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Patrick Cahill
Patrick Cahill
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3815 Visits: 994
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87088 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Patrick Cahill
Patrick Cahill
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3815 Visits: 994
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87088 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9938 Visits: 10573
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... w00t

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

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
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

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87088 Visits: 41113
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... w00t

(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.

:-D

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! :-P

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

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search