0

insert into dates(custid,bdate,ndate) values (5,'2013-12-01','2013-12-04'), (5,'2013-11-05','2013-12-01'), (5,'2013-11-15','2013-12-24'), (5,'2010-12-05','2012-12-04'), (5,'2009-12-05','2011-12-04') ;

I would like to get '2013-11-05','2013-12-24' since the 3 ranges interwind and '2009-12-05', '2012-12-04' since these ranges interwind as well I need the period btw the min and max of them I tried lag + lead but with no luck so far

so far got this tried to creat a flag then range it anf them max min the range.

so far with no luck

select custid,bdate,ndate, lead(bdate) OVER(PARTITION BY custid ORDER BY ndate desc, bdate desc) as lead_bdate, lead(ndate) OVER(PARTITION BY custid ORDER BY ndate desc, bdate desc) as lead_ndate, lag(bdate) OVER(PARTITION BY custid ORDER BY ndate desc, bdate desc) lag_bdate, lag(ndate) OVER(PARTITION BY custid ORDER BY ndate desc, bdate desc) as lag_ddate

            ,
            case 
            when        lead(bdate) OVER(PARTITION BY custid
   ORDER BY ndate desc, 
            bdate desc) >=bdate then 1 
            when ndate = max(ndate) OVER(PARTITION BY custid
   ORDER BY ndate desc, 
            bdate desc) then 1          

            when bdate between 
            lead(bdate) OVER(PARTITION BY custid ORDER BY ndate desc, bdate desc) 
            and lead(ndate) OVER(PARTITION BY custid ORDER BY ndate desc, bdate desc) then 1

            when ndate between 
            lag(bdate) OVER(PARTITION BY custid ORDER BY ndate desc, bdate desc) 
            and lag(ndate) OVER(PARTITION BY custid ORDER BY ndate desc, bdate desc) then 1

            else 0 end as net_seniority_Flag
             from dateso
Zohar Mamut
  • 1

0 Answers0