Using the @ReportingEndingDate, go back in time (@NumberOfPeriods)
1) Will take a starting and ending date range 2) Will do different'periods': Quarter, Month, or Year 3) Will do 'x' number of periods 4) Can start from the beginning date and go forward to the ending date or start from the ending date and go backward to the starting date. The former is obviously used regularly, however, the latter is used in cases where you want to pick an ending date and go backward 'x' number of periods.
Some scenarios: 1)
DECLARE @Start DATETIME = '2013-01-01 00:00:00'
,@End DATETIME = '2014-08-15 23:59:59'
,@Period TINYINT = 1 -- 1 = Quarter, 2 = Month, 3 = Year
,@NumerOfPeriods SMALLINT = 24
,@StartingDateIsOrigniationDate BIT = 1
SELECT * FROM udf_SplitDateRangeIntoPeriods(@Start, @End, @Period, @NumerOfPeriods, @StartingDateIsOrigniationDate)
1/1/2013 12:00:00 AM 3/31/2013 11:59:59 PM 4/1/2013 12:00:00 AM 6/30/2013 11:59:59 PM 7/1/2013 12:00:00 AM 9/30/2013 11:59:59 PM 10/1/2013 12:00:00 AM 12/31/2013 11:59:59 PM 1/1/2014 12:00:00 AM 3/31/2014 11:59:59 PM 4/1/2014 12:00:00 AM 6/30/2014 11:59:59 PM -- no more as last would be partial period
2)
DECLARE @Start DATETIME = '2013-01-01 00:00:00'
,@End DATETIME = '2014-08-15 23:59:59'
,@Period TINYINT = 1 -- 1 = Quarter, 2 = Month, 3 = Year
,@NumerOfPeriods SMALLINT = 24
,@StartingDateIsOrigniationDate BIT = 0
SELECT * FROM udf_SplitDateRangeIntoPeriods(@Start, @End, @Period, @NumerOfPeriods, @StartingDateIsOrigniationDate)
6/1/2014 12:00:00 AM 8/15/2014 11:59:59 PM -- partial period 3/1/2014 12:00:00 AM 5/31/2014 11:59:59 PM 12/1/2013 12:00:00 AM 2/28/2014 11:59:59 PM 9/1/2013 12:00:00 AM 11/30/2013 11:59:59 PM 6/1/2013 12:00:00 AM 8/31/2013 11:59:59 PM 3/1/2013 12:00:00 AM 5/31/2013 11:59:59 PM
/*
-- =============================================
Author: Daniel Kram
Create date: September 2014
Description: Returns a table with a date range
Modifications:
Example:
-- =============================================
*/
IF EXISTS (SELECT name
FROM
sysobjects
WHERE
name = 'udf_SplitDateRangeIntoPeriods'
AND type = 'TF'
)
DROP FUNCTION dbo.udf_SplitDateRangeIntoPeriods;
GO
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE FUNCTION dbo.udf_SplitDateRangeIntoPeriods
(
@StartingDate DATETIME,
@EndingDate DATETIME,
@PeriodType TINYINT = 0, -- 0 = use date-range only, 1 = quarter, 2 = month, 3 = year
@NumberOfPeriods SMALLINT = NULL,
@StartingDateIsFocus BIT = 1 -- 1 = Starting date is the origination point, 0 = ending date is the orignation point
)
RETURNS @DateResults TABLE
(FromDate DATETIME
,ToDate DATETIME
,MyRowNumber INT)
AS BEGIN
IF (@NumberOfPeriods IS NULL) OR (@NumberOfPeriods = 0)
SET @NumberOfPeriods = 99;
IF (@PeriodType = 0)
BEGIN
INSERT INTO @DateResults (FromDate, ToDate, MyRowNumber) SELECT @StartingDate, @EndingDate, 1
END
-- Quarters, working backwards from ending date
ELSE IF (@PeriodType = 1)
BEGIN
IF (@StartingDateIsFocus = 1)
BEGIN
WITH Quarters AS
(
SELECT @StartingDate AS 'FromDate'
,DATEADD(ss,-1,DATEADD(mm, DATEDIFF(m,0,@StartingDate)+3,0)) as 'ToDate'
,1 AS MyRowNumber
UNION ALL
SELECT CAST(DATEADD(d, 1, EOMONTH(ToDate)) AS DATETIME) AS 'FromDate'
,DATEADD(ss,-1,DATEADD(dd,1,CAST(EOMONTH(DATEADD(MONTH,3,ToDate)) AS DATETIME))) as 'ToDate'
,MyRowNumber + 1 AS MyRowNumber
FROM Quarters
WHERE ( (MyRowNumber <= @NumberOfPeriods)
OR (@NumberOfPeriods IS NULL)
)
AND MyRowNumber < 99
) INSERT INTO @DateResults (FromDate, ToDate, MyRowNumber) SELECT * FROM Quarters
WHERE ( (MyRowNumber <= @NumberOfPeriods)
OR (@NumberOfPeriods IS NULL)
)
AND FromDate >= @StartingDate
AND ToDate <= @EndingDate
AND MyRowNumber < 99
END
ELSE IF (@StartingDateIsFocus = 0)
BEGIN
WITH Quarters AS
(
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH,-2,DATEADD(day, -DATEPART(day, @EndingDate)+1, @EndingDate))), 0) as 'FromDate'
,@EndingDate AS 'ToDate'
,1 AS MyRowNumber
UNION ALL
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH,-3,DATEADD(day, -DATEPART(day, FromDate)+1, FromDate))), 0) as 'FromDate'
,DATEADD(ss,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH, -3, ToDate))+1,0)) AS 'ToDate'
,MyRowNumber + 1 AS MyRowNumber
FROM Quarters
WHERE ( (MyRowNumber <= @NumberOfPeriods)
OR (@NumberOfPeriods IS NULL)
)
AND FromDate >= @StartingDate
AND ToDate <= @EndingDate
AND MyRowNumber < 99
) INSERT INTO @DateResults (FromDate, ToDate, MyRowNumber) SELECT * FROM Quarters
WHERE ( (MyRowNumber <= @NumberOfPeriods)
OR (@NumberOfPeriods IS NULL)
)
AND FromDate >= @StartingDate
AND ToDate <= @EndingDate
AND MyRowNumber < 99
END
END
ELSE IF (@PeriodType = 2)
BEGIN
IF (@StartingDateIsFocus = 1)
BEGIN
WITH YearPeriod AS
(
SELECT @StartingDate AS 'FromDate'
,DATEADD(ss,-1,DATEADD(MONTH,1,@StartingDate)) as 'ToDate'
,1 AS MyRowNumber
UNION ALL
SELECT DATEADD(ss,1,ToDate) as 'FromDate'
,DATEADD(ss,-1,DATEADD(mm, DATEDIFF(m,0,ToDate)+2,0)) as 'ToDate'
,MyRowNumber + 1 AS MyRowNumber
FROM YearPeriod
WHERE ( (MyRowNumber <= @NumberOfPeriods)
OR (@NumberOfPeriods IS NULL)
)
AND FromDate >= @StartingDate
AND ToDate <= @EndingDate
AND MyRowNumber < 99
) INSERT INTO @DateResults (FromDate, ToDate, MyRowNumber) SELECT * FROM YearPeriod
WHERE ( (MyRowNumber <= @NumberOfPeriods)
OR (@NumberOfPeriods IS NULL)
)
AND FromDate >= @StartingDate
AND ToDate <= @EndingDate
AND MyRowNumber < 99
END
ELSE IF (@StartingDateIsFocus = 0)
BEGIN
WITH YearPeriod AS
(
SELECT DATEADD(ss,1,DATEADD(MONTH,-1,@EndingDate)) as 'FromDate'
,@EndingDate AS 'ToDate'
,1 AS MyRowNumber
UNION ALL
SELECT DATEADD(ss,1,DATEADD(MONTH,-1,FromDate)) as 'FromDate'
,DATEADD(MONTH,-1,ToDate) as 'ToDate'
,MyRowNumber + 1 AS MyRowNumber
FROM YearPeriod
WHERE ( (MyRowNumber <= @NumberOfPeriods)
OR (@NumberOfPeriods IS NULL)
)
AND FromDate >= @StartingDate
AND ToDate <= @EndingDate
AND MyRowNumber < 99
) INSERT INTO @DateResults (FromDate, ToDate, MyRowNumber) SELECT * FROM YearPeriod
WHERE ( (MyRowNumber <= @NumberOfPeriods)
OR (@NumberOfPeriods IS NULL)
)
AND FromDate >= @StartingDate
AND ToDate <= @EndingDate
AND MyRowNumber < 99
END
END
ELSE IF (@PeriodType = 3)
BEGIN
IF (@StartingDateIsFocus = 1)
BEGIN
WITH YearPeriod AS
(
SELECT @StartingDate AS 'FromDate'
,DATEADD(ss,-1,DATEADD(YEAR,1,@StartingDate)) as 'ToDate'
,1 AS MyRowNumber
UNION ALL
SELECT DATEADD(ss,1,ToDate) as 'FromDate'
,DATEADD(YEAR,1,ToDate) as 'ToDate'
,MyRowNumber + 1 AS MyRowNumber
FROM YearPeriod
WHERE ( (MyRowNumber <= @NumberOfPeriods)
OR (@NumberOfPeriods IS NULL)
)
AND FromDate >= @StartingDate
AND ToDate <= @EndingDate
AND MyRowNumber < 99
) INSERT INTO @DateResults (FromDate, ToDate, MyRowNumber) SELECT * FROM YearPeriod
WHERE ( (MyRowNumber <= @NumberOfPeriods)
OR (@NumberOfPeriods IS NULL)
)
AND FromDate >= @StartingDate
AND ToDate <= @EndingDate
AND MyRowNumber < 99
END
ELSE IF (@StartingDateIsFocus = 0)
BEGIN
WITH YearPeriod AS
(
SELECT DATEADD(ss,1,DATEADD(YEAR,-1,@EndingDate)) as 'FromDate'
,@EndingDate AS 'ToDate'
,1 AS MyRowNumber
UNION ALL
SELECT DATEADD(ss,1,DATEADD(YEAR,-1,FromDate)) as 'FromDate'
,DATEADD(ss,-1,DATEADD(YEAR,-1,ToDate)) as 'ToDate'
,MyRowNumber + 1 AS MyRowNumber
FROM YearPeriod
WHERE ( (MyRowNumber <= @NumberOfPeriods)
OR (@NumberOfPeriods IS NULL)
)
AND FromDate >= @StartingDate
AND ToDate <= @EndingDate
AND MyRowNumber < 99
) INSERT INTO @DateResults (FromDate, ToDate, MyRowNumber) SELECT * FROM YearPeriod
WHERE ( (MyRowNumber <= @NumberOfPeriods)
OR (@NumberOfPeriods IS NULL)
)
AND FromDate >= @StartingDate
AND ToDate <= @EndingDate
AND MyRowNumber < 99
END
END
RETURN
END
