Need help converting query to dynamic dates

I have a report Labour reportWhere I use the table GLAcctPeriod to define the Fiscal Year's periods (i.e. we begin our year May 1 so May 1, 2012 is Period 1 of FiscalYear 2013).  This works as long as I'm only looking at the current and previous fiscal years, there is no data from previous fiscal years.  I want my report to be default of 2 years but we may wish to look at a 5 year period which my current set up would not allow for.  

I was wondering if someone could help me figure out how to replace the GLAcctPeriod table with the code so I can pass multiple parameters (i.e. 2011, 2012, 2013 or 5 for five years) and have the  FiscalYear, Period, PeriodBegDate and PeriodEndDate filled in dynamically?

SELECT     GLAcctPeriod.FiscalYear, GLAcctPeriod.Period, GLAcctPeriod.PeriodDescription, GLAcctPeriod.PeriodBegDate, GLAcctPeriod.PeriodEndDate, SUM(WIP.Whours) 
                      AS Labour
FROM         GLAcctPeriod CROSS JOIN
                      WIP
WHERE     (WIP.Wdate BETWEEN GLAcctPeriod.PeriodBegDate AND GLAcctPeriod.PeriodEndDate) AND (WIP.WCodeCat = 'Bill')
GROUP BY GLAcctPeriod.FiscalYear, GLAcctPeriod.Period, GLAcctPeriod.PeriodDescription, GLAcctPeriod.PeriodBegDate, GLAcctPeriod.PeriodEndDate
ORDER BY GLAcctPeriod.FiscalYear, GLAcctPeriod.Period

Open in new window


I could be because its Friday but my head hurts just thinking about this.  All help is greatly appreciated and have a great weekend!!!
HSI_guelphAsked:
Who is Participating?
 
vastoCommented:
You can create a dynamic list of dates as it is shown in this example:
http://www.r-tag.com/Pages/Sample_OvertimeCalculation.aspx

Check the function fnGetDatesInPeriod (in the upper right corner). You might need to tweak it a little bit ( as it is now it returns all days in a period and you need just the months)

You can use a command and add a CTE directly to the command if you don't want to use an external function
0
 
Habib PourfardSoftware DeveloperCommented:
You can pass multiple parameters if you pass years as a comma separated string.
DECLARE	@FiscalYears VARCHAR(64)

SET @FiscalYears = '2010, 2011, 2012'

Open in new window


Then you need to split comma separated to multiple values. the following function do this:
CREATE FUNCTION [dbo].[Split] ( @List VARCHAR(MAX) )
RETURNS @ParsedList TABLE ( ID INT )
AS BEGIN
    DECLARE @ID VARCHAR(10),
        @Pos INT

    SET @List = LTRIM(RTRIM(@List)) + ','
    SET @Pos = CHARINDEX(',', @List, 1)

    IF REPLACE(@List, ',', '') <> '' 
        BEGIN
            WHILE @Pos > 0
                BEGIN
                    SET @ID = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
                    IF @ID <> '' 
                        BEGIN
                            INSERT  INTO @ParsedList ( ID )
                            VALUES  ( CAST(@ID AS INT) )
                        END
                    SET @List = RIGHT(@List, LEN(@List) - @Pos)
                    SET @Pos = CHARINDEX(',', @List, 1)

                END
        END	
    RETURN
   END

Open in new window



Now you can build your dynamic query:

WHERE GLAcctPeriod.FiscalYear IN (SELECT * FROM  dbo.SplitIDs(@FiscalYears))

Open in new window


Your query could be written like this:
DECLARE	@FiscalYears VARCHAR(64)

SET @FiscalYears = '2008, 2009, 2010, 2011, 2012'

SELECT     GLAcctPeriod.FiscalYear, GLAcctPeriod.Period, GLAcctPeriod.PeriodDescription, GLAcctPeriod.PeriodBegDate, GLAcctPeriod.PeriodEndDate, SUM(WIP.Whours) 
                      AS Labour
FROM         GLAcctPeriod CROSS JOIN
                      WIP
WHERE     (WIP.Wdate BETWEEN GLAcctPeriod.PeriodBegDate AND GLAcctPeriod.PeriodEndDate) AND (WIP.WCodeCat = 'Bill')
AND GLAcctPeriod.FiscalYear IN (SELECT * FROM  dbo.SplitIDs(@FiscalYears))
GROUP BY GLAcctPeriod.FiscalYear, GLAcctPeriod.Period, GLAcctPeriod.PeriodDescription, GLAcctPeriod.PeriodBegDate, GLAcctPeriod.PeriodEndDate
ORDER BY GLAcctPeriod.FiscalYear, GLAcctPeriod.Period

Open in new window

0
 
HSI_guelphAuthor Commented:
Thanks for responding!  My main problem is that GLAcctPeriod only stores the last 2 years of periods
only data in GLAcctPeriodSo I think I need to remove the table reference and dynamically set the information to get previous fiscal years.

I don't have access to create stored procedures so I'm working with what I got in dataset queries.
0
 
HSI_guelphAuthor Commented:
Thank you for the replies!!  I will try the function but I might just make a table with dates in it to get around this in the meantime.  TYVM!!
0
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.

All Courses

From novice to tech pro — start learning today.