Need query for this..

Database
Enthusiast

Need query for this..

Hi  Experts,

some records are unable closed so i need to close it with proper eff_end_dt what i mention o/p below.

I/p:

Batch_ID  EFF_START_DT  EFF_END_DT  DELTA_TYPE_CD

9999         2014-01-10   2014-07-10    CLOSED

9999         2014-08-10   9999-12-31      ACTIVE

9999         2014-12-10   9999-12-31      ACTIVE

9999         2014-15-10   9999-12-31       ACTIVE

O/p:

Batch_ID  EFF_START_DT  EFF_END_DT  DELTA_TYPE_CD

9999         2014-01-10   2014-07-10    CLOSED

9999         2014-08-10   2014-11-10    CLOSED

9999         2014-12-10   2014-14-10     CLOSED

9999         2014-15-10   9999-12-31      ACTIVE




3 REPLIES
Enthusiast

Re: Need query for this..

Some of your dates are invalid so it's a little hard to follow your question...

Enthusiast

Re: Need query for this..

Hi glass,

closed (EFF_END_DT) must be after starting date(EFF_START_DT) -1.

Enthusiast

Re: Need query for this..

Hi,
I can think of something like below.
Let me know if it fits in your requirement

Sel
Batchid ,
start_dt,
case when (Max(Start_dt ) OVER ( partition by batchid order by start_dt asc rows between 1 following and 1 following ) ) is null
Then cast ('3499-12-31' as date ) else (Max(Start_dt ) OVER ( partition by batchid order by start_dt asc rows between 1 following and 1 following ) ) -1 END AS END_DT1 ,
case when END_DT1 ='3499-12-31' then 'ACTIVE' ELSE 'CLOSED' END AS STATUS

From <>