CODEWITHSUNDEEP
sqldatedb2
Sabarish Bapu
Sabarish Bapu

Reputation: 5

Finding rows which don't have continuous date range in db2

I have data like the following and there are millions of rows like this

MBR     MBR_SPAN     EFF_DT    END_DT           
1   B   1/1/2011    12/31/2011
1   C   1/1/2012    12/31/2012
1   A   2/1/2013    12/31/2013
2   D   1/1/2010    12/31/2010
2   X   1/1/2011    12/31/ 2011

I need to find the row for each member where it is not continuous with the previous date range. In this case it is MBR 1 and MBR_SPAN A

I don't have a column which is continuous to sort and determine which should have continous date range. It has to be determined by comparing previous row (May be by sorting eff_dt)

Also it has to be done without creating any temp table as i dont have access to create tables in db2.

Can anyone help?

Upvotes: 0

Views: 112

Answers (3)

Esperento57
Esperento57

Reputation: 17492

Other method :

with tmp as 
(
  select f1.*, rownumber() over (partition by f1.mbr order by f1.eff_dt, f1.END_DT) as rang
  from yourtablename f1
)
select f1.* from tmp f1 
inner join tmp f2 on f1.mbr=f2.mbr and f1.rang=f2.rang-1 and f1.eff_dt + 1 day <> f2.eff_dt

Upvotes: 0

mao
mao

Reputation: 12287

Variation: suppose your table is called 'mydate' and you want a single-row result set from the above sample data:
select * from (select t.*, lag(end_dt) over (partition by mbr order by eff_dt) as prev_end_dt from mydate as t ) x where x.eff_dt <> x.prev_end_dt + 1 day and x.prev_end_dt is not null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Here is one method:

select *
from (select t.*,
             lag(end_dt) over (partition by mbr order by eff_dt) as prev_end_dt
      from t
     ) t
where end_dt <> prev_end_dt + 1 day and prev_end_dte is not null;

Upvotes: 1

Related Questions