Need help to simplify/optimize sql query

[MrNorth]

Hi!

I ahve the following sql:

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

[smendoza]

It's hard to read in the way it's been pasted on the forum, once I reformatted it looks fine to me. I have a few querys that look like that I just made some quick notes so if I have to make changes to it because I only run the query once a month.

[szlamany]

Why are you doing sub-queries back to the same table - twice - with the same WHERE clause?

You can just SUM() that field - seems pretty clear.

Something like this - with the /* and */ stuff removed (it's commented out)

select CONCAT(r.firstname, ' ', r.lastname)  as name
, Sum(Case when RT.timetypeid in (1, 2, 3, 23) Then RT.hours Else 0 end) as Sum1
, Sum(Case when RT.timetypeid not in (1, 2, 3, 23) Then RT.hours Else 0 end) as Sum2
/*, (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;

[MrNorth]

Hi!

Thanks a lot, that query now looks a lot better. Using CASE was the best way here, thanks for pointing me in that direction! Subqueries are evil!

I noticed that there might be a bug in the code. This SQL generates a report for all time reports that occured on the previous week.

SELECT CONCAT(r.firstname, ' ', r.lastname)  as fullname
, IFNULL(Sum(Case when tt.timetypenumber in (10, 11,12) Then RT.hours Else 0 end),'') as sum1
, IFNULL(Sum(Case when tt.timetypenumber in (71, 72, 73, 74, 75, 76, 77, 78, 79, 80) Then RT.hours Else 0 end),'') as sum2
from reportedtime rt
inner join resource r on rt.resourceid = r.resourceid
inner join timetype tt on tt.timetypeid = rt.timetypeid
where WEEK(rt.reporteddate) = WEEK(NOW()) - 1
	and YEAR(rt.reporteddate) = YEAR(NOW())
group by rt.resourceid
order by r.firstname;

but what happens if it is new years eve, then YEAR is 2013 and week - 1 = 0

The correct thing would be Now.Week.Subtract(1) and Now.week.subtract(1).year

if u get my pseudo code... Is that at all possible?

If not I have to build a report form with some dropdown boxe for week and year and they have to generate it manually. As it is now, it runs every monday morning.

kind regards
Henrik

kind regards
Henrik

[szlamany]

Many ways to make "last month and year".

Is this ORACLE? Not familiar with IFNULL....

[TysonLPrice]

I was going to take a crack at it...it is definately not MS SQL. Here is a nice SQL formatter for future use:

http://www.dpriver.com/pp/sqlformat.htm

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;

[MrNorth]

Many ways to make "last month and year".

Is this ORACLE? Not familiar with IFNULL....

Sorry it is MYSQL, I forgot to mention that.


kind regards
Henrik