HSI_guelph
asked on
Need help converting query to dynamic dates
I have a report Where 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?
I could be because its Friday but my head hurts just thinking about this. All help is greatly appreciated and have a great weekend!!!
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
I could be because its Friday but my head hurts just thinking about this. All help is greatly appreciated and have a great weekend!!!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for responding! My main problem is that GLAcctPeriod only stores the last 2 years of periods
So 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.
So 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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!!
http://www.r-tag.com/Pages/MultivalueParametersWithSQLSP.aspx
if this is for SSRS try multivalue parameters :
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/c02370b5-aeda-47ec-a3a8-43b2ec1e6c26/