I need help writing a aging report on oracle. The report should be like:
aging file to submit total 17
aging file to submit 0-2 days 3
aging file to submit 2-4 days 4
aging file to submit 4-6 days 4
aging file to submit 6-8 days 2
aging file to submit 8-10 days 4
I can create a query for each section and then union all the the results like:
select 'aging file to submit total ' || count(*) from FILES_TO_SUBMIT where trunc(DUE_DATE) > trunc(sysdate) -10
union all
select 'aging file to submit 0-2 days ' || count(*) from FILES_TO_SUBMIT where trunc(DUE_DATE) <= trunc(sysdate) and trunc(DUE_DATE) >= trunc(sysdate-2)
union all
select 'aging file to submit 2-4 days ' || count(*) from FILES_TO_SUBMIT where trunc(DUE_DATE) <= trunc(sysdate-2) and trunc(DUE_DATE) >= trunc(sysdate-4) ;
I was wondering if there is a better way using oracle analytic functions or any other query that will get better performance?
Sample data:
CREATE TABLE files_to_submit(file_id int, file_name varchar(255),due_date date);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 1, 'file_' || 1, sysdate);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 2, 'file_' || 2, sysdate -5);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 3, 'file_' || 3, sysdate -4);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 4, 'file_' || 4, sysdate);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 5, 'file_' || 5, sysdate-3);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 6, 'file_' || 6, sysdate-7);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 7, 'file_' || 7, sysdate-10);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 8, 'file_' || 8, sysdate-12);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 9, 'file_' || 9, sysdate-3);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 10, 'file_' || 10, sysdate-5);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 11, 'file_' || 11, sysdate-6);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 12, 'file_' || 12, sysdate-7);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 13, 'file_' || 13, sysdate-5);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 14, 'file_' || 14, sysdate-4);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 15, 'file_' || 15, sysdate-2);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 16, 'file_' || 16, sysdate-6);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 17, 'file_' || 17, sysdate-6);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 18, 'file_' || 18, sysdate-5);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 19, 'file_' || 19, sysdate-10);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES ( 20, 'file_' || 20, sysdate-9);
DROP TABLE files_to_submit;
Allow me to suggest WIDTH_BUCKET. This will divide the date range into equal size. Since you want 10 days range into groups of 2 days, the bucket size will be 10 / 2 = 5.
Query:
SELECT
CASE GROUPING(bucket)
WHEN 1
THEN 'aging file to submit Total'
ELSE 'aging file to submit ' || (bucket-1)*2 || '-' || (bucket)*2 || ' days'
END AS bucket_number,
COUNT(1) AS files
FROM (
SELECT
WIDTH_BUCKET(due_date, sysdate, sysdate-10, 5) bucket
FROM
files_to_submit
WHERE
due_date >= sysdate-10
)
GROUP BY
ROLLUP(bucket)
ORDER BY
bucket NULLS FIRST;
Result:
BUCKET_NUMBER FILES
------------------------------------ ----------
aging file to submit Total 17
aging file to submit 0-2 days 2
aging file to submit 2-4 days 3
aging file to submit 4-6 days 6
aging file to submit 6-8 days 5
aging file to submit 8-10 days 1