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
LVL 1
SeanAsked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
Sorry, it should be like this:

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

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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

0
 
SeanAuthor Commented:
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.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Aneesh RetnakaranDatabase AdministratorCommented:
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

0
 
ralmadaCommented:
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

0
 
ralmadaCommented:
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

0
 
SeanAuthor Commented:
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
0
 
SeanAuthor Commented:
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
0
 
SeanAuthor Commented:
ralmada,
 It worked. You are two for two wise one. Thank you.
0
 
SeanAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.