- Home /

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 */

Comment

Best Answer

**Answer** by sabinweb ·

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

**Answer** by sabinweb ·

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

Copyright 2019 Redgate Software.
Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges