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 6:29 am

    #1337697

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

    Although I'm sure that Uncle Google will tell us about the GetNums, it would be real handy if you'd simply provide the URL in the future since you're the one that brought up the function. 😉

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