question

TDSnet avatar image
TDSnet asked

Splicing Date Periods in SQL Server

I need a solution for merging date periods in SQL Server. The below sets up the working example. I have two tables, the first holding a single period for a particular product, the second holding smaller periods that fall within the related period from the first table. I need to merge the two so that I end up with a list of all the contiguous periods covering the original period when including the smaller periods. I’ve included a sample of the desired result. Rules: - There would only ever be a single row in the first table per product - The rows in the second table would never overlap for a product - The rows in the second table would only ever fall on or within the boundaries of the original period 
    declare @x table (product varchar(10), fromdate date, todate date) -- forecast
    declare @y table (product varchar(10), fromdate date, todate date) -- purchases
    declare @z table (id int identity(1,1), product varchar(10), fromdate date, todate date) -- result
    
    insert @x values ('lrecs', '20150101', '20161231')
    insert @x values ('srecs', '20150701', '20161231')
    
    insert @y values ('lrecs', '20150401', '20150630')
    insert @y values ('lrecs', '20160101', '20160630')
    insert @y values ('srecs', '20160101', '20161231')
    
    /*
    product	fromdate	todate
    ------------------------------
    lrecs	2015-01-01	2015-03-31
    lrecs	2015-04-01	2015-06-30
    lrecs	2015-07-01	2015-12-31
    lrecs	2016-01-01	2016-06-30
    lrecs	2016-07-01	2016-12-31
    srecs	2015-07-01	2015-12-31
    srecs	2016-01-01	2016-12-31
    */
datemergedatesdate-functionsdate-range
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
sabinweb avatar image
sabinweb answered
If you are using 2012 , you can use LEAD or LAG functions declare @x table (product varchar(10), fromdate date, todate date) -- forecast declare @y table (product varchar(10), fromdate date, todate date) -- purchases declare @z table (id int identity(1,1), product varchar(10), fromdate date, todate date) -- result insert @x values ('lrecs', '20150101', '20161231') insert @x values ('srecs', '20150701', '20161231') insert @y values ('lrecs', '20150401', '20150630') insert @y values ('lrecs', '20160101', '20160630') insert @y values ('srecs', '20160101', '20161231') --1 type date = From --2 type date = to ;WITH cte AS( SELECT product,x.fromDate AS dDate , 1 as TypeDate FROM @x as X UNION SELECT product,x.ToDate , 2 FROM @x as X UNION SELECT product,y.fromDate , 1 FROM @y as y UNION SELECT product,y.ToDate , 2 FROM @y as y ) ,cteRow AS( SELECT product,dDate,TypeDate ,Row_Number()OVER(PARTITION BY product ORDER BY dDate,TypeDate) AS RN ,LAG(dDate,1,NULL)OVER(PARTITION BY product ORDER BY dDate,TypeDate) AS PrevDate ,LAG(TypeDate,1,NULL)OVER(PARTITION BY product ORDER BY dDate,TypeDate) AS PrevType ,LEAD(dDate,1,NULL)OVER(PARTITION BY product ORDER BY dDate,TypeDate) AS NextDate ,LEAD(TypeDate,1,NULL)OVER(PARTITION BY product ORDER BY dDate,TypeDate) AS NextType FROM cte ) SELECT product --,dDate,TypeDate , RN ,PrevDate , PrevType, NextDate,NextType ,CASE WHEN TypeDate = 1 THEN dDate ELSE DATEADD(day,1,dDate) END as FromDate ,CASE WHEN NextType = 2 THEN NextDate ELSE DATEADD(day,-1,NextDate) END as ToDate FROM cteRow WHERE NextDate IS NOT NULL and the result is : product FromDate ToDate lrecs 2015-01-01 2015-03-31 lrecs 2015-04-01 2015-06-30 lrecs 2015-07-01 2015-12-31 lrecs 2016-01-01 2016-06-30 lrecs 2016-07-01 2016-12-31 srecs 2015-07-01 2015-12-31 srecs 2016-01-01 2016-12-31
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TDSnet avatar image TDSnet commented ·
Perfect - Thanks..!!
0 Likes 0 ·
sabinweb avatar image
sabinweb answered
Here is one way , base on your input data: declare @x table (product varchar(10), fromdate date, todate date) -- forecast declare @y table (product varchar(10), fromdate date, todate date) -- purchases declare @z table (id int identity(1,1), product varchar(10), fromdate date, todate date) -- result insert @x values ('lrecs', '20150101', '20161231') insert @x values ('srecs', '20150701', '20161231') insert @y values ('lrecs', '20150401', '20150630') insert @y values ('lrecs', '20160101', '20160630') insert @y values ('srecs', '20160101', '20161231') ;WITH cte AS( SELECT product,x.fromDate AS dDate , 1 as IsFromDate , 0 as IsToDate FROM @x as X UNION SELECT product,x.ToDate , 0 as IsFromDate , 1 as IsToDate FROM @x as X UNION SELECT product,y.fromDate , 1 as IsFromDate , 0 as IsToDate FROM @y as y UNION SELECT product,y.ToDate , 0 as IsFromDate , 1 as IsToDate FROM @y as y ) ,cteRow AS( SELECT product,dDate,isFromDate,IsToDate , Row_Number()OVER(PARTITION BY product ORDER BY dDate,IsFromDate,IsToDate) AS RN FROM cte ) SELECT R1.Product, CASE WHEN R1.IsFromDate = 1 THEN R1.dDate ELSE DATEADD(day,1,R1.dDate) END as FromDate, CASE WHEN R2.IsToDate = 1 THEN R2.dDate ELSE DATEADD(day,-1,R2.dDate) END as ToDate FROM cteRow AS R1 INNER JOIN cteRow AS R2 ON R1.RN = R2.RN - 1 AND R1.product = R2.product ORDER BY R1.Product,R1.dDate,R1.IsFromDate,R1.IsToDate
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.