All Forums Database
drake_1030 1 post Joined 12/15
30 Dec 2015
Simple merging of records based on Date

Hi
I am new to Teradata.I have provided my input and desire output table structure.Kindly help to write a simple query tp achieve the output
 
Eno      Name        strt_date           end_date
1         ABC           03-15-2001        07-19-2001
2         ABC           07-20-2001        09-22-2001
3         ABC          09-23-2001         12-31-9999
 
Thus the output i require is:
Eno      Name        strt_date           end_date
1         ABC           03-15-2001        12-31-9999
 
Thus i want to combine the data into one record when the dates are continuous with no gaps as shown above.
Thanks,
Drake

sgarlapa 88 posts Joined 03/13
30 Dec 2015

Try this -
Select min(eno), name, min (strt_dt) , max(end_dt) from emptable group by 1,3,4;  -- for simple scenario of continuos dates are assured.
 
For other cases need to go with a selfjoin...will try and let you know.
 
 

manib0907 61 posts Joined 04/15
05 Jan 2016

http://manibharataraju.blogspot.in/2015/07/removing-overlaps-in-records.html
This should help

Cheers,
Mani

dnoeth 4628 posts Joined 11/04
05 Jan 2016

This will normalize both overlapping and continous periods. It looks for gaps in the Derived Table (current start date greater than maximum previous end date) and then constructs the end date in the outer Select:

SELECT Eno, Name, strt_date, 
  COALESCE(MAX(x)
           OVER (PARTITION BY Name  
                 ORDER BY strt_date
                 ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING), x2)
FROM
 (
   SELECT t.*, 
     MAX(end_date)  -- maximum previous end date
     OVER (PARTITION BY Name 
           ORDER BY strt_date,end_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS x,
     MAX(end_date) 
     OVER (PARTITION BY Name) AS x2 -- the maximum end date 
   FROM tab AS t
   QUALIFY strt_date -1 > x -- first row after a gap
           OR x IS NULL     -- first row in a partition
 ) AS dt

 

Dieter

You must sign in to leave a comment.