Sql (on Oracle) aging report by days

Question

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;

Answer 1

you can use this simple approach to get the report for all days(without total):

select 
'aging file to submit '|| trunc(dist/2)*2 ||'-'|| (trunc(dist/2)*2+2) || ' days: ' ||  count(*)
from (
      select trunc(sysdate) - trunc(DUE_DATE) as dist
      from FILES_TO_SUBMIT 
      --where trunc(DUE_DATE) > trunc(sysdate) -10
)
group by trunc(dist/2)
order by trunc(dist/2);

The only thing that is important is just number of days (dist(ance) field).

If you want to have also the Total in the same scan:

select 
'aging file to submit '|| 
 case 
    when trunc(dist/2) is null 
    then 'Total ' 
    else trunc(dist/2)*2 ||'-'|| (trunc(dist/2)*2+2) || ' days: ' 
 end  ||  
 count(*)
from (
      select trunc(sysdate) - trunc(DUE_DATE) as dist
      from FILES_TO_SUBMIT 
      where trunc(DUE_DATE) > trunc(sysdate) -10
)
group by rollup(trunc(dist/2))
order by trunc(dist/2)
nulls first;

Hint: If you have hundreds of days of history an index would be useful. (pay attention: if your table is very big, >100Milion, the creation of the index will take some time)

create index index_name on files_to_submit(due_date);

and then change the condition to:

where DUE_DATE > trunc(sysdate) - 10

This will speed up y

Answer 2

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

Answer 3

I got different counts using your sample data -- I get 19 total instead of 17 (which seems appropriate as only one of the 20 records in your sample data is out of the range):

WITH d1 AS (
    SELECT 2 AS day_cnt FROM dual
     UNION ALL
    SELECT 4 FROM dual
     UNION ALL
    SELECT 6 FROM dual
     UNION ALL
    SELECT 8 FROM dual
     UNION ALL
    SELECT 10 FROM dual
)
SELECT NVL(title, 'aging file to submit total') AS title, COUNT(DISTINCT file_id)
  FROM (
    SELECT 'aging file to submit ' || prev_day || '-' || day_cnt || ' days' AS title, f1.file_id
      FROM (
        SELECT day_cnt, NVL(LAG(day_cnt) OVER ( ORDER BY day_cnt ), 0) AS prev_day
          FROM d1
    ) d2, files_to_submit f1
     WHERE TRUNC(f1.due_date) <= TRUNC(SYSDATE - d2.prev_day)
       AND TRUNC(f1.due_date) >= TRUNC(SYSDATE - d2.day_cnt)
) GROUP BY ROLLUP(title);

Also, the counts for the day ranges aren't right (they don't add up to 19, that is) because of the the files can be counted twice due to the use of TRUNC() and including both end cases. But I'm sure you can tweak the above to give what you want.