Splicing Date Periods in SQL Server

2
votes

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 (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
*/
2
sql-servertsql
I'm working on a solution using nested cursors but I'm struggling with it and I was wondering if anyone else had either done something similar or had any ideas about how best to attack it. - TDSnet
try while loop instead. it is faster - Sushil
@Sushil, that's not really true. A WHILE loop is still a CURSOR. See this article for details. - Felix Pamittan
What version of SQL Server you use? In SQL Server 2012+ there are LEAD and LAG functions that help to solve this "gaps and islands" problem efficiently. - Vladimir Baranov
thanks @wewesthemenace. its a nice article. cleared my misconception. - Sushil

2 Answers

0
votes

The TrimmedRanges cte just uses table @x to 'trim' each of the ranges in table @y. Then the CombinedRanges cte looks for adjacent ranges and creates new ranges covering them both. It does this recursively. Finally, the MostComprehensiveRanges cte pulls out only the ranges that aren't encompassed by other ranges.

Note depending on the amount of data you're processing, you may need to use the OPTION (MAXRECURSION ##) query hint. https://msdn.microsoft.com/en-us/library/ms175972.aspx

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 (product varchar(10), fromdate date, todate date) -- result

insert  @x values ('lrecs', '20150101', '20161231')
insert  @x values ('srecs', '20150701', '20161231')

insert  @y values ('lrecs', '20141201', '20150331')
insert  @y values ('lrecs', '20150401', '20150630')
insert  @y values ('lrecs', '20150701', '20150731')
insert  @y values ('lrecs', '20150801', '20150831')
insert  @y values ('lrecs', '20160101', '20160630')
insert  @y values ('srecs', '20160101', '20161231')

;with TrimmedRanges as (
  select
    a.product,
    case when a.fromdate < b.fromdate then b.fromdate else a.fromdate end fromdate,
    case when a.todate > b.todate then b.todate else a.todate end todate
  from
    @y a
    join @x b on a.product = b.product
),
CombinedRanges as (
  select product, fromdate, todate from TrimmedRanges
  union all
  select a.product, a.fromdate, b.todate
  from
    TrimmedRanges a
    join CombinedRanges b
      on a.product = b.product
      and b.fromdate = dateadd(d, 1, a.todate)
),
MostComprehensiveRanges as (
  select a.*
  from CombinedRanges a
  where
    not exists (
      select 1
      from CombinedRanges b
      where
        a.product = b.product
        and (
          ( a.fromdate > b.fromdate and a.fromdate < b.todate )
          or ( a.todate > b.fromdate and a.todate < b.todate )
        )
    )
)
select * from MostComprehensiveRanges
order by product, fromdate
0
votes

SQL 2012 and Above Solution

I'll admit I have not done extensive testing on this to make sure it works in all situations, but it works for your sample data set. It is a recursive/loop/cursor-free solution. Check it out and let me know if it needs any tweaks or if you have any questions.

--Combines the tables
;WITH CTE_Union
AS
(
    SELECT product,fromdate,todate
    FROM @y
    UNION ALL
    SELECT product,fromdate,NULL
    FROM @x
    UNION ALL
    SELECT product,NULL,todate
    FROM @x
),
--forms most of the ranges
CTE_range
AS
(
    SELECT  *,
            next_fromdate = LEAD(fromdate,1) OVER (PARTITION BY product ORDER BY fromdate)
    FROM
    (
        SELECT  product,
                fromdate = COALESCE(fromdate,DATEADD(DAY,1,LAG(todate,1) OVER (PARTITION BY product ORDER BY todate))),
                todate = COALESCE(todate,DATEADD(DAY,-1,LEAD(fromdate,1) OVER (PARTITION BY product ORDER BY fromdate)))
        FROM CTE_Union
    ) A
    WHERE fromdate < todate
)

SELECT  product,
        fromdate,
        todate
FROM CTE_range
UNION ALL
--fills in the gaps
SELECT product,DATEADD(DAY,1,todate),DATEADD(DAY,-1,next_fromdate)
FROM CTE_range
WHERE DATEDIFF(DAY,todate,next_fromdate) != 1 --so where there is a gap
ORDER BY product,fromdate