Home Forums Article Discussions Article Discussions by Author Discuss content posted by Patrick Cahill Returning 0's in a SUM (SQL Spackle) RE: Returning 0's in a SUM (SQL Spackle)

  • June 13, 2011 at 7:01 am

    #1337726

    Matjaz Justin (6/13/2011)

    This should be a simpler solution.

    select N2.n as dan, N.n - 1 as Ura, isnull(sum(SaleQTY), 0) as D

    from dbo.GetNums(24) as N cross join dbo.GetNums(31) as N2

    left join (select datepart(day, SaleTime) as Dan, datepart(hh, SaleTime) as Ura, sum(SaleQTY) as SaleQTY

    from dbo.#SalesTestData

    group by datepart(day, SaleTime), datepart(hour, SaleTime))as D on D.Dan = N2.n and D.Ura = (N.n - 1)

    group by N2.n, N.n

    order by 1, 2

    Uncle Goole will tell you more about function GetNums ( = Virtual Auxiliary Table of Numbers).

    Hmmmm... yes, your code is simpler but your code is also calculating sums for the whole table instead of just a day or a month as was done in the article. Modify your code to handle a given month and then we'll see. 😉

    --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)