I'm trying to grab employee hours for the current fiscal year. What I want to do is take today's date, since the user will be interested in the current fiscal year, and if its before May 1st then the pay period BETWEEN would be May 1, 2011 - April 30, 2012. If the pay period is on or after May 1st then the pay period BETWEEN would be May 1, 2012 - April 30, 2013. I can assume that the end date of the range is the date that the report is being run (since no hours would be posted for a future period that hasn't happened yet). But how do I work in a condition in the middle of the main select statement? I don't want to create a table for this, I want it to work dynamically.
example I have working
SELECT Employee.Empfname + ' ' + WIP.WEmpLName AS Employee, SUM(WIP.Whours) AS Hours
FROM WIP INNER JOIN
Employee ON WIP.WempID = Employee.ID INNER JOIN
WHERE (WIP.Wdate BETWEEN
'05/01/' + '2011' + ' 12:00:00 AM' AND GETDATE())
I thought that I could swap out the 2011 with a conditional statement that would take the current year from today's date and + or - a year depending on where in the year it falls but I'm getting errors regarding formatting small date.
Can I replace the whole '05/01/' + '2011' + ' 12:00:00 AM' with a conditional select or can I create a variable that does the conditional testing before it gets to that part in the code?