I am trying to write a query that reorders date ranges around particular spans. It should do something that looks like this
Member Rank Begin Date End Date
2275 A 9/9/14 11/17/14
2275 B 9/26/14 3/24/15
2275 B 3/25/15 12/31/15
8983 A 9/16/13 3/10/15
8983 B 2/24/15 4/28/15
8983 A 4/28/15 12/31/15
and have it become
Member Rank Begin Date End Date
2275 A 9/9/14 11/17/14
2275 B 11/18/14 3/24/15
2275 B 3/25/15 12/31/15
8983 A 9/16/13 3/10/15
8983 B 3/11/15 4/27/15
8983 A 4/28/15 12/31/15
To explain further, I am looking to update the dates. There isn't much to the ranking except A > B. And there is only A and B. Date ranges with rank A should remain untouched. Overlapping B ranked dates are okay. I am concerned with B ranked dates overlapping with A ranked dates. The table is very large (~700 members) and with several different members IDs. The 2nd line (Rank B) of member 2275 changes the begin date to 11/18/15 to not overlap with the 1st line.
I am using Microsoft SQL Server 2008 R2
Thanks
LATEST EDIT: Here's what I did for pre-2012. I don't think it's the most elegant solution.
WITH a AS (
SELECT
1 AS lgoffset
, NULL AS lgdefval
, ROW_NUMBER() OVER(PARTITION BY [Member] ORDER BY [Begin Date]) AS seq
, [Member]
, [Rank]
, [Begin Date]
, [End Date]
FROM #table
)
SELECT
a.seq
, a.[Member]
, a.[Rank]
, a.[Begin Date]
, CASE
WHEN a.[Rank] = 'B' AND a.[Begin Date] <= ISNULL(aLag.[End Date], a.lgdefval)
THEN ISNULL(aLag.[End Date], a.lgdefval)
ELSE a.[Begin Date]
END AS bdate2
, a.[End Date]
INTO #b
FROM a
LEFT OUTER JOIN a aLag
ON a.seq = aLag.seq + a.lgoffset
AND a.[Member] = aLag.[Member]
ORDER BY [Member], [Begin Date];
UPDATE #table
SET #table.bdate = CASE
WHEN #table.rnk = 'B' AND #table.bdate <= (SELECT #b.bdate2 FROM #b WHERE #b.bdate2 > #b.bdate and #table.mbr = #b.mbr)
THEN dateadd(d, 1,(SELECT bdate2 FROM #b WHERE #b.bdate2 > #b.bdate and #table.mbr = #b.mbr ))
ELSE #table.bdate
END
EDIT PS: Below was my previous answer that only applies to 2012 and later.
You may want to try the following SELECT statement to see if you get the desired results and then convert to an UPDATE:
SELECT
[Member]
, [Rank]
, CASE
WHEN [Rank] = 'B' AND [Begin Date] <= LAG([End Date],1,'12/31/2030') OVER(PARTITION BY [Member] ORDER BY [Begin Date])
THEN DATEADD(d,1,LAG([End Date],1,'12/31/2030')OVER(PARTITION BY [Member] ORDER BY [Begin Date]))
ELSE [Begin Date]
END AS [Begin Date]
, [End Date]
FROM #Table
ORDER BY [Member], [Begin Date]
EDIT: So in order to update the begin date column:
UPDATE #Table
SET [Begin Date] = (SELECT
CASE
WHEN [Rank] = 'B' AND [Begin Date] <= LAG([End Date],1,'12/31/2030') OVER(PARTITION BY [Member] ORDER BY [Begin Date])
THEN DATEADD(d,1,LAG([End Date],1,'12/31/2030')OVER(PARTITION BY [Member] ORDER BY [Begin Date]))
ELSE [Begin Date]
END AS [Begin Date]
FROM #Table)
EDIT 2: Some of my code was incorrect due to not realizing the lag function needed an OVER statement, updated select statement and update statement