We help IT Professionals succeed at work.
Get Started

Need help converting query to dynamic dates

705 Views
Last Modified: 2012-08-15
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!!!
Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2011
Commented:
This problem has been solved!
Unlock 2 Answers and 5 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE