0

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:

  1. Start date should be equal to the BEG_DT
  2. Each date span should be equal to the PLAN_LENGTH months apart
  3. Do not exceed END_DT
  4. Do not return a date that exceeds the PLAN_LENGTH months from the current run date.
  5. 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
>     */
1

2 Answers 2

2

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

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

1

My approach is very similar to @Andriy's - how could it not be? If anyone has something significantly different, I'd be all ears! All of the code below is avaialbe in the fiddle here.

I created the table as per the question, however, I added some appropriate (I think) constraints - the more we tell the optimiser, the better the plan it will produce! I realise that this is a test scenario, so maybe these won't apply in the real world!

CREATE TABLE date_span
(
  group_id    VARCHAR(8) NOT NULL,
  plan_id     VARCHAR(8) NOT NULL,
  beg_dt      DATE       NOT NULL,
  end_dt      DATE       NOT NULL,
  plan_length INTEGER    NOT NULL,
  
  CONSTRAINT ds_pk PRIMARY KEY (group_id, plan_id, beg_dt),
  
  CONSTRAINT bd_lt_ed_ck CHECK (beg_dt < end_dt),
  
);

but when it came to INSERTing the records, I changed things slightly. Originally, the data was as follows:

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')  -- out of order
,   ('32198498','AMD00002','2020-10-01','2022-09-30','12')         "
,   ('32198498','AMD00003','2020-10-01','2100-09-30','12')         "

Now, the human mind is brilliant at spotting patterns and finding logical links between those patterns - what it is less good at is pulling information out of a "jumble" of facts.

So, I loaded these records as follows:

('32198498','AMD00001','2020-10-01','2022-09-30', '12'),


('32198498','AMD00002','2020-10-01','2022-09-30', '12'),

('32198498','AMD00003','2020-10-01','2100-09-30', '12'),


('75491773','AMT00004','2020-01-01','2021-06-30', '18'),


('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');

I put a line between the records - made it easier for this human to see what was going on. Note also that in the original set, where were two records with a group_id of 32198498 and a plan_id of AMD00001 - I consolidated them into 1 record. You can do this by doing this:

SELECT 
  group_id, plan_id, MIN(beg_dt), MAX(end_dt), plan_length
FROM date_spans_x
GROUP BY group_id, plan_id, plan_length;

In this test case, I did it manually. The solution works with both sets of data (not shown).

I've gone through the logical steps that I used to arrive at a solution - hopefully it'll help you - I know that after I've finished a problem, I find that sitting down and explaining it to myself helps me also...

This is a question which is screaming out for a Common Table Expression (CTE), in particular a RECURSIVE CTE (RCTE).

First step - fire a shotgun at the problem and hope that something sticks!

--
-- non-discriminating RCTE - limited by n - arbitrary!
--

WITH  tab (n, gid, pid, st, t_p1_et, t_p1_st, et, pl) AS
(
  SELECT 
    1,
    ds.group_id, ds.plan_id,
    
    ds.beg_dt,  
    
    -- beginning of period for plan 1  - for a given group_id & plan_id
    
    DATEADD(DAY, -1, DATEADD(MONTH, ds.plan_length, ds.beg_dt)),  
     
    -- end of period for plan 1
    
    DATEADD(MONTH, ds.plan_length, ds.beg_dt),   
    
    -- beginning of period for plan 1 + 1
    
    ds.end_dt,
  
    ds.plan_length

  FROM date_span ds
  UNION ALL
  
  SELECT 
    n + 1,
    gid, pid, 
    
    t_p1_st,  -- start of next period, 

    DATEADD(DAY, -1, DATEADD(MONTH, tab.pl, tab.t_p1_st)),  -- end of next period
    
    DATEADD(MONTH, tab.pl, tab.t_p1_st),  -- beginning of plan, period + 2

    et,
    
    pl

  FROM tab


  WHERE n < 4 

)
SELECT * FROM tab
ORDER BY gid, pid, n, st, et
OPTION (MAXRECURSION 150);

Result:

n        gid        pid             st     t_p1_et  t_p1_st         et      pl
1   32198498    AMD00001    2020-10-01  2021-09-30  2021-10-01  2022-09-30  12
2   32198498    AMD00001    2021-10-01  2022-09-30  2022-10-01  2022-09-30  12
3   32198498    AMD00001    2022-10-01  2023-09-30  2023-10-01  2022-09-30  12
4   32198498    AMD00001    2023-10-01  2024-09-30  2024-10-01  2022-09-30  12
1   32198498    AMD00002    2020-10-01  2021-09-30  2021-10-01  2022-09-30  12
2   32198498    AMD00002    2021-10-01  2022-09-30  2022-10-01  2022-09-30  12
3   32198498    AMD00002    2022-10-01  2023-09-30  2023-10-01  2022-09-30  12
4   32198498    AMD00002    2023-10-01  2024-09-30  2024-10-01  2022-09-30  12
1   32198498    AMD00003    2020-10-01  2021-09-30  2021-10-01  2100-09-30  12
...
... Snipped for brevity
...
28 rows

So, there's nothing selective about the CTE - just n < 4 - so, we obtain 7 x 4 = 28 records. We'll take a quick look at the first set of records (by group_id/plan_id):

n        gid        pid             st     t_p1_et  t_p1_st         et      pl
1   32198498    AMD00001    2020-10-01  2021-09-30  2021-10-01  2022-09-30  12
2   32198498    AMD00001    2021-10-01  2022-09-30  2022-10-01  2022-09-30  12
3   32198498    AMD00001    2022-10-01  2023-09-30  2023-10-01  2022-09-30  12
4   32198498    AMD00001    2023-10-01  2024-09-30  2024-10-01  2022-09-30  12

