SELECT DISTINCT A.toolID, toolName, ttypeID, roomID, hoursUsed, diffTime
FROM (
SELECT request.timeStart, request.timeEnd, HOUR( '18:00:00' ) - HOUR( timeEnd ) AS diffTime, toolID, dateUse
FROM requestdesc
LEFT JOIN request ON requestdesc.reqID = request.reqID
) AS A
LEFT JOIN tool ON A.toolID = tool.toolID
WHERE ttypeID = '01'
AND A.toolID !=0007
AND roomID = '0001'
AND A.dateUse = '2012-10-31'
AND A.timeEnd < '18:00:00'
AND (
(
'18:00:00' NOT
BETWEEN A.timeStart
AND A.timeEnd
)
AND (
'19:50:00' NOT
BETWEEN A.timeStart
AND A.timeEnd
)
)
ORDER BY diffTime DESC , hoursUsed ASC
$strSQL = " SELECT DISTINCT A.toolID , toolName , ttypeID , roomID , hoursUsed , diffTime FROM (
SELECT DISTINCT toolID, request.timeStart, request.timeEnd, HOUR('$startTime') - HOUR(timeEnd) AS diffTime, dateUse
FROM requestdesc
LEFT JOIN request ON requestdesc.reqID = request.reqID
WHERE ttypeID = '".$arrayttypeId[$i]."'
AND timeEnd < '".$startTime."'
ORDER BY diffTime ASC
) AS A
LEFT JOIN tool ON A.toolID = tool.toolID
WHERE roomID = '0001'
AND A.dateUse ='".$this->request->dateUse."' AND diffTime > 0
AND (('".$startTime."' NOT BETWEEN A.timeStart AND A.timeEnd )
AND ('".$endTime."' NOT BETWEEN A.timeStart AND A.timeEnd ))
GROUP BY toolID ORDER BY diffTime DESC , hoursUsed ASC ";
Load balance : Server 04
