Sql-server – Create Date Spans from Contiguous and Non-Contiguous Date Spans

datesql servert-sql

I am trying to create a query that will return a series of dates based off of the 'BEG_DT' and 'END_DT'. The results should show a series dates based off of the 'BEG_DT' and with a span between each date equal to the 'PLAN_LENGTH' value. An Example would be a 'BEG_DT' of 2020-07-01, 'END_DT' of 2100-06-30 and 'PLAN_LENGTH' of 12, so it would return the dates '2020-07-01' and '2021-07-01'. The last Date it should generate is 2021-07-01 as the next date of '2022-07-01' is more than 12 month in the future.

The logic requirements would look like this:

Start date should be equal to the BEG_DT
Each date span should be equal to the PLAN_LENGTH months apart
Do not exceed END_DT
Do not return a date that exceeds the PLAN_LENGTH months from the current run date.
Each GROUP_ID and PLAN_ID can have its own start and end date.

This is the logic that I have tried use. I have not been able to get it to recognize that different group and plan combinations can have different starting month and days.

DECLARE @RUN_DATE DATETIME = CURRENT_TIMESTAMP

    Create table #DATE_SPANS
    (
        GROUP_ID VARCHAR(8)
    ,   PLAN_ID VARCHAR(8)
    ,   BEG_DT DATE
    ,   END_DT DATE
    ,   PLAN_LENGTH INT
    )

    Insert Into #DATE_SPANS
    (GROUP_ID, PLAN_ID, BEG_DT, END_DT, PLAN_LENGTH)
    values 
        ('82551399','AMT00001','2020-01-01','2020-12-31','12')
    ,   ('82551399','AMT00002','2020-01-01','2100-12-31','12')
    ,   ('82551399','AMT00003','2020-01-01','2021-12-31','12')
    ,   ('75491773','AMT00004','2020-01-01','2021-06-30','18')
    ,   ('32198498','AMD00001','2020-10-01','2021-09-30','12')
    ,   ('32198498','AMD00001','2021-10-01','2022-09-30','12')
    ,   ('32198498','AMD00002','2020-10-01','2022-09-30','12')
    ,   ('32198498','AMD00003','2020-10-01','2100-09-30','12')


declare @BEGIN_DT_1 DATETIME = (SELECT MIN(DISTINCT(BEG_DT)) FROM #DATE_SPANS)
declare @END_DT_1 DATETIME = (SELECT MAX(DISTINCT(CASE WHEN END_DT = '9999-12-31' then @RUN_DATE ELSE END_DT END)) FROM #DATE_SPANS )

--SELECT @END_DT_1

;with dates ([Date]) as (
    Select convert(date, @BEGIN_DT_1) as [Date] -- Put the start date here

    union all 

    Select dateadd(YEAR, 1, [Date])
    from dates
    where [Date] <= @END_DT_1 -- Put the end date here 
) 

select t.GROUP_ID
    , t.PLAN_ID
    , [Date] AS [DATE]
    INTO #TEMP_PLAN_YEARS
    from dates d
    join #DATE_SPANS t on d.Date >= t.BEG_DT
        and d.Date <= DATEADD(MM,0,t.END_DT)
option (maxrecursion 32767)

select distinct GROUP_ID, PLAN_ID,  [DATE]
into #TEMP_PLAN_YEARS_FINAL
from #TEMP_PLAN_YEARS
where [DATE] >= '2021-01-01'
order by [DATE],  PLAN_ID

select * from #TEMP_PLAN_YEARS_FINAL
where DATE <= DATEADD(YEAR, 1, GETDATE())

————TEST DATA——–

CREATE TABLE #DATE_SPANS
(
    GROUP_ID VARCHAR(8)
,   PLAN_ID VARCHAR(8)
,   BEG_DT DATE
,   END_DT DATE
,   PLAN_LENGTH INT
)

INSERT INTO #DATE_SPANS 
(GROUP_ID, PLAN_ID, BEG_DT, END_DT, PLAN_LENGTH)

Group 82551399 has 3 different plan IDs that run for different periods of time. Each plan is based off of a 12 month plan year. AMT00001 has its one year shown. AMT00002 will be broken down into three plan years for 2020, 2021 & 2022.

Note: System Dates can be posted out to and beyond 2100. Often they show as 9999-12-31. AMT00003 will be broken down into two years 2020 & 2021. On a 12 month calendar year. Group 32198498 will follow the same as group 82551399 but will have an effective date starting 10-01.

Run Date for results below would be 6/7/2021

 VALUES
    ('82551399','AMT00001','2020-01-01','2020-12-31','12')
,   ('82551399','AMT00002','2020-01-01','2100-12-31','12')
,   ('82551399','AMT00003','2020-01-01','2021-12-31','12')
,   ('75491773','AMT00004','2020-01-01','2021-06-31','18')
,   ('32198498','AMD00001','2020-10-01','2021-09-30','12')
,   ('32198498','AMD00001','2021-10-01','2022-09-30','12')
,   ('32198498','AMD00002','2020-10-01','2022-09-30','12')
,   ('32198498','AMD00003','2020-10-01','2100-09-30','12')

/*

>     EXPECTED RESULT
> --Group ID 8251399
>     82551399, AMT00001, 2020-01-01
> 
>     82551399, AMT00002, 2020-01-01
>     82551399, AMT00002, 2021-01-01
>     82551399, AMT00002, 2022-01-01
> 
>     82551399, AMT00003, 2020-01-01
>     82551399, AMT00003, 2021-01-01
> 
> --Group ID 75491773
> 
> 75491773, AMT00004, 2020-01-01
> 
> --Group ID 32198498
>     32198498, AMD00001, 2020-10-01
>     32198498, AMD00001, 2021-10-01
> 
>     32198498, AMD00002, 2020-10-01 
>     32198498, AMD00002, 2021-10-01
  
>     32198498, AMD00003, 2020-10-01
>     32198498, AMD00003, 2021-10-01
>     */

Best Answer

My approach to this problem could be summarised like this:

For each date range, get the list of all dates within the interval that are PLAN_LENGTH months apart, starting at BEG_DT.
Exclude all dates that are equal to or later than today plus PLAN_LENGTH months.

If this were PostgreSQL, the logic could be rather succinctly expressed like this:

SELECT
  ds.*
, x.Date
FROM
  date_spans AS ds
, generate_series(ds.BEG_DT, ds.END_DT, (ds.PLAN_LENGTH || ' months')::interval) AS x (Date)
WHERE
  x.Date < CURRENT_TIMESTAMP::date + ds.PLAN_LENGTH * INTERVAL '1 month'
ORDER BY
  ds.group_id ASC
, ds.plan_id ASC
, x.Date ASC
;

Alas, Transact-SQL does not have generate_series or an equivalent, so I had to make do without it. I still used the above query as a general pointer, though. The following Transact-SQL solution, therefore, contains an attempt to emulate generate_series using other means, namely a numbers table (or rather a simple CTE implementation thereof) and a little bit of date arithmetic:

WITH
  Numbers (N) AS
  (
    SELECT
      hundreds.N * 100 + tens.N * 10 + ones.N * 1
    FROM
      (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS ones (N)
    , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS tens (N)
    , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS hundreds (N)
  )
SELECT
  ds.*
, x.Date
FROM
  dbo.date_spans AS ds

  -- this join multiplies each range as many times as there are
  -- PLAN_LENGTH-month intervals within the range
  INNER JOIN Numbers AS n
    ON n.N BETWEEN 0 AND DATEDIFF(MONTH, ds.BEG_DT, ds.END_DT) / ds.PLAN_LENGTH

  -- this generates an actual date
  CROSS APPLY
  (
    SELECT DATEADD(YEAR, n.N, ds.BEG_DT)
  ) AS x (Date)
WHERE
  -- this additionally caps the date list at a point that is today + PLAN_LENGTH months
  x.Date < DATEADD(MONTH, ds.PLAN_LENGTH, CAST(CURRENT_TIMESTAMP AS date))
ORDER BY
  ds.group_id ASC
, ds.plan_id ASC
, n.N ASC
;

The Numbers dataset on its own can be viewed as a replacement for generate_series for (non-negative) integers. And together with the CROSS APPLY it constitutes a replacement for the timestamp-related version of that function.

For the test setup provided in the question, the above script returns this output:

group_id plan_id beg_dt end_dt plan_length Date

32198498 AMD00001 2020-10-01 2021-09-30 12 2020-10-01

32198498 AMD00001 2021-10-01 2022-09-30 12 2021-10-01

32198498 AMD00002 2020-10-01 2022-09-30 12 2020-10-01

32198498 AMD00002 2020-10-01 2022-09-30 12 2021-10-01

32198498 AMD00003 2020-10-01 2100-09-30 12 2020-10-01

32198498 AMD00003 2020-10-01 2100-09-30 12 2021-10-01

75491773 AMT00004 2020-01-01 2021-06-30 18 2020-01-01

82551399 AMT00001 2020-01-01 2020-12-31 12 2020-01-01

82551399 AMT00002 2020-01-01 2100-12-31 12 2020-01-01

82551399 AMT00002 2020-01-01 2100-12-31 12 2021-01-01

82551399 AMT00002 2020-01-01 2100-12-31 12 2022-01-01

82551399 AMT00003 2020-01-01 2021-12-31 12 2020-01-01

82551399 AMT00003 2020-01-01 2021-12-31 12 2021-01-01

group_id	plan_id	beg_dt	end_dt	plan_length	Date
32198498	AMD00001	2020-10-01	2021-09-30	12	2020-10-01
32198498	AMD00001	2021-10-01	2022-09-30	12	2021-10-01
32198498	AMD00002	2020-10-01	2022-09-30	12	2020-10-01
32198498	AMD00002	2020-10-01	2022-09-30	12	2021-10-01
32198498	AMD00003	2020-10-01	2100-09-30	12	2020-10-01
32198498	AMD00003	2020-10-01	2100-09-30	12	2021-10-01
75491773	AMT00004	2020-01-01	2021-06-30	18	2020-01-01
82551399	AMT00001	2020-01-01	2020-12-31	12	2020-01-01
82551399	AMT00002	2020-01-01	2100-12-31	12	2020-01-01
82551399	AMT00002	2020-01-01	2100-12-31	12	2021-01-01
82551399	AMT00002	2020-01-01	2100-12-31	12	2022-01-01
82551399	AMT00003	2020-01-01	2021-12-31	12	2020-01-01
82551399	AMT00003	2020-01-01	2021-12-31	12	2021-01-01

A live version of the solution is available at db<>fiddle.

Related Solutions

Sql-server – How to avoid using variables in WHERE clause

Parameter sniffing is your friend almost all of the time and you should write your queries so that it can be used. Parameter sniffing helps building the plan for you using the parameter values available when the query is compiled. The dark side of parameter sniffing is when the values used when compiling the query is not optimal for the queries to come.

The query in a stored procedure is compiled when the stored procedure is executed, not when the query is executed so the values that SQL Server has to deal with here...

CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
  DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
  SELECT
    -- Stuff
  FROM Sale
  WHERE SaleDate BETWEEN @startDate AND @endDate
END

is a known value for @endDate and an unknown value for @startDate. That will leave SQL Server to guessing on 30% of the rows returned for the filter on @startDate combined with whatever the statistics tells it for @endDate. If you have a big table with a lot of rows that could give you a scan operation where you would benefit most from a seek.

Your wrapper procedure solution makes sure that SQL Server sees the values when DateRangeProc is compiled so it can use known values for both @endDate and @startDate.

Both your dynamic queries leads to the same thing, the values are known at compile-time.

The one with a default null value is a bit special. The values known to SQL Server at compile-time is a known value for @endDate and null for @startDate. Using a null in a between will give you 0 rows but SQL Server always guess at 1 in those cases. That might be a good thing in this case but if you call the stored procedure with a large date interval where a scan would have been the best choice it may end up doing a bunch of seeks.

I left "Use the DATEADD() function directly" to the end of this answer because it is the one I would use and there is something strange with it as well.

First off, SQL Server does not call the function multiple times when it is used in the where clause. DATEADD is considered runtime constant.

And I would think that DATEADD is evaluated when the query is compiled so that you would get a good estimate on the number of rows returned. But it is not so in this case.
SQL Server estimates based on the value in the parameter regardless of what you do with DATEADD (tested on SQL Server 2012) so in your case the estimate will be the number of rows that is registered on @endDate. Why it does that I don't know but it has to do with the use of the datatype DATE. Shift to DATETIME in the stored procedure and the table and the estimate will be accurate, meaning that DATEADD is considered at compile time for DATETIME not for DATE.

So to summarize this rather lengthy answer I would recommend the wrapper procedure solution. It will always allow SQL Server to use the values provided when compiling the the query without the hassle of using dynamic SQL.

PS:

In comments you got two suggestions.

OPTION (OPTIMIZE FOR UNKNOWN) will give you an estimate of 9% of rows returned and OPTION (RECOMPILE) will make SQL Server see the parameter values since the query is recompiled every time.

Sql-server – the best way to get all data for a date range, plus the last event just before the range

I am going to assume that there isn't an index on the date columns, otherwise I think that the query would have been structured differently. If there is, you can probably find a better performing one than this.

The advantage of this query is that it can get all the data in one scan. The disadvantage is that it has to sort the data and join EventEmployee on the entire table. So as always, test with your own situation. This query also assumes that the MAX date is either unique or that equivalent rows would be acceptable.

USE AdventureWorks2012
GO
;
WITH Base AS (
   SELECT 
      TransactionHistory.*
      ,ProductVendor.BusinessEntityID
      ,MAX(CASE WHEN TransactionDate < '2008-08-01' THEN TransactionDate END) 
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS PreviousVendorTransaction
      ,COUNT(CASE WHEN TransactionDate >= '2008-08-01' THEN 1 END ) 
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS VendorAfterCutoff
   FROM
      Production.TransactionHistory
      -- Doesn't make the most sense, but I need a repeating relation
      INNER JOIN Purchasing.ProductVendor
         ON TransactionHistory.ProductID = ProductVendor.ProductID
),
Filtered AS (
   SELECT
      *
   FROM
      Base
   WHERE
      Base.TransactionDate >= '2008-08-01'
      OR (TransactionDate = PreviousVendorTransaction AND VendorAfterCutoff > 0)
)
SELECT DISTINCT
   TransactionID
   ,ProductID
   ,ReferenceOrderID
   ,ReferenceOrderLineID
   ,TransactionDate
   ,TransactionType
   ,Quantity
   ,ActualCost
   ,ModifiedDate
FROM
   Filtered

Edit:

Hmm, I think I may have to take back my comment on structuring it differently if there are indexes. The other suggestions that I have are probably fairly minor.

Make sure the query is using the indexes you're expecting it to. Start and End date to build temp table, end date to drive the previous event loop.
If the query to build the temp table is doing a lookup on the clustered index, it may be better to hold off and do that as part of the main query.
Try using a cte instead of a temp table. I think that a cte might be more competitive with the way that the query is structured below.
If you are returning a lot of events, it might be better to pull out the event table lookup to the main query to give the optimizer the option of doing a merge join.
I don't see a way of optimizing the previous event lookup short of an indexed view.

Here's a query that combines a few of those ideas.

SELECT
    e.[EventID]
INTO #EventTemp
FROM
    [Events] AS e
WHERE
    ( e.[EventStart] >= @StartDate AND e.[EventStart] <= @EndDate )
    OR ( e.[EventEnd] >= @StartDate AND e.[EventEnd] <= @EndDate )

;
WITH PrevEvent AS (
    SELECT
        EmpPrevEvent.[EventID]
    FROM
    (
        SELECT DISTINCT
            ee.[EmployeeID]
        FROM
            #EventTemp
            INNER JOIN [EventEmployee] AS ee ON
                #EventTemp.[EventID] = ee.[EventID]
    ) AS Emp
    CROSS APPLY (
        SELECT TOP 1
            e.[EventID]
        FROM
            [Events] AS e
            INNER JOIN [EventEmployee] AS ee ON
                e.[EventID] = ee.[EventID]
        WHERE
            ee.[EmployeeID] = Emp.[EmployeeID]
            AND e.[EventEnd] < @StartDate
        ORDER BY 
            e.[EventEnd] DESC
    ) AS EmpPrevEvent
)
SELECT
    e.[EventID],
    e.[EventStart],
    e.[EventEnd],
    e.[EventTypeID]
FROM
    [Events] AS e
WHERE
    e.EventID IN (
        SELECT EventID
        FROM #EventTemp
        UNION
        SELECT EventID
        FROM PrevEvent
    )

————TEST DATA——–

Best Answer

Related Solutions

Sql-server – How to avoid using variables in WHERE clause

Sql-server – the best way to get all data for a date range, plus the last event just before the range

Related Question