March 4, 2016 at 7:53 pm
I have some records that have over lapping dates. I need to end date them in such a way to fix
over lapping dates. See example and expected results.
declare @overlap table
(
ID int identity,
infoname char(10),
code char(10),
mods char(30),
type varchar(30),
effective date ,
termination date
)
insert into @overlap
select 'DUMMY','99205','C','*','01/01/2008','12/31/2100'
insert into @overlap
select 'DUMMY','99205','C','*','01/01/2009','12/31/2100'
insert into @overlap
select 'DUMMY','99205','C','*','01/01/2010','12/31/2100'
insert into @overlap
select 'DUMMY','99206','x','*','01/01/2011','12/31/2100'
insert into @overlap
select 'DUMMY','99206','x','*','01/01/2012','12/31/2100'
insert into @overlap
select 'DUMMY','99206','x','*','01/01/2013','12/31/2100'
SELECT * FROM @OVERLAP
/*
---------------------------
---- expected results----
---------------------------
1 DUMMY 99205 C * 2008-01-01 2008-12-31
2 DUMMY 99205 C * 2009-01-01 2009-12-31
3 DUMMY 99205 C * 2010-01-01 2100-12-31 <-unchanged
4 DUMMY 99206 x * 2011-01-01 2011-12-31
5 DUMMY 99206 x * 2012-01-01 2012-12-31
6 DUMMY 99206 x * 2013-01-01 2100-12-31 <-unchanged
*/
March 4, 2016 at 8:12 pm
This little snippet produces your desired output.
;with cte as
(
Select code, effective, termination,
row_number() over (partition by code order by effective desc) rowNum
from @overlap
)
UPDATE cte set
termination = cast(cast(year(effective) as char(4)) + '-12-31' as date)
FROM cte
WHERE rowNum > 1;
SELECT * from @overlap
There are probably a dozen different and very clever ways to use dateadd, datediff, etc. . . to get your desired results.
You can also pick the termination date apart with some other date functions if you don't want to use the literals.
This should get you going in the right direction.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 4, 2016 at 9:41 pm
Quick and simple solution with LEAD()
😎
USE tempdb;
GO
SET NOCOUNT ON;
declare @overlap table
(
ID int identity,
infoname char(10),
code char(10),
mods char(30),
type varchar(30),
effective date ,
termination date
)
insert into @overlap
select 'DUMMY','99205','C','*','01/01/2008','12/31/2100'
insert into @overlap
select 'DUMMY','99205','C','*','01/01/2009','12/31/2100'
insert into @overlap
select 'DUMMY','99205','C','*','01/01/2010','12/31/2100'
insert into @overlap
select 'DUMMY','99206','x','*','01/01/2011','12/31/2100'
insert into @overlap
select 'DUMMY','99206','x','*','01/01/2012','12/31/2100'
insert into @overlap
select 'DUMMY','99206','x','*','01/01/2013','12/31/2100'
SELECT
OL.ID
,OL.infoname
,OL.mods
,OL.type
,OL.effective
,LEAD(DATEADD(DAY,-1,OL.effective),1,OL.termination) OVER
(
PARTITION BY OL.mods
ORDER BY OL.effective
) AS termination
FROM @OVERLAP OL
;
Output
ID infoname mods type effective termination
--- ---------- ----- ----- ---------- -----------
1 DUMMY C * 2008-01-01 2008-12-31
2 DUMMY C * 2009-01-01 2009-12-31
3 DUMMY C * 2010-01-01 2100-12-31
4 DUMMY x * 2011-01-01 2011-12-31
5 DUMMY x * 2012-01-01 2012-12-31
6 DUMMY x * 2013-01-01 2100-12-31
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply