Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Get the First and Last Day of all the Months based on @ReportDate Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, October 17, 2013 4:19 PM
 Valued Member Group: General Forum Members Last Login: Wednesday, November 06, 2013 10:15 AM Points: 54, Visits: 96
 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?
Post #1505941
 Posted Thursday, October 17, 2013 4:45 PM
 SSCommitted Group: General Forum Members Last Login: Today @ 1:44 PM Points: 1,903, Visits: 4,229
 Could you give sample data and expected results? Luis C.Please don't trust me, test the solutions I give you before using them.Forum Etiquette: How to post data/code on a forum to get the best help
Post #1505944
 Posted Thursday, October 17, 2013 5:34 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 2:07 PM Points: 34,581, Visits: 28,768
 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 @pReportDateWITH 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 @pReportDateWITH 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"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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1505950
 Posted Friday, October 18, 2013 12:37 PM
 Valued Member Group: General Forum Members Last Login: Wednesday, November 06, 2013 10:15 AM Points: 54, Visits: 96
 Thank you both for your time to answer my post. Really appreciate it.
Post #1506296

 Permissions