Post reply

SQL Spackle - Find 0 values in sums by date

  • March 10, 2011 at 9:58 am

    #236150

    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

  • March 10, 2011 at 10:19 am

    #1297224

    I'll take this one.

  • March 10, 2011 at 10:24 am

    #1297228

    Excellent, and I'll try to edit it in a timely manner :hehe:

  • March 10, 2011 at 8:49 pm

    #1297437

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 10, 2011 at 9:04 pm

    #1297439

    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.

  • May 8, 2011 at 5:19 pm

    #1321504

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 9, 2011 at 8:18 am

    #1321742

    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

  • May 9, 2011 at 10:13 am

    #1321804

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 9, 2011 at 10:38 am

    #1321831

    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • May 9, 2011 at 5:38 pm

    #1322038

    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.

    😀

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply