Sep 8, 2015

Distinct multiple rows by clubbing relevant rows

  CREATE TABLE SDR_HIST
   ( "ID" NUMBER,
"REQ_DATE" DATE,
"RES_DATE" DATE
   )  ;
 
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (22322,to_date('15-JAN-15','DD-MON-RR'),null);
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (22322,null,to_date('16-JAN-15','DD-MON-RR'));
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (22322,to_date('16-JAN-15','DD-MON-RR'),null);
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (22322,null,to_date('18-JAN-15','DD-MON-RR'));
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (22322,to_date('18-JAN-15','DD-MON-RR'),null);
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (22322,null,to_date('20-JAN-15','DD-MON-RR'));
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (175276,null,to_date('09-DEC-14','DD-MON-RR'));
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (178693,null,to_date('12-FEB-15','DD-MON-RR'));
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (179016,to_date('26-NOV-14','DD-MON-RR'),null);
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (179016,null,to_date('26-NOV-14','DD-MON-RR'));
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (179016,to_date('26-NOV-14','DD-MON-RR'),null);
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (179016,null,to_date('27-NOV-14','DD-MON-RR'));

Data at glance
===============














Required Output
===============










Query to get the required output
================================
SELECT id,  MAX(req_date),  MAX(res_date)
FROM
  (SELECT id,    req_date,    res_date,
    COALESCE (
    CASE       WHEN req_date IS NOT NULL      THEN rn1    END,
    CASE      WHEN res_date IS NOT NULL      THEN rn2    END )rn
  FROM
    (SELECT id,      req_date,      res_date,
      row_number() over (partition BY ID,
      CASE         WHEN res_date IS NULL         THEN 1      END order by id,rownum) rn1,
      row_number() over (partition BY ID,
      CASE         WHEN req_date IS NULL        THEN 1      END order by id,ROWNUM) rn2
    FROM SDR_HIST
    WHERE req_date IS NULL    OR res_date    IS NULL
    )
  )
GROUP BY rn,
  id
ORDER BY 1,2,3;

No comments:

Post a Comment