Home » SQL & PL/SQL » SQL & PL/SQL » grouping of records (Oracle 11.2.0.3)
grouping of records [message #663768] |
Sat, 17 June 2017 00:44 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
I have two tables t_file_dtls, t_rec_dtls.
t_file_dtls gives you the file_name and number of records received in the file_name
i.e.
create table t_file_dtls (file_name varchar2(20), rec_cnt number);
Other table t_rec_dtls contains only those file_names and individual record ids which are failed during processing and record start date and record fixed date ( substraction of these gives you the days to resolve a record)
i.e.
create table t_rec_dtls (file_name varchar2(20),rec_id number, strt_dt date, end_dt date);
insert into t_file_dtls values ('accredo', 20);
insert into t_file_dtls values ('briova', 10);
insert into t_file_dtls values ('covance', 30);
insert into t_rec_dtls values ('accredo', 1,sysdate-5, sysdate);
insert into t_rec_dtls values ('accredo', 4,sysdate-10, sysdate);
insert into t_rec_dtls values ('accredo', 5,sysdate-15, sysdate);
insert into t_rec_dtls values ('accredo', 10,sysdate-20, sysdate);
insert into t_rec_dtls values ('briova', 1,sysdate-5, sysdate);
insert into t_rec_dtls values ('briova', 2,sysdate-10, sysdate);
insert into t_rec_dtls values ('briova', 3,sysdate-15, sysdate);
commit;
Now, I would like to show a report which tells me file name, number of records received in the file, records resolved with in 5 days, records resolved with in 5 to 10 days and records resolved with in 10 to 20 days.
If a file_name does not exists in the t_rec_dtls then we have to assume that all the records in the file_name are resolved with in 0 to 5 days.
and also, number of non error records from t_file_dtls should also go in records resolved with in 5 days.
I have written something like below, which works as expected.
Could you please show other ways of doing it.
SELECT q.file_name,
q.rec_cnt,
q.rec_cnt - (q.five_10_days + q.ten_20_days) zero_5_days,
q.five_10_days,
q.ten_20_days
FROM ( SELECT f.file_name,
f.rec_cnt,
SUM (
CASE
WHEN r.file_name IS NULL THEN f.rec_cnt
ELSE CASE WHEN no_days <= 5 THEN 1 ELSE 0 END
END)
zero_5_days,
SUM (CASE WHEN no_days BETWEEN 6 AND 10 THEN 1 ELSE 0 END)
five_10_days,
SUM (CASE WHEN no_days BETWEEN 11 AND 20 THEN 1 ELSE 0 END)
ten_20_days
FROM t_file_dtls f,
(SELECT file_name, (TRUNC (end_dt) - TRUNC (strt_dt)) no_days
FROM t_rec_dtls) r
WHERE f.file_name = r.file_name(+)
GROUP BY f.file_name, f.rec_cnt) q
Output:
FILE_NAME REC_CNT ZERO_5_DAYS FIVE_10_DAYS TEN_20_DAYS
-------------------- ---------- ----------- ------------ -----------
briova 10 8 1 1
covance 30 30 0 0
accredo 20 17 1 2
Thank you in advance.
Regards,
Pointers
|
|
|
Re: grouping of records [message #663770 is a reply to message #663768] |
Sat, 17 June 2017 01:51 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> with
2 counts as (
3 select file_name,
4 count(case when end_dt-strt_dt > 5 and end_dt-strt_dt <= 10 then file_name end) FIVE_10_DAYS,
5 count(case when end_dt-strt_dt > 10 and end_dt-strt_dt <= 20 then file_name end) TEN_20_DAYS,
6 count(case when end_dt-strt_dt > 20 then file_name end) OVER_TWENTY_DAYS
7 from t_rec_dtls
8 group by file_name
9 )
10 select f.file_name, f.rec_cnt,
11 nvl2(c.file_name, f.rec_cnt-c.FIVE_10_DAYS-c.TEN_20_DAYS-c.OVER_TWENTY_DAYS, f.rec_cnt) ZERO_5_DAYS,
12 nvl2(c.file_name, c.FIVE_10_DAYS, 0) FIVE_10_DAYS,
13 nvl2(c.file_name, c.TEN_20_DAYS, 0) TEN_20_DAYS
14 from t_file_dtls f left join counts c on c.file_name = f.file_name
15 order by 1
16 /
FILE_NAME REC_CNT ZERO_5_DAYS FIVE_10_DAYS TEN_20_DAYS
-------------------- ---------- ----------- ------------ -----------
accredo 20 17 1 2
briova 10 8 1 1
covance 30 30 0 0
This assumes that "file_name" is a PK of "t_file_dtls".
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:37:57 CDT 2024
|