CODEWITHSUNDEEP
sql
user1212436
user1212436

Reputation: 27

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

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.

Upvotes: 0

Views: 1242

Answers (4)

user359040
user359040

Reputation:

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

Upvotes: 1

bpgergo
bpgergo

Reputation: 16047

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

Upvotes: 0

Andomar
Andomar

Reputation: 238296

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

Upvotes: 1

Arion
Arion

Reputation: 31249

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

Upvotes: 0

Related Questions