CODEWITHSUNDEEP
sqlsql-server-2008
Brandon Moore
Brandon Moore

Reputation: 8790

How to make this cumulative sales query span all 24 hours

vwSalesByHour works as expected showing only records for hours in which there were sales. Naturally, vwCumulativeDaySalesByHour also shows only records for hours in which there were sales but I need it to show a record for every hour of the day. I know there has to be a simple way to do this easier than what I've thought of so far... if someone would just be so kind as to point it out to me :)

--This works great
CREATE VIEW vwSalesByHour AS

select      SUM(t.TranAmount - t.DeliveryFee - t.TaxAmount) Sales, 
            BusinessDate,
            DATEPART(HH, OrderTime) Hour
from        transactions t
where       TranState <> 11 and TicketNumber > 0
group by    BusinessDate, DATEPART(HH, OrderTime)


--But I would like this to return records for all 24 hours
CREATE VIEW vwCumulativeDaySalesByHour
AS
select  min(s1.Sales) Sales, 
        sum(s2.Sales) CumulativeSales, 
        s1.BusinessDate, s1.Hour

from    SalesByHour s1 join 
        SalesByHour s2 on s1.BusinessDate = s2.BusinessDate and
                          s1.Hour >= s2.Hour
group by s1.BusinessDate, s1.Hour

Upvotes: 1

Views: 528

Answers (1)

MatBailie
MatBailie

Reputation: 86765

Generally, I recommend changing your design to not require this. But, if you do really need it; Create templates to join on, these ensure that you have every record that you need.

You might have a calendar table with every day pre-populated. And maybe an hourly table with 24 hours populated. Or one table composing everything you could need (year, month, week, day, hour, interval, etc, as different columns, and then with multiple covering indexes).

Even at the extreme, this normally doesn't take much space, and does drastically simplify queries and (normally) saves significant processing time.

You then get something like this...

SELECT
  calendar.date,
  hours.hour,
  SUM(SalesByHour.Sales)                                                        AS CumulativeSales,
  MAX(CASE WHEN SalesByHour.Hour = hours.hour THEN Sales.Sales ELSE NULL END)   AS Sales
FROM
  calendar
CROSS JOIN
  hours
LEFT JOIN
  SalesByHour
    ON  SalesByHour.BusinessDate = calendar.date
    AND SalesByHour.Hour        <= hours.hour
GROUP BY
  calendar.date,
  hours.hour

The alternative is just to populate the gaps in your client. But this my distribute certain logic across client and SQL. The exact best way to avoid this need is situation dependent. But I do recommend finding a way of not needing this if it is t all possible.

The same goes for the cumulative values. SQL is notoriously poor at running totals like this. If the client can process the running total instead of the SQL, you probably get decent overall performance saving.

Upvotes: 1

Related Questions