Calculate totals of field based on current fiscal year only - SQL

Question

I have seen many examples regarding calculating the sum of fields using the fiscal year, but I can not find one that fits my needs. What I am trying to do is get just the current fiscal year totals for a field using SQL Query. The fields I have is userid, startdate, total_hours, and missed_hours. Here is the query I have so far:

SELECT    
userid,    
SUM(total_hours) - SUM(missed_hours) AS hours    
FROM mytable    
GROUP BY userid

This works great, but all I need is the total number of hours for the current fiscal year for each of the userid's. Our fiscal year runs from July to June. I only need the current fiscal year and I need it to start over again this coming July.

Answer 1

Assuming this is SQLServer, try:

SELECT userid, SUM(total_hours) - SUM(missed_hours) AS hours    
FROM mytable
WHERE startdate >= cast( cast(year(dateadd(MM,-6,getdate())) as varchar(4)) + 
                         '-07-01' as date ) and
      startdate <  cast( cast(year(dateadd(MM, 6,getdate())) as varchar(4)) + 
                         '-07-01' as date )
GROUP BY userid

Answer 2

Add a where clause:

FROM mytable
WHERE startdate >= '2011-07-01'
GROUP BY userid

Or with the start of the year dynamically:

where startdate >= dateadd(yy, datepart(yy, getdate())-2001, '2000-07-01')

Answer 3

Maybe something like this:

SELECT
    userid,
    SUM(total_hours) - SUM(missed_hours) AS hours
FROM 
    mytable
WHERE 
    MONTH(startdate) BETWEEN 6 AND 7
    AND YEAR(startdate) IN (2011,2012)
GROUP BY userid

Answer 4

For the solution, two additional information is needed

• the name of the date column
• the vendor type of RDBMS you are using

I supposed your date column is date_col and you are using MySQL

SELECT    
userid,    
SUM(total_hours) - SUM(missed_hours) AS hours    
FROM mytable    
WHERE date_col between STR_TO_DATE('01,7,2011','%d,%m,%Y') and STR_TO_DATE('01,7,2010','%d,%m,%Y')
GROUP BY userid