Hi all,
I have a table similar to an invoice table. Sometime like this:
create table invoices
(
id number(10),
creation_date date,
classification varchar2(10),
origin varchar2(10)
);
insert into invoices(id, creation_date, classification, origin) values(1, TO_DATE('02-09-2024', 'DD/MM/YYYY'), 'FIN', 'MANUAL');
insert into invoices(id, creation_date, classification, origin) values(2, TO_DATE('04-09-2024', 'DD/MM/YYYY'), 'FIN', 'MANUAL');
insert into invoices(id, creation_date, classification, origin) values(3, TO_DATE('04-09-2024', 'DD/MM/YYYY'), 'HR', 'AUTO');
insert into invoices(id, creation_date, classification, origin) values(4, TO_DATE('13-09-2024', 'DD/MM/YYYY'), 'IT', 'AUTO');
insert into invoices(id, creation_date, classification, origin) values(5, TO_DATE('13-09-2024', 'DD/MM/YYYY'), 'LOG', 'SAP');
insert into invoices(id, creation_date, classification, origin) values(6, TO_DATE('13-09-2024', 'DD/MM/YYYY'), 'LOG', 'SAP');
I wonder if we could easily have a result per day, classification and origin.
Something like this:
For each day where we have no invoices (like 01/09), I need a record per classification and origin and 0 as count.
And for days were we have an invoice, it needs to be counted.
Tis query provides the result. But maybe there is a better way to do?
WITH get_calendar AS
(
SELECT TRUNC(SYSDATE, 'MONTH') + LEVEL - 1 AS day
FROM dual
CONNECT BY LEVEL <= ((SYSDATE + 1) - TRUNC(SYSDATE, 'MONTH'))
),
get_class AS (
SELECT DISTINCT classification FROM invoices
),
get_origin AS (
SELECT DISTINCT origin FROM invoices
)
SELECT c.day, a.classification, o.origin, COUNT(i.id) AS count
FROM get_calendar c
CROSS JOIN get_class a
CROSS JOIN get_origin o
LEFT JOIN invoices i ON i.creation_date = c.day AND i.classification = a.classification AND i.origin = o.origin
GROUP BY c.day, a.classification, o.origin;
I first created a calendar with all the days and use it as the driven table in my query. Then I extracted the classification and origins and I did a cross join. That returns the total number of records that must be in my result. And, I used that to JOIN with the invoice table.
I returns the result, I get something like this (unsorted but number of records is ok).
DAY CLASSIFICATION ORIGIN COUNT
---------- -------------- ------ -----
01/09/2024 FIN MANUAL 0
01/09/2024 FIN AUTO 0
01/09/2024 FIN SAP 0
01/09/2024 HR MANUAL 0
01/09/2024 HR AUTO 0
01/09/2024 HR SAP 0
01/09/2024 IT MANUAL 0
01/09/2024 IT AUTO 0
01/09/2024 IT SAP 0
01/09/2024 LOG MANUAL 0
01/09/2024 LOG AUTO 0
01/09/2024 LOG SAP 0
...
02/09/2024 FIN MANUAL 1
02/09/2024 FIN AUTO 0
02/09/2024 FIN SAP 0
02/09/2024 HR MANUAL 0
02/09/2024 HR AUTO 0
02/09/2024 HR SAP 0
02/09/2024 IT MANUAL 0
02/09/2024 IT AUTO 0
02/09/2024 IT SAP 0
02/09/2024 LOG MANUAL 0
02/09/2024 LOG AUTO 0
02/09/2024 LOG SAP 0
...
04/09/2024 FIN MANUAL 1
04/09/2024 FIN AUTO 0
04/09/2024 FIN SAP 0
04/09/2024 HR MANUAL 0
04/09/2024 HR AUTO 1
04/09/2024 HR SAP 0
04/09/2024 IT MANUAL 0
04/09/2024 IT AUTO 0
04/09/2024 IT SAP 0
04/09/2024 LOG MANUAL 0
04/09/2024 LOG AUTO 0
04/09/2024 LOG SAP 0
...
13/09/2024 FIN MANUAL 0
13/09/2024 FIN AUTO 0
13/09/2024 FIN SAP 0
13/09/2024 HR MANUAL 0
13/09/2024 HR AUTO 0
13/09/2024 HR SAP 0
13/09/2024 IT MANUAL 0
13/09/2024 IT AUTO 1
13/09/2024 IT SAP 0
13/09/2024 LOG MANUAL 0
13/09/2024 LOG AUTO 0
13/09/2024 LOG SAP 2
Is there a way to do this in a better way? I mean in a more efficient way? I'm on oracle 21c.