Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get the First and Last Day of all the Months based on @ReportDate Expand / Collapse
Author
Message
Posted Thursday, October 17, 2013 4:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:43 PM
Points: 3,912, Visits: 8,858
Could you give sample data and expected results?


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:34 PM
Points: 35,534, Visits: 32,117
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1505950
Posted Friday, October 18, 2013 12:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 10:15 AM
Points: 54, Visits: 96
Thank you both for your time to answer my post. Really appreciate it.
Post #1506296
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse