CODEWITHSUNDEEP
sqlsql-servert-sql
Subliminal Hash
Subliminal Hash

Reputation: 13742

TSQL: Get sales data for each hour

I am currently using the below code to get sales data from my database. The problem with it is that it will not show 0 for those hours that there were no sales in. I don't mind hard coding the hours but I can't find a way to do it that way. So,

1.st Question: How can I ammend the below code to display 0 for hours that had no sales within,

2.nd Question: How can I get an ordered list out of it. The below code would place 9:00 - 10:00 to the end of the list since it is not two digits..

-- HOUR BASED SALES
select (CAST(DATEPART(HOUR, INSERTDATE) as varchar(255)) + ':00 -' +
        CAST(DATEPART(HOUR, INSERTDATE) as varchar(255)) + ':59'
       ) as [time],
       SUM (TOTAL) as REVENUE,
       COUNT (RECEIPTID) AS TOTAL_SALES,
       DEPOTID
From INVOICE INV
WHERE DEPOTID IN (25) AND TRANSDATE BETWEEN '20181025' AND '20181025'
Group By cast(datepart(hour, INSERTDATE) AS VARCHAR(255)), DEPOTID
ORDER BY CAST(DATEPART(HOUR, INSERTDATE) AS VARCHAR(255))

Upvotes: 0

Views: 88

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

You can list the hours and use left join:

select (cast(v.hh as varchar(255)) + ':00 -' +
        cast(v.hh as varchar(255)) + ':59'
       ) as [time],
       sum(TOTAL) as REVENUE,
       sum(RECEIPTID) AS TOTAL_SALES,
       depot
From (values (0), (1), . . . (23)
     ) v(hh) left join
     invoice i
     on datepart(hour, i.insertdate) = v.hh and
        i.depotid in (25) and
        i.transdate between '20181025' and '20181025'
group by v.hh, DEPOTID
order by v.hh

Upvotes: 2

Related Questions