SQL Calculating Hourly Sales

I am trying to create a SQL report that shows hourly sales. This is from my POS system.

There’s a built in table that has basically all the data I need, I’m just struggling to calculate the sum of a column if they are in the same hour.

Table vRPTHourlySalesPerformance (stripped down to only show relevant info):

dt_when	create_hour_ordinal	c_ticketitem_net_price
2022-11-07 11:20	11	16.00
2022-11-07 12:20	11	17.00
2022-11-07 13:20	12	18.00

I’m looking for a way to output the following.

Hour	Total Sales
11	33.00
12	18.00

This is as far as I’ve gotten:

DECLARE @start_of_day   datetime;
DECLARE @now            datetime;

SET @now = getdate();
SET @start_of_day = dbo.dwf_beginofday_for_day(@now);

--Creates Sales by Hour Table
DECLARE @reportable_table AS TABLE (
    Hour                int,
    [Total Sales]       money
)

INSERT INTO @reportable_table
SELECT create_hour_ordinal AS 'Hour', c_ticketitem_net_price AS 'Total Sales'
FROM vRPTHourlySalesPerformance
WHERE dt_when >= @start_of_day AND dt_when < @now

Answer

It appears you are after a simple aggregate:

insert into @reportable_table([hour], [total Sales])
Select create_hour_ordinal, sum(c_ticketitem_net_price)
from vRPTHourlySalesPerformance
where dt_when >= @start_of_day AND dt_when < @now
group by create_hour_ordinal;

Advertisement

Answer