Lag
| acct_id | credit_limit | effectivestartdate |
| 5678 | 0 | 31/05/2013 23:18 |
| 5678 | 200 | 1/09/2013 10:45 |
| 5678 | 0 | 30/10/2017 11:16 |
| 123456 | 0 | 7/04/2013 14:27 |
| 123456 | 200 | 22/04/2013 16:53 |
| 123456 | 500 | 4/01/2017 8:08 |
| 123456 | 0 | 29/11/2017 15:51 |
select acct_id,
credit_limit,
EffectiveStartDate,
isnull(lag(effectivestartdate) OVER (partition by acct_id order by effectivestartdate desc
), '9999-12-31') as EffectiveEndDate
into #temp
from
(select acct_id, credit_limit,
effectivestartdate
from
CDWOperationalImage.dbo.tacct_aud
where
acct_id in (123456, 5678)
) x
select * from #temp
select * from #temp
where '2017-02-17' between EffectiveStartDate and
EffectiveEndDate
or '2018-02-17' between EffectiveStartDate and
EffectiveEndDate
Output
| acct_id | credit_limit | EffectiveStartDate | EffectiveEndDate |
| 123456 | 0 | 29/11/2017 15:51 | 31/12/9999 0:00 |
| 123456 | 500 | 4/01/2017 8:08 | 29/11/2017 15:51 |
| 5678 | 0 | 30/10/2017 11:16 | 31/12/9999 0:00 |
| 5678 | 200 | 1/09/2013 10:45 | 30/10/2017 11:16 |