The first record's st is the start time (beg_dt from the table) is 2020-10-01. The outside (fourth) date field is et (end_dt from the table). This latter one doesn't change within the group (group_id, plan_id).

The second date field (t_p1_et - period 1, end time) is 2020-10-01 + 12 months = 2021-09-30 which is correct and the 3rd date field (t_np_st - next period, start time) is 2021-10-01.

So, now we proceed to the second record of the group. Its st is now the t_np_st of the previous (1st) record - 2021-10-01 and then its end time is calculated by adding the plan_length number of months to the st and the start of the following period is also derived from this by adding a year to the st.

And so on! The amazing power of RCTEs becomes apparent.

A couple of points to notice about this and about CTEs in general.

  • n is a "dummy" used to stop the CTE - if there is no stop condition, the CTE will go into an infinite loop (also happens with while in other languages if there is no termination condition). If you're using dbfiddle, you will (eventually) receive a Run failed error message.

    The n also becomes very useful when evaluating different WHERE conditions.

  • OPTION (MAXRECURSION 150); does the same thing as n above - it's SQL Server specific to make sure that queries don't run amok - not strictly necessary here - but it's all too easy to delete the n part of the query while experimenting... YMMV.

  • it would in theory be possible to do this by having a (horrible) query which joins the date_span query back on itself 4 times and taking ds1.plan fields and performing a UNION with the ds2.plan_2 fields and peforming a UNION with ds3...

    The date calculations would become pretty hairy as well with adding months and years nested 4 deep... I leave that as an exercise for the reader. And what happens if you want a result for, say, 10 years into the future?

After much fiddling, experimenting, wailing and gnashing of teeth (with apologies to the authors of the Bible...) and some hints from @Andriy, I came up with this - for the WHERE clause in the RCTE and the subsequent SELECT from that RCTE:

--
-- RCTE with meaningful WHERE clause
--

...
... Recursive CTE the same to here - snipped for brevity
...
  FROM tab

  WHERE tab.t_p1_et < tab.et AND tab.t_p1_et < DATEADD(MONTH, tab.pl, GETDATE())  

)
SELECT n, t.gid, t.pid, t.st, t.t_p1_et, t.pl FROM tab t
ORDER BY t.gid, t.pid, t.st 
-- OPTION (MAXRECURSION 150);  -- can leave just in case of data error...

Result:

n        gid         pid        st       t_p1_et    pl
1   32198498    AMD00001    2020-10-01  2021-09-30  12
2   32198498    AMD00001    2021-10-01  2022-09-30  12
1   32198498    AMD00002    2020-10-01  2021-09-30  12
2   32198498    AMD00002    2021-10-01  2022-09-30  12
1   32198498    AMD00003    2020-10-01  2021-09-30  12
2   32198498    AMD00003    2021-10-01  2022-09-30  12
1   75491773    AMT00004    2020-01-01  2021-06-30  18
1   82551399    AMT00001    2020-01-01  2020-12-31  12
1   82551399    AMT00002    2020-01-01  2020-12-31  12
2   82551399    AMT00002    2021-01-01  2021-12-31  12
3   82551399    AMT00002    2022-01-01  2022-12-31  12
1   82551399    AMT00003    2020-01-01  2020-12-31  12
2   82551399    AMT00003    2021-01-01  2021-12-31  12
13 rows

The crux here is the WHERE clause:

WHERE tab.t_p1_et < tab.et AND tab.t_p1_et < DATEADD(MONTH, tab.pl, GETDATE())

So, the first bit tab.t_p1_et < tab.et means that the end time of the plan period cannot extend beyond the end time as specified in the original table. This can be 2100-09-30 (won't be the terminating condition) or something closer when the CTE will be terminated by the second part of the WHERE clause.

The second bit is so that if the end time of the period doesn't go beyond the date the query is run (GETDATE()) plus the number of months in the plan_length. It took considerable time and discussion to elucidate this.

If you want to keep these records permanently, you might want to use a table (I've called it tranche) as follows:

CREATE TABLE tranche
(
  t_id INTEGER IDENTITY (1, 1),
  t_gid VARCHAR(8) NOT NULL,
  t_pid VARCHAR(8) NOT NULL,
  t_sdt DATE       NOT NULL,
  t_edt AS (DATEADD(MONTH, t_plen, t_sdt)),
  t_plen INTEGER    NOT NULL,
  
  CONSTRAINT n_g_p_uq UNIQUE (t_gid, t_pid, t_sdt),  -- you can put in various
                                                   -- other constraints
);

Note that the t_edt field is calculated from t_sdt and t_plen number of months - you may or may not find this useful.

Peformance:

Finally, I turned on statistics as follows in the fiddle:

SET STATISTICS PROFILE, TIME, IO ON;
  • you should always benchmark any SQL against a realistic dataset. Any results derived from a server where a) you don't know what's happening on the rest of the machine and b) you're only dealing with 7 records, should be treated with a big pinch of salt!

I compared my solution with @Andriy's and the results are shown - I'm not really a SQL Server person, but I'll see if I can dig up anything of relevance for these two runs.

Finally, questions such as yours occur frequently enough here and are now reasonably tractable thanks to RCTEs. Many of them make use of calendar tables - as @Andriy points out, PostgreSQL has the very powerful GENERATE_SERIES function - here is a SQL Server answer where I use various approaches to tackle a similar problem. Here are a couple (1 & 2) of answers which use PostgreSQL - but you can use a SQL Server recursive CTE instead of GENERATE_SERIES - if you're interested in learning more. Other posters' approaches are also worth looking at!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.