October 17, 2013 at 4:19 pm
Is there a way to get the First and Last Day of all the months (including current) based on the @ReportDate for current year and previous year?
October 17, 2013 at 4:45 pm
Could you give sample data and expected results?
October 17, 2013 at 5:34 pm
sql1411 (10/17/2013)
Is there a way to get the First and Last Day of all the months (including current) based on the @ReportDate for current year and previous year?
Yes.
--===== Create and populate the @ReportDate parameter
-- which could be used as an input to a function
-- or stored procedure.
DECLARE @pReportDate DATETIME;
SELECT @pReportDate = GETDATE();
--===== Produce 2 years of monthly start and end dates
-- to include the current year of @pReportDate
WITH
B1(N) AS (SELECT 1 UNION ALL SELECT 1),
B5(N) AS (SELECT 1 FROM B1 a, B1 b, B1 c, B1 d, B1 e),
cteTally(N) AS (SELECT TOP 24 ROW_NUMBER() OVER (ORDER BY B5.N) FROM B5)
SELECT MonthStart = DATEADD(mm,t.N-1,ca.StartDate)
,MonthEnd = DATEADD(mm,t.N ,ca.StartDate)-1
FROM cteTally t
CROSS APPLY (SELECT DATEADD(yy,DATEDIFF(yy,0,@pReportDate)-1,0)) ca (StartDate)
;
However, to make aggregations easier to include any times in the datetimes of the data for reporting, I'd make the code return the first of the month and the first of the following month.
--===== Create and populate the @ReportDate parameter
-- which could be used as an input to a function
-- or stored procedure.
DECLARE @pReportDate DATETIME;
SELECT @pReportDate = GETDATE();
--===== Produce 2 years of monthly start and next month start dates
-- to include the current year of @pReportDate
WITH
B1(N) AS (SELECT 1 UNION ALL SELECT 1),
B5(N) AS (SELECT 1 FROM B1 a, B1 b, B1 c, B1 d, B1 e),
cteTally(N) AS (SELECT TOP 24 ROW_NUMBER() OVER (ORDER BY B5.N) FROM B5)
SELECT MonthStart = DATEADD(mm,t.N-1,ca.StartDate)
,NextMonthStart = DATEADD(mm,t.N ,ca.StartDate)
FROM cteTally t
CROSS APPLY (SELECT DATEADD(yy,DATEDIFF(yy,0,@pReportDate)-1,0)) ca (StartDate)
;
--Jeff Moden
October 18, 2013 at 12:37 pm
Thank you both for your time to answer my post. Really appreciate it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply