Search code examples
sqlsql-serversql-server-2014-express

SQL query to find how many hours a person has worked in each life year

I have two tables

Person(id, birthdate)

and

Workday(personid, date, hours)

Both birthdate and date are on the form YYYYMMDD, stored as strings.

I need a query that returns a table that contains the age of a person (0, 1, 2...) and how many hours that person worked in that life year (a year will include the month of the person's birthday).

This is what I have so far:

SELECT CONVERT(INT, SUBSTRING(w.date, 0, 7)) - CONVERT(INT, SUBSTRING(p.birthdate, 0, 7)) AS age, w.hours AS totalhours
FROM Person AS p INNER JOIN Workday AS w ON p.id = w.personid
WHERE (p.person = @id)

This returns

Query return table

What I need is for all rows where 0 < age <= 100, age should be 0, and the total hours from these rows should be summed. Then, for 100 < age <= 200, age is 1, and so on...

Thanks!

Solution

  • Query over your query: 

      SELECT     ((age - 1)/100) as AgeRange, sum(totalhours)
  FROM         yourquery
  group by ((age -1)/100)