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

asked on

Sql query time format modification

Hi,
I need to modify this query's out put with regards to the " time spent" filed.
Here is what I have:

SELECT sinfo.WORKSTATIONNAME "Workstation",wo.WORKORDERID "Request ID",scd.NAME "Sub Category",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') "Created Time",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.COMPLETEDTIME/1000),'1970-01-01 00:00:00')  "Completed Time",
cast((((wo.timespentonreq)/1000)/3600) as varchar(20)) +':'+cast(((((wo.timespentonreq)/1000)/60)) % 60 as varchar(20))+':'+ cast((((wo.timespentonreq)%1000)%60) as varchar(20)) "Time Spent",
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-09-27 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-09-28 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

Which produces these results the attached  .csv  file.
How do I get it so that the "Time Spent " filed is in this format?

2:54:00
4:20:00
3:19:00

Thank you.
test.csv
Avatar of leakim971
leakim971
Flag of Guadeloupe image
Hello slimlcd101,

Try this :


SELECT sinfo.WORKSTATIONNAME "Workstation",wo.WORKORDERID "Request ID",scd.NAME "Sub Category",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') "Created Time",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.COMPLETEDTIME/1000),'1970-01-01 00:00:00')  "Completed Time",
cast((((wo.timespentonreq)/1000)/3600) as varchar(20)) +':'+cast(((((wo.timespentonreq)/1000)/60)) % 60 as varchar(20))+':'+ 
cast((((wo.timespentonreq)/1000)/3600) as varchar(20)) +':'+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))) "Time Spent",
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-09-27 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-09-28 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

leakim971,
Please see the results in test2.csv.
thank you for your help
S
test2.csv
Avatar of leakim971
leakim971
Flag of Guadeloupe image
Seems I copy/cut too much...

Corrected :


SELECT sinfo.WORKSTATIONNAME "Workstation",wo.WORKORDERID "Request ID",scd.NAME "Sub Category",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') "Created Time",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.COMPLETEDTIME/1000),'1970-01-01 00:00:00')  "Completed Time",
cast((((wo.timespentonreq)/1000)/3600) as varchar(20)) +':'+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))) "Time Spent",
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-09-27 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-09-28 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

Leakim971,
this might be a better looking output.
2009-09-27 03:02:15.000      2009-09-27 12:05:59.000      4:5:4:5:00      South California
2009-09-27 10:06:17.000      2009-09-27 10:19:31.000      0:13:0:13:00      South California
2009-09-27 11:06:37.000      2009-09-27 13:03:45.000      1:57:1:57:00      North California
2009-09-27 11:07:38.000      2009-09-27 12:54:40.000      1:47:1:47:00      South California
2009-09-27 13:56:13.000      2009-09-27 15:22:31.000      1:26:1:26:00      South California
2009-09-27 15:02:49.000      2009-09-27 15:37:37.000      0:34:0:34:00      South California
2009-09-27 02:58:12.000      2009-09-27 12:05:27.000      4:5:4:5:00      South California
2009-09-27 11:08:38.000      2009-09-27 13:02:10.000      1:53:1:53:00      South California
2009-09-27 17:12:37.000      2009-09-27 18:55:29.000      1:42:1:42:00      South California
2009-09-27 21:23:54.000      2009-09-28 10:55:58.000      2:55:2:55:00      South California
2009-09-27 00:20:15.000      2009-09-27 11:56:03.000      3:56:3:56:00      South California
2009-09-27 04:49:35.000      2009-09-27 13:03:19.000      5:3:5:3:00      South California
2009-09-27 07:42:59.000      2009-09-27 14:06:25.000      6:6:6:6:00      Chicago, IL
2009-09-27 14:33:26.000      2009-09-27 14:55:04.000      0:21:0:21:00      South California
2009-09-27 15:21:03.000      2009-09-27 15:48:58.000      0:27:0:27:00      South California
2009-09-27 19:33:04.000      2009-09-28 10:41:49.000      3:8:3:8:00      North California

thanks
S
Avatar of Sean
Sean
Flag of United States of America image

ASKER

