[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

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
0
Sean
Asked:
Sean
  • 5
  • 3
  • 2
1 Solution
 
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
 
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now