April 26, 2012 at 6:11 am
Hello,
Would somone know how to calculate the difference between 2 queries each of which give the result in the format - HH:MM:SS. The queries are below:
----------------------------------------------------------
Standard Hours - replace(str((SUM(CAST(SUBSTRING(WeekHours,1,2) AS INT)) *3600 + SUM(CAST(SUBSTRING(WeekHours,4,2) AS INT))*60)/18000,len(ltrim((SUM(CAST(SUBSTRING(WeekHours,1,2) AS INT)) *3600 + SUM(CAST(SUBSTRING(WeekHours,4,2) AS INT))*60)/18000))+
abs(sign((SUM(CAST(SUBSTRING(WeekHours,1,2) AS INT)) *3600 + SUM(CAST(SUBSTRING(WeekHours,4,2) AS INT))*60)/359999)-1)) + ':' +
str(((SUM(CAST(SUBSTRING(WeekHours,1,2) AS INT)) *3600 + SUM(CAST(SUBSTRING(WeekHours,4,2) AS INT))*60)/60)%60,2) + ':' +
str((SUM(CAST(SUBSTRING(WeekHours,1,2) AS INT)) *3600 + SUM(CAST(SUBSTRING(WeekHours,4,2) AS INT))*60) %60,2),' 0','00')
Actual Hours - CONVERT(varchar(6), (sum((datepart(hour,Timenetin) * 3600)) + sum((datepart(hour,cat2_hours) * 3600)) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,cat2_hours) * 60) + sum(datepart(second,Timenetin)) + sum(datepart(second,cat2_hours))) / 3600)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,Timenetin) * 3600)) + sum((datepart(hour,cat2_hours) * 3600)) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,cat2_hours) * 60)+
sum(datepart(second,Timenetin)) + sum(datepart(second,cat2_hours))) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,Timenetin) * 3600)) + sum((datepart(hour,cat2_hours) * 3600)) +
sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,cat2_hours) * 60) + sum(datepart(second,Timenetin)) + sum(datepart(second,cat2_hours))) % 60), 2)
----------------------------------------------------------
I need to calculate the result in Hours, Minutes and Seconds separately. For example if the values are:
Standard Hours: 133:50:15
Actual Hours : 98:16:57
Then the result should be shown as:
Actual Hours - Standard Hours, which is - 35:33:18.
Is that possible ? Thanks in advance for your help.
Regards.
Paul
April 26, 2012 at 6:42 am
Hi Paul
Can you post a little sample data please? I'm sure this can be done more efficiently.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 26, 2012 at 6:44 am
Here's a very very not pretty solution.
DECLARE @StandardHours VARCHAR(20) = '133:50:15';
DECLARE @ActualHours VARCHAR(20) = '98:16:57';
SELECT
CAST(diffSeconds/3600 AS VARCHAR(6)) + ':'+ RIGHT('00'+CAST(diffSeconds%(3600)/60 AS VARCHAR(2)),2)+':'+RIGHT('00'+CAST(diffSeconds%60 AS VARCHAR(2)),2)
FROM (SELECT
DATEDIFF(second,
DATEADD(hh,actualHours.hrs,DATEADD(mi,actualHours.mins,DATEADD(ss,actualHours.secs,CAST('1900' AS DATETIME)))),
DATEADD(hh,standardHours.hrs,DATEADD(mi,standardHours.mins,DATEADD(ss,standardHours.secs,CAST('1900' AS DATETIME))))
)
FROM (SELECT
SUM(CASE WHEN pos=1 THEN timePart ELSE 0 END) AS hrs,
SUM(CASE WHEN pos=2 THEN timePart ELSE 0 END) AS mins,
SUM(CASE WHEN pos=3 THEN timePart ELSE 0 END) AS secs
FROM (SELECT x.y.value('.','int') AS timePart,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS pos
FROM (SELECT CAST('<time><part>'+REPLACE(@StandardHours,':','</part><part>')+'</part></time>' AS XML))a(X)
CROSS APPLY X.nodes('/time/part/text()') x(y)
) a )standardHours
CROSS JOIN (SELECT
SUM(CASE WHEN pos=1 THEN timePart ELSE 0 END) AS hrs,
SUM(CASE WHEN pos=2 THEN timePart ELSE 0 END) AS mins,
SUM(CASE WHEN pos=3 THEN timePart ELSE 0 END) AS secs
FROM (SELECT x.y.value('.','int') AS timePart,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS pos
FROM (SELECT CAST('<time><part>'+REPLACE(@ActualHours,':','</part><part>')+'</part></time>' AS XML))a(X)
CROSS APPLY X.nodes('/time/part/text()') x(y)
) a )actualHours
) a(diffSeconds);
The issue is that you're using formatted data in the database. Instead, you should store this as TIME or DATETIME then convert it to the correct format at the presentation layer.
April 26, 2012 at 6:51 am
Cadavre (4/26/2012)
...The issue is that you're using formatted data in the database. Instead, you should store this as TIME or DATETIME then convert it to the correct format at the presentation layer.
Tell this to Microsoft - ever played with run_duration in msdb.dbo.sysjobhistory? 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 26, 2012 at 6:55 am
ChrisM@Work (4/26/2012)
Cadavre (4/26/2012)
...The issue is that you're using formatted data in the database. Instead, you should store this as TIME or DATETIME then convert it to the correct format at the presentation layer.
Tell this to Microsoft - ever played with run_duration in msdb.dbo.sysjobhistory? 😀
As soon as I have to do any work for Microsoft using a schema that they designed and uses a string data type for datetime/time/date data I will :w00t:
Until then, I'll continue grumbling about incorrect datatype usage wherever I see it. It may not make a difference, it may not be possible for the person using the incorrect datatype to change it, but it'll make me feel better 😛
April 26, 2012 at 7:04 am
Cadavre (4/26/2012)
ChrisM@Work (4/26/2012)
Cadavre (4/26/2012)
...The issue is that you're using formatted data in the database. Instead, you should store this as TIME or DATETIME then convert it to the correct format at the presentation layer.
Tell this to Microsoft - ever played with run_duration in msdb.dbo.sysjobhistory? 😀
As soon as I have to do any work for Microsoft using a schema that they designed and uses a string data type for datetime/time/date data I will :w00t:
Until then, I'll continue grumbling about incorrect datatype usage wherever I see it. It may not make a difference, it may not be possible for the person using the incorrect datatype to change it, but it'll make me feel better 😛
LOL quite right too!
I'd use iTVF's for this, A to B and B to A, and SUM across seconds to a bigint - what do you reckon, Cadavre?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 26, 2012 at 7:05 am
ChrisM@Work (4/26/2012)
LOL quite right too!I'd use iTVF's for this, A to B and B to A, and SUM across seconds to a bigint - what do you reckon, Cadavre?
Probably the best bet
April 26, 2012 at 9:53 am
Cadavre (4/26/2012)
Here's a very very not pretty solution.
DECLARE @StandardHours VARCHAR(20) = '133:50:15';DECLARE @ActualHours VARCHAR(20) = '98:16:57';
SELECT
CAST(diffSeconds/3600 AS VARCHAR(6)) + ':'+ RIGHT('00'+CAST(diffSeconds%(3600)/60 AS VARCHAR(2)),2)+':'+RIGHT('00'+CAST(diffSeconds%60 AS VARCHAR(2)),2)
FROM (SELECT
DATEDIFF(second,
DATEADD(hh,actualHours.hrs,DATEADD(mi,actualHours.mins,DATEADD(ss,actualHours.secs,CAST('1900' AS DATETIME)))),
DATEADD(hh,standardHours.hrs,DATEADD(mi,standardHours.mins,DATEADD(ss,standardHours.secs,CAST('1900' AS DATETIME))))
)
FROM (SELECT
SUM(CASE WHEN pos=1 THEN timePart ELSE 0 END) AS hrs,
SUM(CASE WHEN pos=2 THEN timePart ELSE 0 END) AS mins,
SUM(CASE WHEN pos=3 THEN timePart ELSE 0 END) AS secs
FROM (SELECT x.y.value('.','int') AS timePart,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS pos
FROM (SELECT CAST('<time><part>'+REPLACE(@StandardHours,':','</part><part>')+'</part></time>' AS XML))a(X)
CROSS APPLY X.nodes('/time/part/text()') x(y)
) a )standardHours
CROSS JOIN (SELECT
SUM(CASE WHEN pos=1 THEN timePart ELSE 0 END) AS hrs,
SUM(CASE WHEN pos=2 THEN timePart ELSE 0 END) AS mins,
SUM(CASE WHEN pos=3 THEN timePart ELSE 0 END) AS secs
FROM (SELECT x.y.value('.','int') AS timePart,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS pos
FROM (SELECT CAST('<time><part>'+REPLACE(@ActualHours,':','</part><part>')+'</part></time>' AS XML))a(X)
CROSS APPLY X.nodes('/time/part/text()') x(y)
) a )actualHours
) a(diffSeconds);
The issue is that you're using formatted data in the database. Instead, you should store this as TIME or DATETIME then convert it to the correct format at the presentation layer.
Thanks for your reply, however I am unable to understand your code. I have reached upto the point where I am able to calculate the minutes difference. However, the hour difference is not right.
Could you pls help me in sorting this out. This is the formula I have created for the minutes difference and it works correctly(Timenetin and cat2_hours are Time fields but Weekhours is varchar)):
------------------------------------
convert(varchar(10),((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(hour,cat2_hours) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,cat2_hours) * 60) + sum(datepart(second,Timenetin) * 1) + sum(datepart(second,cat2_hours) * 1)) - (SUM(CAST(SUBSTRING(WeekHours,1,2) AS INT) *3600) + SUM(CAST(SUBSTRING(WeekHours,4,2) AS INT))*60)) % 3600/60)
------------------------------------
When I alter the above formula to calculate the hours difference, I get incorrect values. This is what I have done:
------------------------------------
convert(varchar(10),((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(hour,cat2_hours) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,cat2_hours) * 60) + sum(datepart(second,Timenetin) * 1) + sum(datepart(second,cat2_hours) * 1)) - (SUM(CAST(SUBSTRING(WeekHours,1,2) AS INT) *3600) + SUM(CAST(SUBSTRING(WeekHours,4,2) AS INT))*60)) /3600)
------------------------------------
Could anyone pls tell what wrong I may be doing.
Thanks !
April 26, 2012 at 10:13 am
ChrisM@Work (4/26/2012)
Hi PaulCan you post a little sample data please? I'm sure this can be done more efficiently.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 26, 2012 at 10:29 am
pwalter83 (4/26/2012)
Thanks for your reply, however I am unable to understand your code. I have reached upto the point where I am able to calculate the minutes difference. However, the hour difference is not right.
My code is converting the "Standard Hours" and the "Actual Hours" into DATETIME, then doing a DATEDIFF for the number of seconds (lowest datepart that you store) between them. Then I'm transforming the number of seconds back into the number of hours (CAST(diffSeconds/3600 AS VARCHAR(6)), minutes (RIGHT('00'+CAST(diffSeconds%(3600)/60 AS VARCHAR(2)),2)), and seconds (RIGHT('00'+CAST(diffSeconds%60 AS VARCHAR(2)),2)).
Does that help? If not, please post back with the information Chris requested.
April 27, 2012 at 2:54 am
Cadavre (4/26/2012)
pwalter83 (4/26/2012)
Thanks for your reply, however I am unable to understand your code. I have reached upto the point where I am able to calculate the minutes difference. However, the hour difference is not right.My code is converting the "Standard Hours" and the "Actual Hours" into DATETIME, then doing a DATEDIFF for the number of seconds (lowest datepart that you store) between them. Then I'm transforming the number of seconds back into the number of hours (CAST(diffSeconds/3600 AS VARCHAR(6)), minutes (RIGHT('00'+CAST(diffSeconds%(3600)/60 AS VARCHAR(2)),2)), and seconds (RIGHT('00'+CAST(diffSeconds%60 AS VARCHAR(2)),2)).
Does that help? If not, please post back with the information Chris requested.
Please find attached a sample output from SQL. The last 2 columns(Hours Diff and Min Diff) are the ones I am trying to calculate by subtracting Standard hours (SH) from Actual Hours (AH)-
AH - SH.
The Min Diff seems to be okay but the Hours Diff value is very high. This is how I am calculating the Hours and Min difference:
-----------------------------------------------
convert(varchar(10),((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(hour,cat2_hours) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,cat2_hours) * 60) + sum(datepart(second,Timenetin) * 1) + sum(datepart(second,cat2_hours) * 1)) - SUM(CAST(SUBSTRING(WeekHours,1,2) AS INT) *3600)) /3600) As Hrs_Diff,
convert(varchar(10),((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(hour,cat2_hours) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,cat2_hours) * 60) + sum(datepart(second,Timenetin) * 1) + sum(datepart(second,cat2_hours) * 1)) - (SUM(CAST(SUBSTRING(WeekHours,1,2) AS INT) *3600) + SUM(CAST(SUBSTRING(WeekHours,4,2) AS INT))*60)) % 3600/60) As Min_Diff
------------------------------------------------
The Timenetin and cat2 fields have a time datatype, whereas WeekHours is a varchar field, could that be the problem ?
April 27, 2012 at 3:43 am
Oh come on, Paul - none of us is going to retype from a bitmap! Be nice!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 27, 2012 at 3:55 am
ChrisM@Work (4/27/2012)
Oh come on, Paul - none of us is going to retype from a bitmap! Be nice!
This is a sample data from a stored procedure not a table. The codes for the calculation of Standard hours and Actual hours are already mentioned in the earlier post.
I really dont know what further info I need to give.
April 27, 2012 at 6:08 am
pwalter83 (4/27/2012)
ChrisM@Work (4/27/2012)
Oh come on, Paul - none of us is going to retype from a bitmap! Be nice!This is a sample data from a stored procedure not a table. The codes for the calculation of Standard hours and Actual hours are already mentioned in the earlier post.
I really dont know what further info I need to give.
-- Function code:
DECLARE @TimeChar VARCHAR(10), @SecondsINT BIGINT
SET @TimeChar = '168:03:09'
--iTVF_TimeVCToSecondsINT ('HHHH:MM:SS')
SELECT @SecondsINT = ([Hours]*3600) + ([Minutes]*60) + [Seconds]
FROM (
SELECT
[Hours] = CAST(LEFT(@TimeChar,CHARINDEX(':',@TimeChar,0)-1) AS BIGINT),
[Minutes] = CAST(LEFT(RIGHT(@TimeChar,5),2) AS BIGINT),
[Seconds] = CAST(RIGHT(@TimeChar,2) AS BIGINT)
) d
SELECT TimeChar = @TimeChar, SecondsINT = @SecondsINT
--iTVF_SecondsINTToTimeVC (1)
SELECT @TimeChar = [Hours] + ':' + [Minutes] + ':' + [Seconds]
FROM (
SELECT
[Hours] = RIGHT('00'+CAST([Hours] AS VARCHAR(4)),CASE WHEN LEN([Hours]) < 3 THEN 2 ELSE LEN([Hours]) END),
[Minutes] = RIGHT('00'+CAST(Remainder/60 AS VARCHAR(2)),2),
[Seconds] = RIGHT('00'+CAST(Remainder%60 AS VARCHAR(2)),2)
FROM (SELECT [Hours] = @SecondsINT/3600, Remainder = @SecondsINT%3600) d
) x
SELECT TimeChar = @TimeChar, SecondsINT = @SecondsINT
-- Usage:
;WITH Sampledata AS (
SELECT Name = 'ONE', InTime = '07:31:22', OutTime = '15:21:67' UNION ALL
SELECT 'ONE', '07:29:12', '16:56:59' UNION ALL
SELECT 'TWO', '08:54:06', '17:29:48' UNION ALL
SELECT 'THREE', '09:01:58', '18:01:59')
,ConvertedToSeconds AS (
SELECT
s.Name,
s.InTime,
s.OutTime,
InTimeSeconds = iTVF_TimeVCToSecondsINT1.SecondsINT,
OutTimeSeconds = iTVF_TimeVCToSecondsINT2.SecondsINT
FROM Sampledata s
CROSS APPLY (
SELECT SecondsINT = ([Hours]*3600) + ([Minutes]*60) + [Seconds]
FROM (
SELECT
[Hours] = CAST(LEFT(s.InTime,CHARINDEX(':',s.InTime,0)-1) AS BIGINT),
[Minutes] = CAST(LEFT(RIGHT(s.InTime,5),2) AS BIGINT),
[Seconds] = CAST(RIGHT(s.InTime,2) AS BIGINT)
) d
) iTVF_TimeVCToSecondsINT1 (SecondsINT)
CROSS APPLY (
SELECT SecondsINT = ([Hours]*3600) + ([Minutes]*60) + [Seconds]
FROM (
SELECT
[Hours] = CAST(LEFT(s.OutTime,CHARINDEX(':',s.OutTime,0)-1) AS BIGINT),
[Minutes] = CAST(LEFT(RIGHT(s.OutTime,5),2) AS BIGINT),
[Seconds] = CAST(RIGHT(s.OutTime,2) AS BIGINT)
) d
) iTVF_TimeVCToSecondsINT2 (SecondsINT)
)
SELECT
Name,
SumWorkedTime = iTVF_SecondsINTToTimeVC.TimeChar
FROM (
SELECT Name, WorkedSeconds = SUM(OutTimeSeconds - InTimeSeconds)
FROM ConvertedToSeconds c
GROUP BY Name
) agg
CROSS APPLY (
SELECT TimeChar = [Hours] + ':' + [Minutes] + ':' + [Seconds]
FROM (
SELECT
[Hours] = RIGHT('00'+CAST([Hours] AS VARCHAR(4)),CASE WHEN LEN([Hours]) < 3 THEN 2 ELSE LEN([Hours]) END),
[Minutes] = RIGHT('00'+CAST(Remainder/60 AS VARCHAR(2)),2),
[Seconds] = RIGHT('00'+CAST(Remainder%60 AS VARCHAR(2)),2)
FROM (SELECT [Hours] = agg.WorkedSeconds/3600, Remainder = agg.WorkedSeconds%3600) d
) x
) iTVF_SecondsINTToTimeVC(TimeChar)
Result:
NameWorkedSecondsTimeChar
ONE6231217:18:32
THREE3240109:00:01
TWO3094208:35:42
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 27, 2012 at 6:53 am
ChrisM@Work (4/27/2012)
pwalter83 (4/27/2012)
ChrisM@Work (4/27/2012)
Oh come on, Paul - none of us is going to retype from a bitmap! Be nice!This is a sample data from a stored procedure not a table. The codes for the calculation of Standard hours and Actual hours are already mentioned in the earlier post.
I really dont know what further info I need to give.
-- Function code:DECLARE @TimeChar VARCHAR(10), @SecondsINT BIGINT
SET @TimeChar = '168:03:09'
--iTVF_TimeVCToSecondsINT ('HHHH:MM:SS')
SELECT @SecondsINT = ([Hours]*3600) + ([Minutes]*60) + [Seconds]
FROM (
SELECT
[Hours] = CAST(LEFT(@TimeChar,CHARINDEX(':',@TimeChar,0)-1) AS BIGINT),
[Minutes] = CAST(LEFT(RIGHT(@TimeChar,5),2) AS BIGINT),
[Seconds] = CAST(RIGHT(@TimeChar,2) AS BIGINT)
) d
SELECT TimeChar = @TimeChar, SecondsINT = @SecondsINT
--iTVF_SecondsINTToTimeVC (1)
SELECT @TimeChar = [Hours] + ':' + [Minutes] + ':' + [Seconds]
FROM (
SELECT
[Hours] = RIGHT('00'+CAST([Hours] AS VARCHAR(4)),CASE WHEN LEN([Hours]) < 3 THEN 2 ELSE LEN([Hours]) END),
[Minutes] = RIGHT('00'+CAST(Remainder/60 AS VARCHAR(2)),2),
[Seconds] = RIGHT('00'+CAST(Remainder%60 AS VARCHAR(2)),2)
FROM (SELECT [Hours] = @SecondsINT/3600, Remainder = @SecondsINT%3600) d
) x
SELECT TimeChar = @TimeChar, SecondsINT = @SecondsINT
-- Usage:
;WITH Sampledata AS (
SELECT Name = 'ONE', InTime = '07:31:22', OutTime = '15:21:67' UNION ALL
SELECT 'ONE', '07:29:12', '16:56:59' UNION ALL
SELECT 'TWO', '08:54:06', '17:29:48' UNION ALL
SELECT 'THREE', '09:01:58', '18:01:59')
,ConvertedToSeconds AS (
SELECT
s.Name,
s.InTime,
s.OutTime,
InTimeSeconds = iTVF_TimeVCToSecondsINT1.SecondsINT,
OutTimeSeconds = iTVF_TimeVCToSecondsINT2.SecondsINT
FROM Sampledata s
CROSS APPLY (
SELECT SecondsINT = ([Hours]*3600) + ([Minutes]*60) + [Seconds]
FROM (
SELECT
[Hours] = CAST(LEFT(s.InTime,CHARINDEX(':',s.InTime,0)-1) AS BIGINT),
[Minutes] = CAST(LEFT(RIGHT(s.InTime,5),2) AS BIGINT),
[Seconds] = CAST(RIGHT(s.InTime,2) AS BIGINT)
) d
) iTVF_TimeVCToSecondsINT1 (SecondsINT)
CROSS APPLY (
SELECT SecondsINT = ([Hours]*3600) + ([Minutes]*60) + [Seconds]
FROM (
SELECT
[Hours] = CAST(LEFT(s.OutTime,CHARINDEX(':',s.OutTime,0)-1) AS BIGINT),
[Minutes] = CAST(LEFT(RIGHT(s.OutTime,5),2) AS BIGINT),
[Seconds] = CAST(RIGHT(s.OutTime,2) AS BIGINT)
) d
) iTVF_TimeVCToSecondsINT2 (SecondsINT)
)
SELECT
Name,
SumWorkedTime = iTVF_SecondsINTToTimeVC.TimeChar
FROM (
SELECT Name, WorkedSeconds = SUM(OutTimeSeconds - InTimeSeconds)
FROM ConvertedToSeconds c
GROUP BY Name
) agg
CROSS APPLY (
SELECT TimeChar = [Hours] + ':' + [Minutes] + ':' + [Seconds]
FROM (
SELECT
[Hours] = RIGHT('00'+CAST([Hours] AS VARCHAR(4)),CASE WHEN LEN([Hours]) < 3 THEN 2 ELSE LEN([Hours]) END),
[Minutes] = RIGHT('00'+CAST(Remainder/60 AS VARCHAR(2)),2),
[Seconds] = RIGHT('00'+CAST(Remainder%60 AS VARCHAR(2)),2)
FROM (SELECT [Hours] = agg.WorkedSeconds/3600, Remainder = agg.WorkedSeconds%3600) d
) x
) iTVF_SecondsINTToTimeVC(TimeChar)
Result:
NameWorkedSecondsTimeChar
ONE6231217:18:32
THREE3240109:00:01
TWO3094208:35:42
Thanks for your help. However, was able to achieve this with code:
convert(int,((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(hour,cat2_hours) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,cat2_hours) * 60) + sum(datepart(second,Timenetin) * 1) + sum(datepart(second,cat2_hours) * 1))))/3600 -
((sum(datepart(hour,Monhours) * 3600) + sum(datepart(hour,Tuehours) * 3600) + sum(datepart(hour,Wedhours) * 3600) + sum(datepart(hour,Thuhours) * 3600) + sum(datepart(hour,Frihours) * 3600)
+ sum(datepart(minute,Monhours) * 60) + sum(datepart(minute,Tuehours) * 60) + sum(datepart(minute,Wedhours) * 60) + sum(datepart(minute,Thuhours) * 60) + sum(datepart(minute,Frihours) * 60) +
sum(datepart(second,Monhours) * 1) + sum(datepart(second,Tuehours) * 1) + sum(datepart(second,Wedhours) * 1) +
sum(datepart(second,Thuhours) * 1) + sum(datepart(second,Frihours) * 1)))/18000 As Hrs_Diff,
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply