• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Separating timeframe into month periods and sending as parameters to functions

I have a very complex query that has hardcoded parameters going to functions, and I need to be able to separate out monthly parameters from one start and one end date that could be up to 20 months apart.  I can create the parameter for the start and end date easily enough in SSRS, but I'm having trouble wrapping my brain around the way to break it into months, as the function is called for each month within the start and end dates.  The query is quite long, so I am attaching it as a file in case anyone is brave enough to look at it.  Don't worry about the whole thing, I'm just looking for a suggestion for the function parameters.
  • 2
1 Solution
If you have some startdate and enddate entered by user it can rise into unpredictable number of report columns...

Let suppose you have just startdate and you need to calculate beginning and end of several following months:

declare @startdt smalldatetime
set @startdt = '2009.05.31'
-- if above date contains the time part you have to strip it
-- set @startdt = convert(varchar(10), @startdt, 102)

-- current month
select dateadd(month, 0, @startdt-DAY(@startdt)+1), dateadd(month, 1, @startdt-DAY(@startdt)+1)-1
-- next month (just two constants change)
select dateadd(month, 1, @startdt-DAY(@startdt)+1), dateadd(month, 2, @startdt-DAY(@startdt)+1)-1
-- current month + 2 (just two constants change again)
select dateadd(month, 2, @startdt-DAY(@startdt)+1), dateadd(month, 3, @startdt-DAY(@startdt)+1)-1

Hope it helps.

The question is if the end date should not contain time part (23:59) which is necessary for correct comparisons at the end of the interval. Another possibility is to make end of month equal to the beginning of the next month.

Another question is why you use current date as the beginning of the interval for the curent month ('2009-05-20' in your example). Above example calculates each month from the 1st day.
Chris LuttrellSenior Database ArchitectCommented:
here is a table valued function that can be called like one of these and returns rows of month start and end dates.  See if you can make use of it.

--test cases
SELECT * FROM dbo.fnMonthPeriods(NULL,NULL) FMP
SELECT * FROM dbo.fnMonthPeriods('5/25/2009',NULL) FMP
SELECT * FROM dbo.fnMonthPeriods(NULL,'10/25/2009') FMP
SELECT * FROM dbo.fnMonthPeriods('5/25/2009','10/25/2009') FMP

Open in new window

Chris LuttrellSenior Database ArchitectCommented:
That output was from the first call with nulls, defaulting to today for start and 20 months for end.
charkerrAuthor Commented:
Thanks, I think this will work for what I need.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now