Sql report (for Oracle) by day

Question

I need help writing an oracle aging report. The report should look like this:

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 combine all the results, for example:

 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 to use oracle analytic functions or any other query that will have 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

Let me suggest WIDTH_BUCKET . This will divide the date range into equal sizes. Since you want the 10-day range to be 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 2

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

 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 matters is simply the number of days (the dist (ance) field).

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

 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 will be useful. (note: if your table is very large,> 100Milion, creating 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 

It will speed y

Answer 3

I have different calculations using your sample data. I get 19 in total instead of 17 (which seems appropriate since only one of the 20 entries in your sample data is out of 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); 

In addition, the calculations for the daily ranges are incorrect (they are not summed up to 19, that is) due to the fact that the files can be counted twice due to the use of TRUNC() and include both ends, But I'm sure you can configure the above to give what you want.

Answer 4

This approach allows you to maintain your buckets separately from the main SQL if you want them to be of different sizes or call them something that SQL is not generated, for example, On, Delinquent, etc., and also provides 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 5

 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 record double entries for a common line. Since your daily intervals overlap, you cannot summarize individual bills to get the total. As another query given here, there are only 25 with only 20 entries and 1 out of range.

The result for everything is what you expect with 20 entries, and 1 - 12 days. The innermost query does all the heavy lifting, but it runs once to get all the aging results. Its result will be no more than 11 lines, 0-10 days. The remaining queries are for final results and good results.

You can exclude one level of queries using SUMing at the same level, it’s just easier for me to check the results by choosing intermediate queries to check the place.

Here is the result of the query: