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

This approach allows you to maintain your buckets separately from your main SQL in case you want them to be different sizes or to name them something that is not generated from the SQL, such as 'On time', 'Delinquent', etc. and also provides a very readable main SQL block.

with aging as
 (select count(*) count_per_day, (trunc(sysdate) - trunc(f.due_date)) age
    from files_to_submit f
    where trunc(f.due_date - 10) <= sysdate
   group by (trunc(sysdate) - trunc(f.due_date))),
buckets as
 (select 1 bucket_id, 0 bucket_min, 2 bucket_max, 'aging file to submit 0-2' bucket_name from dual
  union select 2, 2,  4, 'aging file to submit 2-4' from dual
  union select 3, 4,  6, 'aging file to submit 4-6' from dual
  union select 4, 6,  8, 'aging file to submit 6-8' from dual
  union select 5, 8, 10, 'aging file to submit 8-10' from dual
  union select 6, null, null, 'aging file to submit total' from dual
)
select nvl(b.bucket_name, (select bucket_name from buckets where bucket_id = 6)), sum(a.count_per_day) bucket_cnt
  from aging a
  join buckets b on (a.age >= b.bucket_min and a.age <= b.bucket_max)
 group by rollup(b.bucket_name)
 order by b.bucket_name nulls first;

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

WITH r (
    'aging file to submit ' Prefix,
    Total,
    Days0_2,
    Days2_4,
    Days4_6,
    Days6_8,
    Days8_10
    ) AS (
    SELECT 
        SUM(Total) Total,
        SUM(Days0_2) Days0_2,
        SUM(Days2_4) Days2_4,
        SUM(Days4_6) Days4_6,
        SUM(Days6_8) Days6_8,
        SUM(Days8_10) Days8_10
      FROM (
            SELECT 
                (CASE WHEN f.days <= 2 THEN f.num ELSE NULL END) AS Days0_2,
                (CASE WHEN f.days >= 2 AND f.days <= 4 THEN f.num ELSE NULL END) AS Days2_4,
                (CASE WHEN f.days >= 4 AND f.days <= 6 THEN f.num ELSE NULL END) Days4_6,
                (CASE WHEN f.days >= 6 AND f.days <= 8 THEN f.num ELSE NULL END) AS Days6_8,
                (CASE WHEN f.days >= 8 AND f.days <= 10 THEN f.num ELSE NULL END) AS Days8_10,
                f.num AS Total
            FROM (
                SELECT 
                    COUNT(*) AS num,
                    TRUNC(due_date) - TRUNC(SYSDATE) + 10 AS days
                FROM FILES_TO_SUBMIT t
                WHERE (TRUNC(due_date) - TRUNC(SYSDATE) + 10) >= 0
                GROUP BY TRUNC(due_date) - TRUNC(SYSDATE) + 10
            ) f
    ) s
) 
SELECT Prefix || 'Total' AS Label, Total AS Count FROM r
UNION ALL SELECT Prefix || '0-2 days',  Days0_2   FROM r
UNION ALL SELECT Prefix || '2-4 days',  Days2_4   FROM r
UNION ALL SELECT Prefix || '4-6 days',  Days4_6   FROM r
UNION ALL SELECT Prefix || '6-8 days',  Days6_8   FROM r
UNION ALL SELECT Prefix || '8-10 days', Days8_10  FROM r

It will not double count records for the Total row. Since your day ranges overlap you cannot sum up the individual counts to get the total. As another query given here gives a total of 25 with only 20 records and 1 out of range.

The result for total is what you expect with 20 records and 1 being 12 days old. The innermost query does all the heavy lifting but it is executed once to get all aging results. Its result will be at most 11 rows, 0-10 days. The rest of the queries are for final results and pretty output.

You can eliminate one query level by SUMing one level in, I just find it easier to validate the results by being able to select intermediate queries for a spot check.

Here is the result of the query:

Answer 4

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 5

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.