asked
How do I pair up 2 dates to form a single date span
Through SQL, I need to pair up dates so that they form a date span that are currently in a table that looks like this An example of how they would look like in a table would be ![alt text][1] A record with Type=Start, must correspond with a record with Type=End. The Start Date must be less than or equal to the corresponding End date. If a single Start date can correspond to multiple End dates then use the End date closest to the Start date. If multiple Start dates can correspond to a single End date then choose Start date that is the oldest. Each record can only be used once. If the record was already a paired up with another date span then it can't be used again. Thanks in advance for any advice given! The resulting output should look like something like this ![alt text][2] [1]: /storage/temp/1422-table_example.png [2]: /storage/temp/1423-output_example.png
Will it always be that a date with ID=N is less than or equal to a date with ID=N+1?
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
Here's my suggestion. I'm not sure if the hints used ensure that the rows come in the clustered index order. Some ninja-skilled sql developer might challenge this code. Please do so! :) use sandbox; GO CREATE TABLE TableWithStartAndEndDates( ID int identity(1,1) CONSTRAINT PK_TableWithStartAndEndDates PRIMARY KEY, RecordType varchar(5) NOT NULL, RecordDate Date NOT NULL); GO INSERT TableWithStartAndEndDates(RecordType,RecordDate) VALUES ('start','2012-03-01'), ('end','2013-07-16'), ('start','2013-11-19'), ('start','2013-12-10'), ('start','2013-12-12'), ('end','2013-12-12'), ('end','2013-12-13'), ('start','2013-12-13'), ('end','2013-12-15'), ('end','2013-12-31'), ('start','2014-01-01'); CREATE TABLE #t(id int ,usethis bit); INSERT #t(id) SELECT id from TableWithStartAndEndDates; DECLARE @LastRecordType varchar(5)=''; DECLARE @usethis bit; DECLARE @ctr int=0; UPDATE #t SET @usethis=case when t.recordtype=@LastRecordType then 0 else 1 end,@LastRecordType=t.RecordType,UseThis=@useThis FROM TableWithStartAndEndDates t with (index=PK_TableWithStartAndEndDates) INNER JOIN #t ON
t.id=#
t.ID OPTION (FORCE ORDER) ; WITH CTE AS( SELECT
t.id,t.RecordType,t.RecordDate,row_number() OVER(order by
t.id) as rownum FROM #t INNER JOIN TableWithStartAndEndDates t ON #
t.id=
t.id AND #t.usethis=1 ) SELECT
CTE.ID as StartID,'start',CTE.RecordDate as StartDate,
CTE2.ID as EndID,'end',CTE2.RecordDate as EndDate FROM CTE INNER JOIN CTE as CTE2 ON CTE.RecordType='start' AND CTE2.RecordType='end' AND CTE2.rownum=CTE.rownum+1 UNION ALL SELECT case when recordtype='start' then
t.id else null end, case when recordtype='start' then 'start' else null end, case when recordtype='start' then t.RecordDate else null end, case when recordtype='end' then
t.id else null end, case when recordtype='end' then 'end' else null end, case when recordtype='end' then t.RecordDate else null end FROM #t inner join TableWithStartAndEndDates t ON #
t.id=
t.id and #t.usethis=0 DROP TABLE TableWithStartAndEndDates; DROP TABLE #t;
You might think it will. But please read the warning note in my answer. I'm not 100% sure you will get the records in the correct order in the UPDATE-statement. You might (or might not, depending on if the index hint along with FORCE ORDER query hint does what I think it does) end up with a query which internally retrieves records in another order than that of the primary key. By nature, SQL Server retrives rows in a random order, unless an ORDER BY statement is there, and in an UPDATE-query, there is no such thing as an ORDER BY clause. That's why I use the hints. But again, I'm not sure they do what I think they do. Someone else on
ask.sqlservercentral.com can surely answer if the hint does what I think it does. Until then: Use this script with caution.
Here's an example solution. This just processes the start dates and end dates in ID order. Not sure about the requirement that "If multiple Start dates can correspond to a single End date then choose Start date that is the oldest" declare @YourTable table ( id int, [Type] varchar(10), [Date] date ) insert into @YourTable select 1, 'Start', '1 Mar 2012' insert into @YourTable select 2, 'End', '16 Jul 2013' insert into @YourTable select 3, 'Start', '19 Nov 2013' insert into @YourTable select 4, 'Start', '10 Dec 2013' insert into @YourTable select 5, 'Start', '12 Dec 2013' insert into @YourTable select 6, 'End', '12 Dec 2013' insert into @YourTable select 7, 'Start', '13 Dec 2013' select
Starts.ID, Starts.[Type], Starts.[Date],
Ends.ID, Ends.[Type], Ends.[Date] from ( select row_number()over(order by id) as rn,id ,[Type] , [Date] from @YourTable where [Type] = 'Start' )Starts left join ( select row_number()over(order by id) as rn,id ,[Type] , [Date] from @YourTable where [Type] = 'End' )Ends on Ends.rn = Starts.rn gives ID Type Date ID Type Date ----------- ---------- ---------- ----------- ---------- ---------- 1 Start 2012-03-01 2 End 2013-07-16 3 Start 2013-11-19 6 End 2013-12-12 4 Start 2013-12-10 NULL NULL NULL 5 Start 2013-12-12 NULL NULL NULL 7 Start 2013-12-13 NULL NULL NULL (5 row(s) affected)
I think one needs to implement something looking backwards on the row before to know if the row should be used or not in the pairing up. I've added some rows to the test data and your script gives wrong results if you have for example below data: insert into @YourTable select 1, 'Start', '1 Mar 2012' insert into @YourTable select 2, 'End', '16 Jul 2013' insert into @YourTable select 3, 'Start', '19 Nov 2013' insert into @YourTable select 4, 'Start', '10 Dec 2013' insert into @YourTable select 5, 'Start', '12 Dec 2013' insert into @YourTable select 6, 'End', '12 Dec 2013' insert into @YourTable select 6, 'End', '13 Dec 2013' insert into @YourTable select 7, 'Start', '14 Dec 2013'
how about this ? ; with cte as ( select id, Type, Date, grp = 1 from @YourTable s where id = 1 union all select
t.id, t.Type, t.Date, grp = case when c.Type = 'End' and t.Type = 'Start' then c.grp + 1 else c.grp end from cte c inner join @YourTable t on
c.id =
t.id - 1 ), cte2 as ( select idStart = min(id), DateStart = min(case when Type = 'Start' then Date end), idEnd = min(case when Type = 'End' then id end), DateEnd = min(case when Type = 'End' then Date end) from cte group by grp ) select t.*, c.idEnd, c.DateEnd from @YourTable t left join cte2 c on
t.id = c.idStart where t.Type = 'Start' RESULT : id Type Date idEnd DateEnd ----------- ---------- ---------- ----------- ---------- 1 Start 2012-03-01 2 2013-07-16 3 Start 2013-11-19 6 2013-12-12 4 Start 2013-12-10 NULL NULL 5 Start 2013-12-12 NULL NULL 8 Start 2013-12-14 NULL NULL (5 row(s) affected)
17 People are following this question.
