Split dates based on Start and End Dates
-
Hi Team,
I'm seeking help on date split, the dates can start between month also. below is the sample code and output. Thank you!
Create Table #DATE_SPLIT
(
ID INT,
StartDate DATE,
EndDate DATE
)
INSERT INTO #DATE_SPLIT
SELECT 10,'01/10/2020','03/21/2020'
UNION ALL
SELECT 10,'03/22/2020','12/31/9999'
--OUTPUT
ID StartDate EndDate
10 '01/10/2020' '01/31/2020'
10 '02/01/2020' '02/29/2020'
10 '03/01/2020' '03/21/2020'
10 '03/22/2020' '03/31/2020'
10 '04/01/2020' '12/31/9999'- This topic was modified 3 years, 9 months ago by koti.raavi. Reason: Updated code
-
drop table if exists #date_split;
go
Create Table #date_split
(
ID INT,
StartDate DATE,
EndDate DATE
)
go
insert #date_split(ID, StartDate, EndDate) values
(10,'01/10/2020','03/21/2020'),
(10,'03/22/2020','12/31/9999');
drop function if exists dbo.fnNextMonth;
go
create function dbo.fnNextMonth(
@StartDate date,
@EndDate date)
returns table as
return
select iif(@EndDate='12/31/9999', dateadd(month, 1, datefromparts(year(@StartDate), month(@StartDate), 1)), @EndDate) NextMonth;
go
select
ID,
iif(ds.EndDate='12/31/9999',
iif(f.n=0, ds.StartDate, fnm.NextMonth),
iif(f.n=0, ds.StartDate, dateadd(month, f.n, datefromparts(year(ds.StartDate), month(ds.StartDate), 1)))) StartDate,
iif(ds.EndDate='12/31/9999',
iif(f.n=0, dateadd(day, -1, fnm.NextMonth), ds.EndDate),
iif(f.n=datediff(month, ds.StartDate, fnm.NextMonth),
ds.EndDate,
dateadd(day, -1, dateadd(month, f.n+1, datefromparts(year(ds.StartDate), month(ds.StartDate), 1))))) EndDate
from
#date_split ds
cross apply
dbo.fnNextMonth(ds.StartDate, ds.EndDate) fnm
cross apply
dbo.fnTally(0, datediff(month, ds.StartDate, fnm.NextMonth)) f;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Awesome! Thank you so much!
-
One small update needed for this. sorry for updating requirement. While performing QA found it. Some member can have deceased populated , so loop should end based on deceased end. and other cases loop should till current date. Thanks!
Create Table #DATE_SPLIT
(
ID INT,
StartDate DATE,
EndDate DATE,
Deceased_Date DATE
)
INSERT INTO #DATE_SPLIT
SELECT 10,'01/10/2020','03/21/2020',NULL
UNION ALL
SELECT 10,'03/22/2020','12/31/9999',NULL
UNION ALL
SELECT 20,'01/01/2020','02/21/2020','03/20/2020'
UNION ALL
SELECT 20,'02/22/2020','12/31/9999','03/20/2020'
--OUTPUT
ID StartDate EndDate Deceased_Date
10 '01/10/2020' '01/31/2020' NULL
10 '02/01/2020' '02/29/2020' NULL
10 '03/01/2020' '03/21/2020' NULL
10 '03/22/2020' '03/31/2020' NULL
10 '04/01/2020' '04/30/2020' NULL
10 '05/01/2020' '05/31/2020' NULL
10 '06/01/2020' '06/30/2020' NULL
10 '07/01/2020' '12/31/9999' NULL
20 '01/10/2020' '01/31/2020' '03/20/2020'
20 '02/01/2020' '02/21/2020' '03/20/2020'
20 '02/22/2020' '02/29/2020' '03/20/2020'
20 '03/01/2020' '03/20/2020' '03/20/2020' ---LOOP Has to end here, since member is decease. thanks! -
Set End_Date = ISNULL (Deceased_date, End_Date)
_____________
Code for TallyGenerator -
It's been refactored into an fnSplitMonth function WITH SCHEMABINDING. Then I did what Sergiy suggested 🙂
/* updated */
drop table if exists #date_split;
go
Create Table #date_split(
ID INT,
StartDate DATE,
EndDate DATE,
Deceased_Date DATE);
go
insert #date_split(ID, StartDate, EndDate, Deceased_Date) values
(10, '01/10/2020', '03/21/2020', NULL),
(10, '03/22/2020', '12/31/9999', null),
(20, '01/01/2020', '02/21/2020', '03/20/2020'),
(20, '02/22/2020', '12/31/9999', '03/20/2020');
drop function if exists dbo.fnSplitMonth;
go
create function dbo.fnSplitMonth(
@StartDate date,
@EndDate date)
returns table with schemabinding as
return
select
iif(@EndDate='12/31/9999',
iif(f.n=0, @StartDate, nxt.NextMonth),
iif(f.n=0, @StartDate, dateadd(month, f.n-1, nxt.NextMonth))) SplitStartDate,
iif(@EndDate='12/31/9999',
iif(f.n=0, dateadd(day, -1, nxt.NextMonth), @EndDate),
iif(f.n=datediff(month, @StartDate, @EndDate),
@EndDate,
dateadd(day, -1, dateadd(month, f.n, nxt.NextMonth)))) SplitEndDate
from
(select dateadd(month, 1, datefromparts(year(@StartDate), month(@StartDate), 1)) NextMonth) nxt
cross apply
dbo.fnTally(0, datediff(month, @StartDate, iif(@EndDate='12/31/9999', nxt.NextMonth, @EndDate))) f;
go
select ds.*, fsm.*
from
#date_split ds
cross apply
dbo.fnSplitMonth(ds.StartDate, isnull(ds.Deceased_Date, ds.EndDate)) fsm
order by
1, 2, 5;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
There is a small glitch in data, please see attached screenshot. No 7 is not correct and 8,9 rows are dups if you take a look at split start and date.
-
Ok, if the end date is less than the deceased date, then use the end date, else use the deceased date.
select ds.*, fsm.*
from
#date_split ds
cross apply
dbo.fnSplitMonth(ds.StartDate, iif(ds.Deceased_Date is null, ds.EndDate, iif(ds.EndDate<ds.Deceased_Date, ds.EndDate, ds.Deceased_Date))) fsm
order by
1, 2, 5;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
With a Calendar table in place it seems to be easier:
select DS.*, C.Date, EOM,
CASE WHEN ds.StartDate > C.Date then ds.StartDate ELSE c.Date END SplitBegin,
case when EOM > GETDATE() then ds.EndDate
when ds.EndDate > EOM Then EOM
ELSE ds.EndDate end SplitEnd
from (
select ID, StartDate,
case when Deceased_Date < EndDate then Deceased_Date else EndDate end EndDate
from #date_split ) ds
inner join (
select Date, dateadd(dd, -1, dateadd(mm, 1, Date)) EOM
FROM dbo.Calendar
where DayOfMN = 1 and Date < GETDATE()
) C on C.Date >= dateadd(mm, datediff(mm, 0, ds.StartDate), 0)
and C.Date < ds.EndDate_____________
Code for TallyGenerator -
Afaik the OP didn't state the end dates couldn't be in the future. It seems safer to test against '9999-12-31'. Still WITH SCHEMABINDING so no physical table. Here's using the daterange function from this script:
https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function
drop function if exists dbo.fnSplitMonth;
go
create function dbo.fnSplitMonth(
@StartDate date,
@EndDate date,
@Deceased date=null)
returns table with schemabinding as
return
with
open_cte(SplitStartDate, SplitEndDate) as (
select @StartDate, eomonth(@StartDate) where @EndDate='9999-12-31' and @Deceased is null
union all
select dateadd(day, 1, eomonth(@StartDate)), '9999-12-31' where @EndDate='9999-12-31' and @Deceased is null),
closed_cte(SplitStartDate, SplitEndDate) as (
select
iif(dt.dt=@StartDate, @StartDate, dt.StartMonth),
iif(dt.EndMonth>ce.CalcEndDate, ce.CalcEndDate, dt.EndMonth)
from
(select iif(@Deceased is null, @EndDate, iif(@EndDate<@Deceased, @EndDate, @Deceased)) CalcEndDate) ce
cross apply
(select
cast(dr.[value] as date) dt,
datefromparts(year(dr.[value]), month(dr.[value]), 1) StartMonth,
eomonth(dr.[value]) EndMonth
from dbo.daterange(@StartDate, ce.CalcEndDate, 'mm', 1) dr
where not
(@EndDate='9999-12-31' and @Deceased is null)) dt)
select oc.SplitStartDate, oc.SplitEndDate from open_cte oc
union all
select cc.SplitStartDate, cc.SplitEndDate from closed_cte cc;
go
select ds.*, fsm.*
from
#date_split ds
cross apply
dbo.fnSplitMonth(ds.StartDate, ds.EndDate, ds.Deceased_Date) fsm
order by
1, 2, 5;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- Steve Collins wrote:
Afaik the OP didn't state the end dates couldn't be in the future.
He kinda did. Right here
Any other explanation why the splitting ends on July and does not go month by month all the way to 9999-12-31?
_____________
Code for TallyGenerator -
Hmmm... that's odd. Maybe we're both right until the OP clarifies?!?
When the OP first posted: Input
SELECT 10,'03/22/2020','12/31/9999'
Output:
10 '03/22/2020' '03/31/2020'
10 '04/01/2020' '12/31/9999'
Then the second post breaks down the same data differently (as you've shown). Then the 3rd post it was back to the way it was originally. My code works with 1st and 3rd examples and not the 2nd. Does it depend on when the report is run? Maybe
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply