Hi again everyone,
I have another tricky query to write. I could do that using a programming language like java but I was wondering if we could generate such advanced output using SELECT statements.
Imagine I have a simple table like this one:
CREATE TABLE cal
(
start_period date,
end_period date,
cnt NUMBER
);
insert into cal values(to_date('10/04/2022 02', 'DD/MM/YYYY HH24'), to_date('10/04/2022 03', 'DD/MM/YYYY HH24'), 4); -- we don't care because it's sunday from two weeks before.
insert into cal values(to_date('11/04/2022 04', 'DD/MM/YYYY HH24'), to_date('11/04/2022 05', 'DD/MM/YYYY HH24'), 8); -- monday we are interested in this date.
insert into cal values(to_date('11/04/2022 22', 'DD/MM/YYYY HH24'), to_date('11/04/2022 23', 'DD/MM/YYYY HH24'), 5);
insert into cal values(to_date('13/04/2022 02', 'DD/MM/YYYY HH24'), to_date('13/04/2022 03', 'DD/MM/YYYY HH24'), 1);
insert into cal values(to_date('15/04/2022 00', 'DD/MM/YYYY HH24'), to_date('15/04/2022 01', 'DD/MM/YYYY HH24'), 3);
insert into cal values(to_date('18/04/2022 00', 'DD/MM/YYYY HH24'), to_date('18/04/2022 01', 'DD/MM/YYYY HH24'), 1);
insert into cal values(to_date('21/04/2022 04', 'DD/MM/YYYY HH24'), to_date('21/04/2022 05', 'DD/MM/YYYY HH24'), 1);
I would like to generate an output like a timetable. For each day, each hour, I would like to summarise what is in the table. We shouldn't have more than one row for each period. Just in case it happens, we could either SUM them or LISTAGG them.
(previous week)
WEEK_15 | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday
00 - 01 | | | | | 3 |
01 - 02 | | 1 | | | |
02 - 03 | | | | | |
03 - 04 | | | | | |
04 - 05 | 8 | | | | |
05 - 06 | | | | | |
...
22 - 23 | 5 | | | | |
23 - 00 | | | | | |
(week 16 is this week)
WEEK_16 | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday
00 - 01 | 1 | | | | |
01 - 02 | | | | | |
02 - 03 | | | | | |
03 - 04 | | | | | |
04 - 05 | | | | 1 | |
05 - 06 | | | | | |
...
22 - 23 | | | | | |
23 - 00 | | | | | |
This is quite tricky and I'm looking for some guidance on how we could achieve it if possible in SQL. Otherwise, I could do in Java.
Thank you for all your suggestions.