OraFAQ Forum: SQL & PL/SQL » grouping of records

Home » SQL & PL/SQL » SQL & PL/SQL » grouping of records (Oracle 11.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: grouping of records [message #663770 is a reply to message #663768]

Sat, 17 June 2017 01:51

Michel Cadot
Messages: 68641
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".

Report message to a moderator

Previous Topic:	stored proc execution based on selected value from a column
Next Topic:	Sending List to Oracle Stored Procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 15:06:26 CDT 2024