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:35 pm

    #1338130

    harry9katz (6/13/2011)

    Hi

    You have to add hour to both tables

    SELECT cast(floor(cast(SaleTime as float)) as datetime)Saledate,DATEPART(hour,SaleTime) saleshour

    ,sum([SaleQty]) sumsales

    into #salessum

    FROM #SalesTestData

    group by cast(floor(cast(SaleTime as float)) as datetime),DATEPART(hour,SaleTime)

    order by cast(floor(cast(SaleTime as float)) as datetime),DATEPART(hour,SaleTime)

    here U get the first table that the article started with

    U get salesum but not on every hour

    Then U add to the #Clendertest for every day all the hours

    an now it is simple

    SELECT dbo.CalendarTest.hourid, dbo.CalendarTest.StartDate, ISNULL(SUM(dbo.saleoneday.sumsales), 0) AS sumsales

    FROM dbo.saleoneday RIGHT OUTER JOIN

    dbo.CalendarTest ON dbo.saleoneday.Saledate = dbo.CalendarTest.StartDate AND dbo.saleoneday.saleshour = dbo.CalendarTest.hourid

    GROUP BY dbo.CalendarTest.hourid, dbo.CalendarTest.StartDate

    ORDER BY dbo.CalendarTest.StartDate, dbo.CalendarTest.hourid

    Is the dbo.CalendarTest table the same as the #CalendarTest table in the article? Also, what's the dbo.saleoneday table because I can find anything named even close to that in the article and you don't build it anywhere in your code example.

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