How to get Active counts between dates. Please
-
Good Morning ,
I have a policy table with below structure. I would like to get how many active policies by day (running total may be).
Create Table #Policy (PolNumber int, StartDate date, EndDate date, Status varchar(10))
Insert Into #Policy Values (100,'2023-01-04','2024-02-22','Active')
Insert Into #Policy Values (100,'2021-01-04','2022-02-04','InActive')
Insert Into #Policy Values (101,'2022-01-01','2025-12-26','Active')
Insert Into #Policy Values (102,'2024-02-01','2024-12-16','Active')
Insert Into #Policy Values (104,'2023-06-24','2024-05-31','Active')
Select * from #policy where status = 'Active' which means enddate> currentdate. we need to provide running totals how many policy active on particular day
expecting outcome is, (from the Min of startday (only active) till max(enddate) daily running total of active polcies
Date ActivePolicies
2022-01-01 1
2022-01-02 1
2022-01-03 1
2022-01-04 1
2022-01-05 1
2022-01-06 1
,......
.....
2023-01-04 2 (as of 2023-01-04 policy 101 and 100 active)
2023-01-05 2
2023-01-06 2
.........
2023-06-24 3 (101,100, 104 active)
2023-06-25 3
..............
2024-01-01 3 (as of 2024-01-01 policy 101, 100, 104 active)
....
2024-02-01 4 (as of 2024-01-01 policy 101, 100, 104, 102 active)
......
2024-02-22 3 (100 enddate become effect so only 3 active)
Can you please help with this.
Thank you in advance
Asiti
-
Here's how we'll do it:
- Create a Date List: First up, we're going to make a list of dates. This list starts from the earliest start date of all your policies and goes all the way to the latest end date. It's like marking every single day on a calendar within that range.
- Count the Policies: Now, for each day on our list, we'll count how many policies were active. We consider a policy active if the day falls between its start and end dates and its status is 'Active'.
-- First, we make our list of dates
;WITH DateSeries AS (
SELECT MIN(StartDate) AS Date
FROM #Policy
UNION ALL
SELECT DATEADD(DAY, 1, Date)
FROM DateSeries
WHERE Date < (SELECT MAX(EndDate) FROM #Policy)
),
-- Then, we count active policies for each day
ActivePolicies AS (
SELECT
ds.Date,
COUNT(p.PolNumber) AS ActivePolicies
FROM
DateSeries ds
LEFT JOIN #Policy p ON ds.Date BETWEEN p.StartDate AND p.EndDate AND p.Status = 'Active'
GROUP BY
ds.Date
)
SELECT * FROM ActivePolicies
OPTION (MAXRECURSION 0); -- This helps us avoid hitting a limit on how many days we can look at.The DateSeries part creates our calendar of dates we're interested in.
The ActivePolicies part then figures out how many policies are saying "Here!" for roll call on each of those dates.
Just remember, when running this, we're telling SQL Server to be ready for a lot of dates (OPTION (MAXRECURSION 0)), so give it a minute if it needs to think.
-
Good Morning Ali,
Thank you much for clear explanation, I am getting error "Msg 467, Level 16, State 1, Line 2
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'DateSeries'."
Any idea why this is happening? you think better to use temp tables rather cte? just incase if that resolves this
Please help
Thanks
-
I did this now my temp table has data from 2006 to 2112 about 36k records. Thanks
-- Create a table variable to hold the date range
DECLARE @StartDate DATE, @EndDate DATE
SELECT @StartDate = MIN(Start_Date), @EndDate = MAX(CASE WHEN end_Date='9999-01-01' THEN NULL ELSE end_Date end) FROM [dbo].[Policy_Dim]
where Status = 'Current'
select @StartDate, @EndDate, DATEDIFF(DAY, @StartDate, @EndDate);
WITH DateSeries AS (
SELECT @StartDate AS Date
UNION ALL
SELECT DATEADD(DAY, 1, Date)
FROM DateSeries
WHERE Date < @EndDate
)
SELECT Date into #a2
FROM DateSeries
OPTION (MAXRECURSION 0);
-
Recursion is notoriously slow for iteration. Maybe use a "standard" tally table instead:
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
SELECT 0 AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
),
cte_tally10K AS (
SELECT 0 AS number UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally100 c1 CROSS JOIN cte_tally100 c2
),
policy_date_range AS (
SELECT MIN(StartDate) AS MinStartDate, CASE WHEN MAX(EndDate) > CAST(GETDATE() AS date) THEN CAST(GETDATE() AS date) ELSE MAX(EndDate) END AS MaxEndDate
FROM #Policy
)
SELECT DATEADD(DAY, t.number, pdr.MinStartDate) AS PolicyDay,
SUM(CASE WHEN P.Status = 'Active' THEN 1 ELSE 0 END) AS PolicyDayCount
FROM #Policy p
CROSS JOIN policy_date_range pdr
INNER JOIN cte_tally10K t ON t.number BETWEEN DATEDIFF(DAY, pdr.MinStartDate, p.StartDate) AND
DATEDIFF(DAY, pdr.MinStartDate, CASE WHEN p.EndDate > CAST(GETDATE() AS date) OR p.EndDate > pdr.MaxEndDate THEN CAST(GETDATE() AS date) ELSE p.EndDate END)
GROUP BY pdr.MinStartDate, pdr.MaxEndDate, t.number
ORDER BY 1SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Scott's advice is solid. I suggest you compare execution times between the solutions offered and then you'll see why.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Ali Varzeshi wrote:
Here's how we'll do it:
- Create a Date List: First up, we're going to make a list of dates. This list starts from the earliest start date of all your policies and goes all the way to the latest end date. It's like marking every single day on a calendar within that range.
- Count the Policies: Now, for each day on our list, we'll count how many policies were active. We consider a policy active if the day falls between its start and end dates and its status is 'Active'.
-- First, we make our list of dates
;WITH DateSeries AS (
SELECT MIN(StartDate) AS Date
FROM #Policy
UNION ALL
SELECT DATEADD(DAY, 1, Date)
FROM DateSeries
WHERE Date < (SELECT MAX(EndDate) FROM #Policy)
),
-- Then, we count active policies for each day
ActivePolicies AS (
SELECT
ds.Date,
COUNT(p.PolNumber) AS ActivePolicies
FROM
DateSeries ds
LEFT JOIN #Policy p ON ds.Date BETWEEN p.StartDate AND p.EndDate AND p.Status = 'Active'
GROUP BY
ds.Date
)
SELECT * FROM ActivePolicies
OPTION (MAXRECURSION 0); -- This helps us avoid hitting a limit on how many days we can look at.The DateSeries part creates our calendar of dates we're interested in. The ActivePolicies part then figures out how many policies are saying "Here!" for roll call on each of those dates. Just remember, when running this, we're telling SQL Server to be ready for a lot of dates (OPTION (MAXRECURSION 0)), so give it a minute if it needs to think.
Hello, Ali Varzeshi and welcome aboard.
Scott and Phil are correct. Please see the following article for why generating dates and doing other incremental counts using a Recursive CTE is one of the worst practices ever. The article also has 3 different alternatives that blow the doors off of Recursive CTEs both performance and resource usage wise.
https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Using a tally function or GENERATE_SERIES maybe something like this
/* expand the date range using a tally function */
select v.calc_dt, count(*) active_policies
from #Policy p
cross apply dbo.fnTally(0, datediff(day, p.StartDate, p.EndDate)) fn
cross apply (values (dateadd(day, fn.n, p.StartDate))) v(calc_dt)
where p.[Status]='Active'
group by v.calc_dt
order by v.calc_dt;
/* expand the date range using generate_series function */
select v.calc_dt, count(*) active_policies
from #Policy p
cross apply generate_series(0, datediff(day, p.StartDate, p.EndDate), 1) fn
cross apply (values (dateadd(day, fn.n, p.StartDate))) v(calc_dt)
where p.[Status]='Active'
group by v.calc_dt
order by v.calc_dt;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Or maybe create and keep a table, Calendar, that contains a record for each date in given interval. If your calendar table contains all dates from say, 1950-01-01 to 2150-12-31 you can pull out any date range yo need. There were several good articles on Calendar tables on this site. With decent calendar table you do not have to worry about this or that algorithm. set it and forget it. Or use the function Jeff M. provided.
Zidar's Theorem: The best code is no code at all...
-
Here is a very efficient way of doing this.
😎
Comments in the code.
USE TEEST;
-----------------------------------------------------------------------------------------
-- WHEN POSTING CODE ONLINE, I MAKE CERTAIN THAT IT POINTS TO A NON-EXISTING DATABASE TO
-- PREVENT INEXPERIENCED USERS CAUSING DAMAGE ON ACTUAL SYSTEMS!
-----------------------------------------------------------------------------------------
GO
SET NOCOUNT ON;
GO
-----------------------------------------------------------------------------------------
-- THE SAMPLE DATA SET
-----------------------------------------------------------------------------------------
--Create Table #Policy (PolNumber int, StartDate date, EndDate date, Status varchar(10))
--Insert Into #Policy Values (100,'2023-01-04','2024-02-22','Active')
--Insert Into #Policy Values (100,'2021-01-04','2022-02-04','InActive')
--Insert Into #Policy Values (101,'2022-01-01','2025-12-26','Active')
--Insert Into #Policy Values (102,'2024-02-01','2024-12-16','Active')
--Insert Into #Policy Values (104,'2023-06-24','2024-05-31','Active')
--;
-----------------------------------------------------------------------------------------
-- THE T(N) CTE PROVIDES A LIST OF 10 VALUES AS A SEED FOR THE NUMBER GENERATION NEEDED
-- FOR THE GENERATION OF THE COMPLETE DATE LIST.
-----------------------------------------------------------------------------------------
;WITH T(N) AS (SELECT N FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS T(N))
-----------------------------------------------------------------------------------------
-- THE DATES() CTE PROVIDES THE NUMBER OF DATES AND THE BOUNDRIES AS START AND END DATES.
-- THIS MAKES THE CODE DYNAMIC IN DATE RANGE AND SELF ADJUSTING TO THE VALUES OF THE
-- DATA.
-----------------------------------------------------------------------------------------
,DATES ( FIRST_DATE, LAST_DATE, DAY_COUNT) AS
(
SELECT
-- NO NEED FOR A TOP(1) HERE!
MIN(POL.StartDate) AS FIRST_DATE
,MAX(POL.EndDate) AS LAST_DATE
,DATEDIFF(DAY,MIN(POL.StartDate),MAX(POL.EndDate)) AS DAY_COUNT
FROM #Policy POL
)
-----------------------------------------------------------------------------------------
-- THE NUMS(N) CTE PROVIDES A LIST OF NUMBERS, STARTING AT 0. THIS WILL BE GENERATED TO
-- THE CARDINALITY OF DAY_COUNT + 1. THE REASON FOR STARTING AT 0 IS THAT THE VALUES ARE
-- USED IN THE DATEADD FUNCTION LATER IN THE CODE. THIS IS TO INCLUDE THE START DATE.
-----------------------------------------------------------------------------------------
,NUMS(N) AS
(
SELECT 0
UNION ALL
SELECT
TOP((SELECT DAY_COUNT FROM DATES)) ROW_NUMBER() OVER (ORDER BY (SELECT @@VERSION)) AS N
FROM DATES D
CROSS APPLY T T1, T T2, T T3, T T4, T T5
)
-----------------------------------------------------------------------------------------
-- THE DATE_LIST(XDAY) GENERATES A SET OF DATES THAT ARE IN SCOPE GIVEN THE VALUES FOUND
-- IN THE SAMPLE DATA.
-----------------------------------------------------------------------------------------
,DATE_LIST AS
(
SELECT TOP((SELECT DAY_COUNT + 1 FROM DATES))
DATEADD ( DAY, NM.N, D.FIRST_DATE) AS XDAY
FROM NUMS NM
CROSS APPLY DATES D
ORDER BY NM.N ASC
)
-----------------------------------------------------------------------------------------
-- THE FINAL QUERY PROVIDES THE COUNT OF ACTIVE POLICIES BY SUMMING THE SIGN OF THE
-- POLICY NUMBERS THAT ARE ACTIVE ON EACH DAY IN THE DATE_LIST.
-- NOTE: THE POLICY NUMBERS MUST BE A POSITIVE NUMBER GREATER THAN ZERO.
-- NOTE: THE INACTIVE POLICIES ARE EXCLUDED AS THOSE ARE NOT NEEDED.
-----------------------------------------------------------------------------------------
SELECT
DL.XDAY
,SUM(SIGN(POL.PolNumber)) AS ACTIVE_COUNT
FROM DATE_LIST DL
LEFT OUTER JOIN #Policy POL
ON DL.XDAY >= POL.StartDate AND DL.XDAY < POL.EndDate
WHERE POL.Status = 'Active'
GROUP BY DL.XDAY
ORDER BY DL.XDAY ASC;
-----------------------------------------------------------------------------------------
-- CLEAN UP
-----------------------------------------------------------------------------------------
--DROP TABLE #Policy;
----------------------------------------------------------------------------------------- - ScottPletcher wrote:
Recursion is notoriously slow for iteration. Maybe use a "standard" tally table instead:
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
SELECT 0 AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
),
cte_tally10K AS (
SELECT 0 AS number UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally100 c1 CROSS JOIN cte_tally100 c2
),
policy_date_range AS (
SELECT MIN(StartDate) AS MinStartDate, CASE WHEN MAX(EndDate) > CAST(GETDATE() AS date) THEN CAST(GETDATE() AS date) ELSE MAX(EndDate) END AS MaxEndDate
FROM #Policy
)
SELECT DATEADD(DAY, t.number, pdr.MinStartDate) AS PolicyDay,
SUM(CASE WHEN P.Status = 'Active' THEN 1 ELSE 0 END) AS PolicyDayCount
FROM #Policy p
CROSS JOIN policy_date_range pdr
INNER JOIN cte_tally10K t ON t.number BETWEEN DATEDIFF(DAY, pdr.MinStartDate, p.StartDate) AND
DATEDIFF(DAY, pdr.MinStartDate, CASE WHEN p.EndDate > CAST(GETDATE() AS date) OR p.EndDate > pdr.MaxEndDate THEN CAST(GETDATE() AS date) ELSE p.EndDate END)
GROUP BY pdr.MinStartDate, pdr.MaxEndDate, t.number
ORDER BY 1This code would never get my approval!
There are quite a few opportunities for improving this query, here are some of the obvious ones:
😎
1. The cardinality of the Tally CTE is far too high, 10.000 rows when less than 1200 are needed.
2. There are no cardinality reductions before the Tally output is joined to the sample data, resulting in a set generation of 50.000 rows. 3. This introduces a Lazy Table Spool operator that feeds into a Nested Loops inner join operation that does almost 2/3rds of the query work.
4. There is a join that has functions on the join predicates, resulting in a Hash Match operator, a potential time bomb if the cardinality is increased.
-
IMHO, Steve Collins' post (with a tiny tweak (change fn.n to fn.value) in the 2nd bit of code) contains the winning entries for simplicity IF you don't mind any missing dates not showing up.
If you want missing embedded dates to show up with zeros, the we can do the following, which is a bit of a simple trick to totally avoid the expense of a join. Details in the comments. Also, since this was posted in a 2022 forum, I use GENERATE_SERIES() and haven't submitted a separate fnTally version.
WITH
cteDateRange AS
(--==== Add 1 row from the full range of dates with IsReal = 0 so WON'T be counted.
-- This keeps us from the expense of doing a join.
SELECT StartDate = MIN(pol.StartDate)
,DateRange = DATEDIFF(dd,MIN(pol.StartDate),MAX(pol.EndDate))
,IsReal = 0
FROM #Policy pol
WHERE Status = 'Active'
UNION ALL
--==== Calculate the date range for each row with IsReal = 1 so WILL be counted.
SELECT StartDate
,DateRange = DATEDIFF(dd,pol.StartDate,pol.EndDate)
,IsReal = 1
FROM #Policy pol
WHERE Status = 'Active'
)--==== Gen the dates and add up IsReal to count the policies for each day.
SELECT [Date] = DATEADD(dd,t.value,drng.StartDate)
,ActivePolicies = SUM(IsReal)
FROM cteDateRange drng
CROSS APPLY GENERATE_SERIES(0,drng.DateRange) t
GROUP BY DATEADD(dd,t.value,drng.StartDate)
ORDER BY [Date]
;There should also be a way to generate the policy count without having to generate dates for each range using an Itzik Ben-Gan style "pack intervals", but I'm going to pass on that for the evening.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If you install this DateRange function, this gives certainly the shortest SQL if not the fastest
select CONVERT(date, d.value) Date,
COUNT(*) ActivePolicies
from #Policy p
cross apply dbo.DateRange(p.StartDate, p.EndDate, 'dd', 1) d
where p.status = 'Active'
group by d.value
order by d.value
; - Jonathan AC Roberts wrote:
If you install this DateRange function, this gives certainly the shortest SQL if not the fastest
select CONVERT(date, d.value) Date,
COUNT(*) ActivePolicies
from #Policy p
cross apply dbo.DateRange(p.StartDate, p.EndDate, 'dd', 1) d
where p.status = 'Active'
group by d.value
order by d.value
;As with other code, that won't return embedded "missing dates" with a zero if they exist (and neither will the "other" code below).
Also, while it IS good, fast, and very useful, it's actually not the fastest solution in this case. Here's the test code...
--********************************************************************************
SET NOCOUNT ON;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS ;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS ;
GO
--===== Jonathan =================================================================
select CONVERT(date, d.value) Date,
COUNT(*) ActivePolicies
from #Policy p
cross apply dbo.DateRange(p.StartDate, p.EndDate, 'dd', 1) d
where p.status = 'Active'
group by d.value
order by d.value
;
GO 5
--********************************************************************************
SET NOCOUNT ON;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS ;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS ;
GO
--===== Generate_Series ==========================================================
SELECT [Date] = DATEADD(dd,t.value,pol.StartDate)
,ActivePolicies = COUNT(*)
FROM #Policy pol
CROSS APPLY GENERATE_SERIES(0,DATEDIFF(dd,pol.StartDate,pol.EndDate)) t
WHERE pol.Status = 'Active'
GROUP BY DATEADD(dd,t.value,pol.StartDate)
;
GO 5
SET NOCOUNT OFF;And here are the SQL Profiler results.
Don't get me wrong... the DateRange function is incredibly useful and absolutely does make for some simple code and the performance hit for that good bit of flexibility is relatively tiny, but it isn't the fastest solution in this case.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Jonathan AC Roberts wrote:
If you install this DateRange function, this gives certainly the shortest SQL if not the fastest
select CONVERT(date, d.value) Date,
COUNT(*) ActivePolicies
from #Policy p
cross apply dbo.DateRange(p.StartDate, p.EndDate, 'dd', 1) d
where p.status = 'Active'
group by d.value
order by d.value
;As with other code, that won't return embedded "missing dates" with a zero if they exist (and neither will the "other" code below).
Also, while it IS good, fast, and very useful, it's actually not the fastest solution in this case. Here's the test code...
--********************************************************************************
SET NOCOUNT ON;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS ;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS ;
GO
--===== Jonathan =================================================================
select CONVERT(date, d.value) Date,
COUNT(*) ActivePolicies
from #Policy p
cross apply dbo.DateRange(p.StartDate, p.EndDate, 'dd', 1) d
where p.status = 'Active'
group by d.value
order by d.value
;
GO 5
--********************************************************************************
SET NOCOUNT ON;
CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS ;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS ;
GO
--===== Generate_Series ==========================================================
SELECT [Date] = DATEADD(dd,t.value,pol.StartDate)
,ActivePolicies = COUNT(*)
FROM #Policy pol
CROSS APPLY GENERATE_SERIES(0,DATEDIFF(dd,pol.StartDate,pol.EndDate)) t
WHERE pol.Status = 'Active'
GROUP BY DATEADD(dd,t.value,pol.StartDate)
;
GO 5
SET NOCOUNT OFF;And here are the SQL Profiler results.
Don't get me wrong... the DateRange function is incredibly useful and absolutely does make for some simple code and the performance hit for that good bit of flexibility is relatively tiny, but it isn't the fastest solution in this case.
Ok, yours is faster and about the same length SQL.
The SQL 2022 GENERATE_SERIES is faster than the traditional method of generating a tally. I thought I'd implement the DateRange function to use GENERATE_SERIES:
CREATE FUNCTION [dbo].[DateRange2022]
(
@StartDate datetime2,
@EndDate datetime2,
@DatePart nvarchar(3)='dd',
@Interval int=1
)
RETURNS TABLE AS RETURN
SELECT CASE @DatePart
WHEN 'ns' THEN DATEADD(ns, T.AddAmount, @StartDate)
WHEN 'mcs' THEN DATEADD(mcs,T.AddAmount, @StartDate)
WHEN 'ms' THEN DATEADD(ms, T.AddAmount, @StartDate)
WHEN 'ss' THEN DATEADD(ss, T.AddAmount, @StartDate)
WHEN 'mi' THEN DATEADD(mi, T.AddAmount, @StartDate)
WHEN 'hh' THEN DATEADD(hh, T.AddAmount, @StartDate)
WHEN 'dd' THEN DATEADD(dd, T.AddAmount, @StartDate)
WHEN 'ww' THEN DATEADD(ww, T.AddAmount, @StartDate)
WHEN 'mm' THEN DATEADD(mm, T.AddAmount, @StartDate)
WHEN 'qq' THEN DATEADD(qq, T.AddAmount, @StartDate)
WHEN 'yy' THEN DATEADD(yy, T.AddAmount, @StartDate)
ELSE DATEADD(dd, T.AddAmount, @StartDate)
END [Value]
FROM GENERATE_SERIES(0,ABS(CASE @DatePart
WHEN 'ns' THEN DATEDIFF(ns, @EndDate, @StartDate)/@Interval
WHEN 'mcs' THEN DATEDIFF(mcs,@EndDate, @StartDate)/@Interval
WHEN 'ms' THEN DATEDIFF(ms, @EndDate, @StartDate)/@Interval
WHEN 'ss' THEN DATEDIFF(ss, @EndDate, @StartDate)/@Interval
WHEN 'mi' THEN DATEDIFF(mi, @EndDate, @StartDate)/@Interval
WHEN 'hh' THEN DATEDIFF(hh, @EndDate, @StartDate)/@Interval
WHEN 'dd' THEN DATEDIFF(dd, @EndDate, @StartDate)/@Interval
WHEN 'ww' THEN DATEDIFF(ww, @EndDate, @StartDate)/@Interval
WHEN 'mm' THEN DATEDIFF(mm, @EndDate, @StartDate)/@Interval
WHEN 'qq' THEN DATEDIFF(qq, @EndDate, @StartDate)/@Interval
WHEN 'yy' THEN DATEDIFF(yy, @EndDate, @StartDate)/@Interval
ELSE DATEDIFF(dd, IIF(@StartDate < @EndDate, @StartDate, @EndDate), IIF(@StartDate < @EndDate, @EndDate, @StartDate))/@Interval
END), 1)
CROSS APPLY(VALUES (IIF(@StartDate<@EndDate, @interval*value, @interval*-value))) T(AddAmount)
GOSurprisingly, to me, this is a bit slower than the original DateRange function.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply