ok, I did some tests based on my assumptions...
You can make it easier if you use CTE (Common Table Expression) and recursive query to generate the desired range timestamps.
Example... this query bellow, will generate ALL hourly timestamps between 2019-03-10 00:00 and 2019-03-16 00:00
Code:
WITH BASETIME( BASETS ) AS
(
VALUES
(TIMESTAMP('2019-03-10-00.00.00'))
UNION ALL
SELECT
(BASETS + 1 HOUR)
FROM BASETIME
WHERE BASETS <= '2019-03-15-24.00.00'
)
SELECT * FROM BASETIME
Will produce the following result-set :
Code:
BASETS
---------------------
2019-03-10 00:00:00.0
2019-03-10 01:00:00.0
2019-03-10 02:00:00.0
...
2019-03-15 21:00:00.0
2019-03-15 22:00:00.0
2019-03-15 23:00:00.0
2019-03-16 00:00:00.0
As I dont have your transactions table data, I also "simulated" some sample transactions data, based on coluns from your 1st post..
Code:
WITH TRANSACTIONS (ID, STARTDATE, STARTTIME, STARTHR, STOPDATE, STOPTIME, STOPHR) AS
( VALUES ( 1, '2019-03-09', '22:45', 22, '2019-03-10', '00:45', 0) ,
( 2, '2019-03-10', '22:45', 22, '2019-03-11', '01:45', 1) ,
( 3, '2019-03-11', '22:45', 22, '2019-03-12', '02:45', 2) ,
( 4, '2019-03-12', '22:45', 22, '2019-03-13', '03:45', 3) ,
( 5, '2019-03-13', '22:45', 22, '2019-03-14', '04:45', 4) ,
( 6, '2019-03-14', '22:45', 22, '2019-03-15', '05:45', 5) ,
( 7, '2019-03-15', '22:45', 22, '2019-03-16', '06:45', 6) ,
( 8, '2019-03-16', '22:45', 22, '2019-03-17', '07:45', 7) ,
( 9, '2019-03-17', '22:45', 22, '2019-03-18', '08:45', 8) ,
(10, '2019-03-18', '22:45', 22, '2019-03-19', '09:45', 9)
)
SELECT * FROM TRANSACTIONS ;
this will result in the following result-set:
Code:
ID STARTDATE STARTTIME STARTHR STOPDATE STOPTIME STOPHR
-- ---------- --------- ------- ---------- -------- ------
1 2019-03-09 22:45 22 2019-03-10 00:45 0
2 2019-03-10 22:45 22 2019-03-11 01:45 1
3 2019-03-11 22:45 22 2019-03-12 02:45 2
4 2019-03-12 22:45 22 2019-03-13 03:45 3
5 2019-03-13 22:45 22 2019-03-14 04:45 4
6 2019-03-14 22:45 22 2019-03-15 05:45 5
7 2019-03-15 22:45 22 2019-03-16 06:45 6
8 2019-03-16 22:45 22 2019-03-17 07:45 7
9 2019-03-17 22:45 22 2019-03-18 08:45 8
10 2019-03-18 22:45 22 2019-03-19 09:45 9
All of them stating at 22:45 in a particular day, and ending at some time on next day.
Now, you join both together:
Code:
WITH BASETIME( BASETS ) AS
(
-- SELECT
VALUES
(TIMESTAMP('2019-03-10-00.00.00'))
UNION ALL
SELECT
(BASETS + 1 HOUR)
FROM BASETIME
WHERE BASETS <= '2019-03-15-24.00.00'
) ,
TRANSACTIONS (ID, STARTDATE, STARTTIME, STARTHR, STOPDATE, STOPTIME, STOPHR) AS
( VALUES ( 1, '2019-03-09', '22:45', 22, '2019-03-10', '00:45', 0) ,
( 2, '2019-03-10', '22:45', 22, '2019-03-11', '01:45', 1) ,
( 3, '2019-03-11', '22:45', 22, '2019-03-12', '02:45', 2) ,
( 4, '2019-03-12', '22:45', 22, '2019-03-13', '03:45', 3) ,
( 5, '2019-03-13', '22:45', 22, '2019-03-14', '04:45', 4) ,
( 6, '2019-03-14', '22:45', 22, '2019-03-15', '05:45', 5) ,
( 7, '2019-03-15', '22:45', 22, '2019-03-16', '06:45', 6) ,
( 8, '2019-03-16', '22:45', 22, '2019-03-17', '07:45', 7) ,
( 9, '2019-03-17', '22:45', 22, '2019-03-18', '08:45', 8) ,
(10, '2019-03-18', '22:45', 22, '2019-03-19', '09:45', 9)
)
SELECT * FROM BASETIME LEFT JOIN TRANSACTIONS ON (
BASETS BETWEEN TIMESTAMP(STARTDATE, STARTHR || ':00' ) AND TIMESTAMP(STOPDATE, STOPHR || ':00') )
This now, will produce the following result-set :
Code:
BASETS ID STARTDATE STARTTIME STARTHR STOPDATE STOPTIME STOPHR
--------------------- ---- ---------- --------- ------- ---------- -------- ------
2019-03-10 00:00:00.0 1 2019-03-09 22:45 22 2019-03-10 00:45 0
2019-03-10 01:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 02:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 03:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 04:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 05:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 06:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 07:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 08:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 09:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 10:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 11:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 12:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 13:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 14:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 15:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 16:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 17:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 18:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 19:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 20:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 21:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-10 22:00:00.0 2 2019-03-10 22:45 22 2019-03-11 01:45 1
2019-03-10 23:00:00.0 2 2019-03-10 22:45 22 2019-03-11 01:45 1
2019-03-11 00:00:00.0 2 2019-03-10 22:45 22 2019-03-11 01:45 1
2019-03-11 01:00:00.0 2 2019-03-10 22:45 22 2019-03-11 01:45 1
2019-03-11 02:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 03:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 04:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 05:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 06:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 07:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 08:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 09:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 10:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 11:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 12:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 13:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 14:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 15:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 16:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 17:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 18:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 19:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 20:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 21:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-11 22:00:00.0 3 2019-03-11 22:45 22 2019-03-12 02:45 2
2019-03-11 23:00:00.0 3 2019-03-11 22:45 22 2019-03-12 02:45 2
2019-03-12 00:00:00.0 3 2019-03-11 22:45 22 2019-03-12 02:45 2
2019-03-12 01:00:00.0 3 2019-03-11 22:45 22 2019-03-12 02:45 2
2019-03-12 02:00:00.0 3 2019-03-11 22:45 22 2019-03-12 02:45 2
2019-03-12 03:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 04:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 05:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 06:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 07:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 08:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 09:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 10:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 11:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 12:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 13:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 14:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 15:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 16:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 17:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 18:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 19:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 20:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 21:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-12 22:00:00.0 4 2019-03-12 22:45 22 2019-03-13 03:45 3
2019-03-12 23:00:00.0 4 2019-03-12 22:45 22 2019-03-13 03:45 3
2019-03-13 00:00:00.0 4 2019-03-12 22:45 22 2019-03-13 03:45 3
2019-03-13 01:00:00.0 4 2019-03-12 22:45 22 2019-03-13 03:45 3
2019-03-13 02:00:00.0 4 2019-03-12 22:45 22 2019-03-13 03:45 3
2019-03-13 03:00:00.0 4 2019-03-12 22:45 22 2019-03-13 03:45 3
2019-03-13 04:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 05:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 06:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 07:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 08:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 09:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 10:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 11:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 12:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 13:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 14:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 15:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 16:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 17:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 18:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 19:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 20:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 21:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-13 22:00:00.0 5 2019-03-13 22:45 22 2019-03-14 04:45 4
2019-03-13 23:00:00.0 5 2019-03-13 22:45 22 2019-03-14 04:45 4
2019-03-14 00:00:00.0 5 2019-03-13 22:45 22 2019-03-14 04:45 4
2019-03-14 01:00:00.0 5 2019-03-13 22:45 22 2019-03-14 04:45 4
2019-03-14 02:00:00.0 5 2019-03-13 22:45 22 2019-03-14 04:45 4
2019-03-14 03:00:00.0 5 2019-03-13 22:45 22 2019-03-14 04:45 4
2019-03-14 04:00:00.0 5 2019-03-13 22:45 22 2019-03-14 04:45 4
2019-03-14 05:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 06:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 07:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 08:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 09:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 10:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 11:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 12:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 13:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 14:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 15:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 16:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 17:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 18:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 19:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 20:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 21:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-14 22:00:00.0 6 2019-03-14 22:45 22 2019-03-15 05:45 5
2019-03-14 23:00:00.0 6 2019-03-14 22:45 22 2019-03-15 05:45 5
2019-03-15 00:00:00.0 6 2019-03-14 22:45 22 2019-03-15 05:45 5
2019-03-15 01:00:00.0 6 2019-03-14 22:45 22 2019-03-15 05:45 5
2019-03-15 02:00:00.0 6 2019-03-14 22:45 22 2019-03-15 05:45 5
2019-03-15 03:00:00.0 6 2019-03-14 22:45 22 2019-03-15 05:45 5
2019-03-15 04:00:00.0 6 2019-03-14 22:45 22 2019-03-15 05:45 5
2019-03-15 05:00:00.0 6 2019-03-14 22:45 22 2019-03-15 05:45 5
2019-03-15 06:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 07:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 08:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 09:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 10:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 11:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 12:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 13:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 14:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 15:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 16:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 17:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 18:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 19:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 20:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 21:00:00.0 NULL NULL NULL NULL NULL NULL NULL
2019-03-15 22:00:00.0 7 2019-03-15 22:45 22 2019-03-16 06:45 6
2019-03-15 23:00:00.0 7 2019-03-15 22:45 22 2019-03-16 06:45 6
2019-03-16 00:00:00.0 7 2019-03-15 22:45 22 2019-03-16 06:45 6
This is what I understood from your post.. Let me know if I didnt get it right...
Oh, of course, you need to change it a litte bit, as you have your real transaction table ... basically, you don't need the 2nd temporary table definition (after the "," character in my sample.
Another change, is that my sample has only completed transactions.. but I am sure you can change the join expression to count the transactions that are still running ...
Last Note: I tested this with DB2 (dont have MSSQL installed here), but from what I searched the web, it should work ! If not, I guess minimal changes only will be required.
good luck
Regards