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.
PL-CustomRpt.doc
charkerrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
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.
0
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

MonthPeriods.png
fnMonthPeriods.txt
0
Chris LuttrellSenior Database ArchitectCommented:
That output was from the first call with nulls, defaulting to today for start and 20 months for end.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
charkerrAuthor Commented:
Thanks, I think this will work for what I need.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.