|
Edited on Fri May-30-08 07:40 PM by The Straight Story
Problem is, it does not take into account a canceled trip (cxltime), only ones where we arrived at destination. This is the original they had, I am re-writing it. Thinking of using 3 views (one for ats, on for cancel, and one for a combo) since they want it all on one report. Of course I could write it in vb or something and use more logic, just wanted to try and keep it server side....(and yes, this is the real query) SELECT dbo_Trips.tdate, dbo_Trips.dstatus, dbo_Trips.priority, dbo_Priorities.descr, dbo_Trips.puzone, dbo_Zones.descr, dbo_Trips.calltype, DateDiff("s",DateSerial(Year( ),Month(),Day())+TimeSerial(Hour(),Minute(),Second()),DateSerial(Year(),Month(),Day())+TimeSerial(Hour(),Minute(),Second())) AS GT539Sec, dbo_Call_Types.descr, dbo_Trips.atsdate, dbo_Trips.atstime, dbo_Trips.calldate, dbo_Trips.calltime, dbo_Trips.putime, dbo_Trips.RunNumber, dbo_Trips.puaddr, dbo_Trips.comments, dbo_Late_Reasons.descr FROM (((dbo_Trips INNER JOIN dbo_Zones ON dbo_Trips.puzone = dbo_Zones.code) INNER JOIN dbo_Priorities ON dbo_Trips.priority = dbo_Priorities.code) INNER JOIN dbo_Call_Types ON dbo_Trips.calltype = dbo_Call_Types.code) LEFT JOIN dbo_Late_Reasons ON dbo_Trips.LateReason = dbo_Late_Reasons.code GROUP BY dbo_Trips.tdate, dbo_Trips.dstatus, dbo_Trips.priority, dbo_Priorities.descr, dbo_Trips.puzone, dbo_Zones.descr, dbo_Trips.calltype, DateDiff("s",DateSerial(Year(),Month(),Day())+TimeSerial(Hour(),Minute(),Second()),DateSerial(Year(),Month(),Day())+TimeSerial(Hour(),Minute(),Second())), dbo_Call_Types.descr, dbo_Trips.atsdate, dbo_Trips.atstime, dbo_Trips.calldate, dbo_Trips.calltime, dbo_Trips.putime, dbo_Trips.RunNumber, dbo_Trips.puaddr, dbo_Trips.comments, dbo_Late_Reasons.descr HAVING (((dbo_Trips.tdate)>= And (dbo_Trips.tdate)<=) AND ((dbo_Trips.dstatus)=-7 Or (dbo_Trips.dstatus)=-6 Or (dbo_Trips.dstatus)=-3 Or (dbo_Trips.dstatus)=-2 Or (dbo_Trips.dstatus)=-1 Or (dbo_Trips.dstatus)=1 Or (dbo_Trips.dstatus)=2 Or (dbo_Trips.dstatus)=3 Or (dbo_Trips.dstatus)=4 Or (dbo_Trips.dstatus)=5 Or (dbo_Trips.dstatus)=6 Or (dbo_Trips.dstatus)=7) AND ((dbo_Trips.priority)=17) AND ((dbo_Trips.puzone)>1) AND ((dbo_Zones.descr) Like "*, M*") AND ((dbo_Trips.calltype)=1 Or (dbo_Trips.calltype)=2 Or (dbo_Trips.calltype)=6 Or (dbo_Trips.calltype)=7 Or (dbo_Trips.calltype)=8 Or (dbo_Trips.calltype)=9 Or (dbo_Trips.calltype)=25 Or (dbo_Trips.calltype)=26 Or (dbo_Trips.calltype)=29 Or (dbo_Trips.calltype)=30) AND ((DateDiff("s",DateSerial(Year(),Month(),Day())+TimeSerial(Hour(),Minute(),Second()),DateSerial(Year(),Month(),Day())+TimeSerial(Hour(),Minute(),Second())))>539) AND ((dbo_Trips.RunNumber)>0)) ORDER BY dbo_Zones.descr;
|