query optimization - large report 1

[NorthStarDA]

i am writing a query where i need to get a count of transaction types from a table for employees within a certain timeframe. I have a table for work_history with columns time_in and time_out. And there is a transactionHistory table where i want to get my counts. here is an example

SELECT CONCAT(e.last_name, ', ', e.first_name) AS employeeName,th.employeeID,
	(SELECT SUM(TIME_TO_SEC(h.time_out)-TIME_TO_SEC(h.time_in)) / 3600
	FROM corporate.workHistory h
	WHERE h.employee_ID = th.employeeID
	AND h.project_ID = 418
	AND h.work_date = now()
	) totalHours,
	(SELECT COUNT(th2.transactionID) FROM transactionHistory th2 WHERE th2.transactionValue = '1' AND th2.transactionTypeID = '1' AND th2.employeeID = e.ID AND date(th2.transactionDate) = CURDATE()) status1,
	(SELECT COUNT(th2.transactionID) FROM transactionHistory th2 WHERE th2.transactionValue = '2' AND th2.transactionTypeID = '1' AND th2.employeeID = e.ID AND date(th2.transactionDate) = CURDATE()) status2,
	(SELECT COUNT(th2.transactionID) FROM transactionHistory th2 WHERE th2.transactionValue = '3' AND th2.transactionTypeID = '1' AND th2.employeeID = e.ID AND date(th2.transactionDate) = CURDATE()) status3,
	(SELECT COUNT(th2.transactionID) FROM transactionHistory th2 WHERE th2.transactionValue = '7' AND th2.transactionTypeID = '1' AND th2.employeeID = e.ID AND date(th2.transactionDate) = CURDATE()) status4,
	(SELECT COUNT(th2.transactionID) FROM transactionHistory th2 WHERE th2.transactionValue = '8' AND th2.transactionTypeID = '1' AND th2.employeeID = e.ID AND date(th2.transactionDate) = CURDATE()) status5
FROM transactionHistory th
INNER JOIN corporate.employees e ON e.id = th.employeeID
WHERE date(th.transactionDate) = CURDATE()
GROUP BY th.employeeID
ORDER BY employeeName;

problem is, by the time i finish writing those reports i will have to have about 40 different subselects to get all the counts i need and the query is already performing badly - is there a better way to get these numbers based on the info i've given here?

=========================================
Don't sweat the petty things and don't pet the sweaty things.

 

[r937]

select concat(e.last_name, ', ', e.first_name) as employeeName
     , th.employeeID
     , ( select sum(time_to_sec(h.time_out)
                   -time_to_sec(h.time_in)) / 3600
           from corporate.workHistory h
          where h.employee_ID = th.employeeID
            and h.project_ID = 418
            and h.work_date = now()  
                        [COLOR=red]/* THIS WON'T WORK */[/color]
       ) totalHours
    , sum(case when th.transactionValue = 1
                and th.transactionTypeID = 1
               then 1 else 0 end ) as status1
    , sum(case when th.transactionValue = 2
                and th.transactionTypeID = 1
               then 1 else 0 end ) as status2
    , sum(case when th.transactionValue = 3
                and th.transactionTypeID = 1
               then 1 else 0 end ) as status3
    , sum(case when th.transactionValue = 7
                and th.transactionTypeID = 1
               then 1 else 0 end ) as status[B]4[/B]
    , sum(case when th.transactionValue = 8
                and th.transactionTypeID = 1
               then 1 else 0 end ) as status[b]5[/b]
  from transactionHistory th
inner 
  join corporate.employees e 
    on e.id = th.employeeID
 where date(th.transactionDate) = curdate()
group 
    by e.last_name
     , e.first_name
     , th.employeeID
order 
    by e.last_name
     , e.first_name

r937.com | rudy.ca

 

[NorthStarDA]

hey rudy,

thanks much for showing me a better way, i don't understand the comment though, i wrote a sample query this way and it seems to work fine. should i be getting an error or is it that it won't return the proper data?

thanks again!

=========================================
Don't sweat the petty things and don't pet the sweaty things.

 

[r937]

seems to work fine

NOW() is the current datetime -- accurate to a fraction of a second

it would be terribly unusual for you to have a value of h.work_date that is exactly equal to the datetime of when you're running the query

r937.com | rudy.ca

 

[NorthStarDA]

ok thanks again rudy, it's working great so far!

=========================================
Don't sweat the petty things and don't pet the sweaty things.