troubleshooting Question

Sql query help

Avatar of Sean
SeanFlag for United States of America asked on
DatabasesMySQL ServerMicrosoft SQL Server 2005
10 Comments1 Solution533 ViewsLast Modified:
Hi,
I have this sql query, that results in the attached output. What I need is another column that would give me the difference between column " Created Time" and " Fixed Time" in following format;

HH:MM

Thanks
SELECT  sinfo.WORKSTATIONNAME "Kiosk",
        wo.WORKORDERID "Request ID" ,scd.Name "Issue" 
        ,convert(varchar(16), dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wo.CREATEDTIME/1000),'1970-01-01 00:00:00'), 121) "Created Time", 
        convert(varchar(16), dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wo.COMPLETEDTIME/1000),'1970-01-01 00:00:00'), 121) "CompletedTime", 
        cast((((wo.timespentonreq)/1000)/3600) as varchar(20)) +':'+REPLACE(REPLACE(LEN(cast(((((wo.timespentonreq )/1000)/60)) % 60 as varchar(20))),
        '1','0'+cast(((((wo.timespentonreq )/1000)/60)) % 60 as varchar(20))),'2',
        cast(((((wo.timespentonreq)/1000)/60)) % 60 as varchar(20)))+':'+ REPLACE(REPLACE(LEN(cast((((wo.timespentonreq)%1000)%60) as varchar(20))),
        '1','0'+cast((((wo.timespentonreq)%1000)%60) as varchar(20))),'2',cast((((wo.timespentonreq)%1000)%60) as varchar(20)))"Down Time",
        convert(varchar(16), dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wof.UDF_DATE1/1000),'1970-01-01 00:00:00'), 121) "Fixed Time", 
        wof.UDF_CHAR3 "Location" 
        
FROM WorkOrder_Threaded wot   

INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID   LEFT JOIN SystemInfo sinfo ON wo.WORKSTATIONID=sinfo.WORKSTATIONID   LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID   LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID   LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID   LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID   WHERE  (((DATEADD(s,wo.CREATEDTIME/1000,'01-01-1970 05:30')> convert(varchar,'2009-12-23 12:00',21) AND ((wo.CREATEDTIME != 0)   AND (wo.CREATEDTIME IS NOT NULL))) AND (DATEADD(s,wo.CREATEDTIME/1000,'01-01-1970 05:30') <= convert(varchar,'2009-12-24 11:59',21)  AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))   AND (cd.CATEGORYNAME = N'Cajeros Only' COLLATE SQL_Latin1_General_CP437_CS_AS)) AND wot.THD_WOID=wot.WORKORDERID
sqlout3.jpg
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros