Home » SQL & PL/SQL » SQL & PL/SQL » Checking date lag and merging several records into one
icon11.gif  Checking date lag and merging several records into one [message #208028] Thu, 07 December 2006 20:29 Go to next message
gentlelily
Messages: 9
Registered: December 2006
Location: St Louis, MO
Junior Member
Hi,

I think I have frustrated myself just enough that it is time to ask for some help.

I have a number of records in a table. Need to check that there is no break in rsn and that the number of days between endt and begdt of next record in the occurance is no greater than 1. Once this has been established, the records need to be "merged" into one record with the least begdt and the greatest enddt into one record. If there is a change in rsn, a new record needs to be created as well.

Data may look like this

ID RSN BEGDT ENDT
1 TBD 20061120 20061228
1 TBD 20061229 20061230
1 TBD 20061231 20070401
4 LKY 20070101 20070301

Here is what I have come up with so far for my sql:

SELECT A.ID,
A.RSN,
A.BEGDT,
A.ENDT
FROM TABLE A
UNION
SELECT
B.ID,
B.RSN,
(B.ENDT + TRUNC(1)) AS FIRSTDAY,
(B.BEGDT + TRUNC(-1)) AS LASTDAY
FROM TABLE B,
TABLE C
WHERE
B.ID = B.ID
AND B.ENDT = (SELECT MAX(ENDT)
FROM TABLE D
WHERE D.ID = C.ID
AND D.BEGDT < C.BEGDT)
AND (ROUND(TRUNC(TO_DATE(B.ENDT,'YYYYMMDD'))) -
(TRUNC(TO_DATE(C.BEGDT,'YYYYMMDD')))) > 1
ORDER BY 1,3;

I appreciate any help anyone can offer.

Thank You.

Gentle.
Re: Checking date lag and merging several records into one [message #208082 is a reply to message #208028] Fri, 08 December 2006 01:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Analytics are, in general, what you need for contiguous record problems like this:
create table contig (id  number, rsn  varchar2(3), begdt  date, endt date);

alter session set nls_Date_format = 'yyyymmdd';

insert into contig values (1, 'TBD' ,'20061120' ,'20061228');
insert into contig values (1, 'TBD' ,'20061229' ,'20061230');
insert into contig values (1, 'TBD' ,'20061231' ,'20070401');
insert into contig values (4, 'LKY' ,'20070101' ,'20070301');

select distinct 
       id
      ,rsn
      ,case when prev_endt is not null then lag(begdt) over (partition by id,rsn order by begdt)
            else begdt  end  begdt
      ,case when next_begdt is not null then lead(endt)  over (partition by id,rsn order by endt)
            else endt  end endt
from (            
select id
      ,rsn
      ,begdt
      ,endt
      ,case when lag(endt) over (partition by id,rsn order by begdt) != begdt -1 then null
            else lag(endt) over (partition by id,rsn order by begdt) end prev_endt
      ,case when lead(begdt)  over (partition by id,rsn order by endt) != endt +1 then null
            else lead(begdt)  over (partition by id,rsn order by endt) end next_begdt
from   contig)
where prev_endt is null
or    next_begdt is null;

        ID RSN BEGDT     ENDT
---------- --- --------- ---------
         1 TBD 20-NOV-06 01-APR-07
         4 LKY 01-JAN-07 01-MAR-07

Exactly the same problem was posted and solved here and there's a good long explanation of how the query works.

[Updated on: Fri, 08 December 2006 01:58]

Report message to a moderator

Re: Checking date lag and merging several records into one [message #208210 is a reply to message #208082] Fri, 08 December 2006 10:44 Go to previous message
gentlelily
Messages: 9
Registered: December 2006
Location: St Louis, MO
Junior Member
I appreciate your help immensely.

Thank You.
Previous Topic: help with dbms_aqadm package
Next Topic: Search on first 3 characters of data
Goto Forum:
  


Current Time: Sun Dec 11 00:27:51 CST 2016

Total time taken to generate the page: 0.07536 seconds