Leakin971,
This is from your second post, not quit there,  
2009-09-27 03:02:15.000      2009-09-27 12:05:59.000      4:5:00      South California
2009-09-27 13:56:13.000      2009-09-27 15:22:31.000      1:26:00      South California
2009-09-27 15:02:49.000      2009-09-27 15:37:37.000      0:34:00      South California
2009-09-27 02:58:12.000      2009-09-27 12:05:27.000      4:5:00      South California
2009-09-27 21:23:54.000      2009-09-28 10:55:58.000      2:55:00      South California
2009-09-27 00:20:15.000      2009-09-27 11:56:03.000      3:56:00      South California
2009-09-27 04:49:35.000      2009-09-27 13:03:19.000      5:3:00      South California
2009-09-27 07:42:59.000      2009-09-27 14:06:25.000      6:6:00      Chicago, IL
2009-09-27 19:33:04.000      2009-09-28 10:41:49.000      3:8:00      North California

Thanks
S
Avatar of leakim971
leakim971
Flag of Guadeloupe image
Don't think you want to have two digits for hour so :


SELECT sinfo.WORKSTATIONNAME "Workstation",wo.WORKORDERID "Request ID",scd.NAME "Sub Category",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') "Created Time",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.COMPLETEDTIME/1000),'1970-01-01 00:00:00')  "Completed Time",
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))) "Time Spent",
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-09-27 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-09-28 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

ASKER CERTIFIED SOLUTION
Avatar of leakim971
leakim971
Flag of Guadeloupe 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

Leakim971,
 you got it. Thank you. Here is the output.
2009-09-27 03:02:15.000      2009-09-27 12:05:59.000      4:05:00      South California
2009-09-27 13:56:13.000      2009-09-27 15:22:31.000      1:26:00      South California
2009-09-27 15:02:49.000      2009-09-27 15:37:37.000      0:34:00      South California
2009-09-27 02:58:12.000      2009-09-27 12:05:27.000      4:05:00      South California
2009-09-27 11:08:38.000      2009-09-27 13:02:10.000      1:53:00      South California
2009-09-27 17:12:37.000      2009-09-27 18:55:29.000      1:42:00      South California
2009-09-27 21:23:54.000      2009-09-28 10:55:58.000      2:55:00      South California
2009-09-27 00:20:15.000      2009-09-27 11:56:03.000      3:56:00      South California
2009-09-27 04:49:35.000      2009-09-27 13:03:19.000      5:03:00      South California
2009-09-27 07:42:59.000      2009-09-27 14:06:25.000      6:06:00      Chicago, IL
2009-09-27 14:33:26.000      2009-09-27 14:55:04.000      0:21:00      South California
2009-09-27 15:21:03.000      2009-09-27 15:48:58.000      0:27:00      South California
2009-09-27 19:33:04.000      2009-09-28 10:41:49.000      3:08:00      North California


Much appreciated.
Sean
Avatar of Sean
Sean
Flag of United States of America image

ASKER

Leakim971,

The sql query runs just fine,thank you.
when I import the data to an Excel sheel the format is as you see below, it looks fine but when I try to find the total "time spent" my formula only works if there is not 0 next to the "hour" value, ie..2:54:00
,3:19:00,3:06:00. is there anything that can be done on sql query side or Excel side?
Thanks again.


10/9/2009 23:05      10/10/2009 11:37      03:37:00      North California
10/9/2009 0:32      10/9/2009 12:20      04:20:00      North California
10/9/2009 1:23      10/9/2009 11:15      03:15:00      South California
10/9/2009 7:32      10/9/2009 14:13      06:13:00      North California
10/9/2009 3:02      10/9/2009 11:09      03:09:00      South California
10/9/2009 7:38      12/31/1969 17:00      00:00:00      Chicago, IL
10/9/2009 10:46      10/9/2009 11:01      00:14:00      South California
10/9/2009 14:57      10/9/2009 18:11      03:13:00      South California
10/9/2009 18:00      10/10/2009 12:14      06:14:00      North California
10/9/2009 21:20      10/10/2009 10:12      02:12:00      South California
Avatar of leakim971
leakim971
Flag of Guadeloupe image
Post ID 25544732 I don't see any zero.
Assuming Excel is formatting the hour when you past results, you need to work on it :
Right click Time Spent column, right click cell format, custom and set h:mm:ss


Avatar of Sean
Sean
Flag of United States of America image

ASKER

I see. Very well thank you for your help . will be posting another question shortly with regards to the same query.