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!
