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?
SELECT GLAcctPeriod.FiscalYear, GLAcctPeriod.Period, GLAcctPeriod.PeriodDescription, GLAcctPeriod.PeriodBegDate, GLAcctPeriod.PeriodEndDate, SUM(WIP.Whours)
FROM GLAcctPeriod CROSS JOIN
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!!!