Link to home
Start Free TrialLog in
Avatar of Sean
SeanFlag for United States of America

asked on

Sql query help

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

Open in new window

sqlout3.jpg
Avatar of Aneesh
Aneesh
Flag of Canada image
Hello slimlcd101,



Regards,

aneeshattingal
SELECT a.*, CONVERT(char(4), "Created Time"-"Fixed Time" , 018 ) as diff
FROM  (

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   
 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

) A

Open in new window

Avatar of Sean
Sean
Flag of United States of America image

ASKER

aneeshattingal:

Thank you for your response, this is what I get when I run your query:

Msg 8117, Level 16, State 1, Line 1
Operand data type varchar is invalid for subtract operator.
Avatar of Aneesh
Aneesh
Flag of Canada image
SELECT a.*, CONVERT(char(4), cast("Created Time" as datetime) - cast("Fixed Time" as DATEtime ) , 108 ) as diff
FROM  (
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  
 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
) A

Avatar of ralmada
ralmada
Flag of Canada image
No need for the subquery. I would just do it like this:
 
....
convert(varchar(5), dateadd(s, datediff(s,GETUTCDATE() ,getdate()) + (wof.UDF_DATE1 - wo.CREATEDTIME)/1000 , 0), 108) as "Created Time" ,  
....

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) as "Created Time" , 
        convert(varchar(16), dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.COMPLETEDTIME/1000),'1970-01-01 00:00:00'), 121) as "Completed Time", 
		convert(varchar(5), dateadd(ms, (wo.timespentonreq%1000)%60, dateadd(s, wo.timespentonreq/1000, 0)), 108) as "Down Time", 
        convert(varchar(16), dateadd(s, datediff(s, getutcdate(), getdate()), dateadd(ms, ((wof.UDF_DATE1%1000)%60), dateadd(s, wof.UDF_DATE1/1000, '1970-01-01'))), 121) "Fixed Time", 
		convert(varchar(5), dateadd(s,	datediff(s,GETUTCDATE() ,getdate()) + (wof.UDF_DATE1 - wo.CREATEDTIME)/1000 , 0), 108) as "Created 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

Open in new window

Avatar of ralmada
ralmada
Flag of Canada image
Sorry forgot to change the column name
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) as "Created Time" ,  
        convert(varchar(16), dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.COMPLETEDTIME/1000),'1970-01-01 00:00:00'), 121) as "Completed Time",  
                convert(varchar(5), dateadd(ms, (wo.timespentonreq%1000)%60, dateadd(s, wo.timespentonreq/1000, 0)), 108) as "Down Time",  
        convert(varchar(16), dateadd(s, datediff(s, getutcdate(), getdate()), dateadd(ms, ((wof.UDF_DATE1%1000)%60), dateadd(s, wof.UDF_DATE1/1000, '1970-01-01'))), 121) "Fixed Time",  
                convert(varchar(5), dateadd(s,  datediff(s,GETUTCDATE() ,getdate()) + (wof.UDF_DATE1 - wo.CREATEDTIME)/1000 , 0), 108) as [Created - Fixed] ,  
         
                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

Open in new window

Avatar of Sean
Sean
Flag of United States of America image

ASKER

aneeshattingal:
This is the result of running your query. As you can see the times difference is not correct.

Thanks again for your help
sqlout5.jpg
Avatar of Sean
Sean
Flag of United States of America image

ASKER

Hi ralmada:
this what i get when I run your query. As you can see the time difference is not correct.
Thanks again for your help
sqlout6.jpg
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean
Sean
Flag of United States of America image

ASKER

ralmada,
 It worked. You are two for two wise one. Thank you.
Avatar of Sean
Sean
Flag of United States of America image

ASKER

Thank you.