CODEWITHSUNDEEP
sqlsql-servert-sqlaggregatedate-range
TZERO
TZERO

Reputation: 25

Select aggregrate and date range

I can't figure this query out and it should be easy. But I'm at a loss. How do you query using an aggregate SUM bound by a date range?

Given this table:

ID   EmployeeID PayAmount   PayDate
1    48         289.0000        2003-12-22 00:00:00.000
2    251        458.0000        2003-12-30 00:00:00.000
3    48         248.0000        2003-12-30 00:00:00.000
4    167        255.5000        2003-12-30 00:00:00.000
5    48         100.00          2004-01-31 00:00:00.000
6    251        100.00          2004-01-31 00:00:00.000
7    251        300.00          2004-02-14 00:00:00:000

I would like to run a query to see how much each employee earned during a given year. So for 2003, the results would like this:

EmployeeID  TotalPaid 
48          537.00
167         255.50
251         458.00

For 2004 the results would be:

EmployeeID   TotalPaid
48           100.00
251          400.00

Upvotes: 1

Views: 61

Answers (1)

radar
radar

Reputation: 13425

In Microsoft SQL Server you can do like this. Grouping the data based on the Year for each EmployeeID The data can be filtered for a particular year using HAVING clause WITH YEAR function. This query gives data for the year 2004

SELECT EmployeeID, 
       SUM(PayAmount) as TotalPaid, 
       DATEADD(YEAR, DATEDIFF(YEAR,0, Paydate), 0)  as Year
FROM Table1
GROUP BY EmployeeID, DATEADD(YEAR, DATEDIFF(YEAR,0, Paydate), 0) 
HAVING YEAR(DATEADD(YEAR, DATEDIFF(YEAR,0, Paydate), 0)) =2004

Upvotes: 1

Related Questions