Link to home
Start Free TrialLog in
Avatar of HSI_guelph
HSI_guelphFlag for Canada

asked on

Need help converting query to dynamic dates

I have a report User generated imageWhere 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!!!
SOLUTION
Avatar of Habib Pourfard
Habib Pourfard
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of HSI_guelph

ASKER

Thanks for responding!  My main problem is that GLAcctPeriod only stores the last 2 years of periods
User generated imageSo 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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!!