SQL Spackle - Find 0 values in sums by date

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Articles Requested » SQL Spackle - Find 0 values in sums by date

SQL Spackle - Find 0 values in sums by date

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Thursday, March 10, 2011 9:58 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 4:46 PM
Points: 31,433, Visits: 13,745

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

Patrick Cahill

Posted Thursday, March 10, 2011 10:19 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:40 AM
Points: 2,412, Visits: 578

I'll take this one.

Post #1076410

Steve Jones - SSC Editor

Posted Thursday, March 10, 2011 10:24 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 4:46 PM
Points: 31,433, Visits: 13,745

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

Post #1076414

Jeff Moden

Posted Thursday, March 10, 2011 8:49 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910, Visits: 26,800

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1076657

Patrick Cahill

Posted Thursday, March 10, 2011 9:04 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:40 AM
Points: 2,412, Visits: 578

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

Jeff Moden

Posted Sunday, May 08, 2011 5:19 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910, Visits: 26,800

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

Post #1105168

Patrick Cahill

Posted Monday, May 09, 2011 8:18 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:40 AM
Points: 2,412, Visits: 578

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

Jeff Moden

Posted Monday, May 09, 2011 10:13 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910, Visits: 26,800

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

Post #1105531

WayneS

Posted Monday, May 09, 2011 10:38 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,370, Visits: 8,235

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

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

Jeff Moden

Posted Monday, May 09, 2011 5:38 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910, Visits: 26,800

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.

BigGrin

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! Tongue

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

--Jeff Moden

Post #1105789

« Prev Topic | Next Topic »

Permissions