September 25th, 2007, 07:42 AM
|
|
Contributing User
|
|
Join Date: Aug 2007
Posts: 119
Time spent in forums: 8 h 4 m 34 sec
Reputation Power: 0
|
|
|
Other Language - How do I Calculate Average Leadtime in sqlserver...
Hi, The below query calculates hours and minutes seperately and gives the output as :
Quote: | 2007-09-18 09:47:00(Req_Date), 2007-09-18 10:50:00(CLOSED_DATE),1(TICKET),1(HOURS), 3(MINUTES),63(TOTALMINS). |
As we can see am getting 1 hours and 3 minutes as seperate columns.. But i need it as 1.3 hours (minutes i dont want seperately. Can anyone please send me the query for this.
Code:
SELECT dbo.iCalls_Calls.REQ_DATE, dbo.iCalls_Calls.CLOSED_DATE,
COUNT(*) AS TICKET, FLOOR(DATEDIFF(MI, dbo.iCalls_Calls.REQ_DATE,
dbo.iCalls_Calls.CLOSED_DATE) / 60) AS HOURS,
DATEDIFF(MI, dbo.iCalls_Calls.REQ_DATE, dbo.iCalls_Calls.CLOSED_DATE)
- FLOOR(DATEDIFF(MI, dbo.iCalls_Calls.REQ_DATE, dbo.iCalls_Calls.CLOSED_DATE)
/ 60) * 60 AS MINUTES, DATEDIFF(MI, dbo.iCalls_Calls.REQ_DATE,
dbo.iCalls_Calls.CLOSED_DATE) AS TOTALMINS
FROM dbo.iCalls_Calls INNER JOIN
dbo.iCalls_Users ON dbo.iCalls_Calls.REQUESTOR = dbo.iCalls_Users.USER_ID
WHERE (dbo.iCalls_Calls.STATUS_ID = 3)
GROUP BY dbo.iCalls_Calls.REQ_DATE, dbo.iCalls_Calls.CLOSED_DATE
Thanks a lot...
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
