Hi!

I ahve the following sql:

Code:
select CONCAT(r.firstname, ' ', r.lastname)  as name, (select sum(rts.hours) 
from reportedtime rts
where rts.resourceid = rt.resourceid
and rts.timetypeid in (1, 2, 3, 23)
and WEEK(rts.reporteddate) = 30
and YEAR(rts.reporteddate) = 2012) as sum1,
(select sum(rtss.hours) 
from reportedtime rtss
where rtss.resourceid = rt.resourceid
and rtss.timetypeid NOT IN (1, 2, 3, 23)
and WEEK(rtss.reporteddate) = 30
and YEAR(rtss.reporteddate) = 2012) as sum2
from reportedtime rt
inner join resource r on rt.resourceid = r.resourceid
where WEEK(rt.reporteddate) = 30
and YEAR(rt.reporteddate) = 2012
group by rt.resourceid
order by r.lastname;

I can't figure out how to make this any more simple. Basically, the data is in two tables, one for the reported time, the other for the name of the person who reported it.

I need to select three columns, one with name, one with a sum based on certain timetypes, and another sum based on other timetypes. The only way I can figure this out is to use sub selects, but it looks rather clumpsy.

Anyone know a better way?

/Henrik