Create hourly count for multiple days with separate columns that each only has date and time

Question

I have a server that contains a column with date only(datetime) and the other with time only(char), but I'm trying to create hourly counts with the code below but it's not showing up as expected:

    SELECT 
        account_date as Date,
        sum(qty) as qty
        sum(amount) as Amount,
        cast(datepart(hour,time) as varchar)
    FROM [data source]
    WHERE account_date between '10/1/2022' and '10/5/2022'
    GROUP BY account_date, time

I'm expecting something like this:

enter image description here

Here's the data format for date and time columns:

**Date**
2022-10-01 00:00:00.000
2022-10-02 00:00:00.000
2022-10-03 00:00:00.000
2022-10-04 00:00:00.000
**Time**
0709
0707
0707
0707

Answer 1

You're looking for a conditional aggregation or a PIVOT.

Select account_date
      ,[0000] = sum(case when HH='00' then qty else 0 end)
      ,[0100] = sum(case when HH='01' then qty else 0 end)
      ,[0200] = sum(case when HH='02' then qty else 0 end)
      -- ... add more columns
      ,[2300] = sum(case when HH='23' then qty else 0 end)
From   YourTable
Cross Apply ( values ( left([Time],2) ) ) B(HH)
Where  account_date between '2022-10-01' and '2022-10-05'
Group By account_date