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.

startdate is the "date" field that will be used to figure in the query for the fiscal year. Thanks — user1212436, Mar 14 '12 at 14:44

Andomar · Answer 1 · 2012-03-14T14:49:44.897

1

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')

edited Mar 14 '12 at 14:49

answered Mar 14 '12 at 14:41

Andomar

232,371
49
380
404

I didn't donwvote you, and I know that is a simple fix, but op asked for fiscal year and said that their fiscal year runs from July to June – Lamak Mar 14 '12 at 14:47

score 1 · Accepted Answer · 2012-03-14T15:23:37.260

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

edited Mar 14 '12 at 15:23

answered Mar 14 '12 at 15:03

There was a typo in my query that I have now corrected - if you are using SQLServer, this should now work. – Mar 14 '12 at 15:20
Just took out the extra "," in the second part of the where statement. I had to change date to datetime. This works for me. Thanks! – user1212436 Mar 14 '12 at 15:22
1

In SQL Server you can also `CAST('2012' AS date)`, which would result in the date of `2012-01-01`. So, for instance, the beginning of the current fiscal year could also be found like this: `DATEADD(MONTH, -6, CAST(DATENAME(YEAR, GETDATE()) AS date))`. – Andriy M Mar 14 '12 at 15:33

score 0 · Answer 3 · answered Mar 14 '12 at 14:47

0

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

answered Mar 14 '12 at 14:47

Arion

31,011
10
70
88

score 0 · Answer 4 · answered Mar 14 '12 at 14:51

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

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

4 Answers4

Linked