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 (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: Administrators
Points: 150922 Visits: 19455
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
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4002 Visits: 1005
I'll take this one.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: Administrators
Points: 150922 Visits: 19455
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 (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222526 Visits: 42003
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
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4002 Visits: 1005
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 (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222526 Visits: 42003
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
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4002 Visits: 1005
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 (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222526 Visits: 42003
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
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22374 Visits: 10657
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 (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222526 Visits: 42003
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