Reputation: 9756
I have time stored in minutes in integer datatype which has to be displayed in HH:MM format. For example: if total minutes is 80 then it should convert to 01:20.
select SUM(OTTime) from dbo.TableOT where ....
I have tried some queries but didn't get the exact output.
Updated Query:
SELECT SUM(t.OTTime),d.combovalue
FROM dbo.employee e
join dbo.OT t
on e.id = t.employeeid
JOIN dbo.combovalues d
ON e.department = d.id
GROUP By d.combovalue
Upvotes: 0
Views: 435
Reputation: 1271231
First, I recommend using decimal hours. Much simpler:
SELECT d.combovalue, SUM(t.OTTime) / 60.0
FROM dbo.employee e JOIN
dbo.OT t
ON e.id = t.employeeid JOIN
dbo.combovalues d
ON e.department = d.id
GROUP By d.combovalue;
But that is not your question. If you knew that there are never more than 24 hours, you could use the TIME
data type:
CONVERT(VARCHAR(5), DATEADD(minute, SUM(t.OTTime), 0), 8)
If that is too dangerous, then you need to convert to a string:
CONCAT(FORMAT(SUM(t.OTTime) / 60, '00'), ':', FORMAT(SUM(t.OTTime) % 60, '00'))
Upvotes: 0
Reputation: 1340
Try this Single Query:
DECLARE @Duration int
SET @Duration= 4000
SELECT CAST( CAST((@Duration) AS int) / 60 AS varchar) + ':' + right('0' + CAST(CAST((@Duration) AS int) % 60 AS varchar(2)),2)
For Updated Query:
SELECT d.combovalue,CAST(CAST((SUM(t.OTTime)) AS int) / 60 AS varchar) + ':'
+ right('0' + CAST(CAST((SUM(t.OTTime)) AS int) % 60 AS varchar(2)),2)
FROM dbo.employee e join dbo.OT t on e.id = t.employeeid
JOIN dbo.combovalues d ON e.department = d.id
GROUP By d.combovalue
Upvotes: 1
Reputation: 1340
Instead of @minutes variable you can use SUM(OTTime) and a from clause in this below Query
DECLARE @minutes INT=80
SELECT CASE WHEN @minutes >= 60 THEN (SELECT CAST((@minutes / 60) AS VARCHAR(2)) + ':' + CASE WHEN (@minutes % 60) > 0 THEN CAST((@minutes % 60) AS VARCHAR(2))
ELSE '' END)
ELSE CAST((@minutes % 60) AS VARCHAR(2)) END
For Eg:
SELECT CASE WHEN SUM(OTTime) >= 60 THEN (SELECT CAST((SUM(OTTime) / 60) AS VARCHAR(2)) + ':' + CASE WHEN (SUM(OTTime) % 60) > 0 THEN CAST((SUM(OTTime) % 60) AS VARCHAR(2))
ELSE '' END)
ELSE CAST((SUM(OTTime) % 60) AS VARCHAR(2)) END
from dbo.TableOT where ....
Create a SCALAR Function and pass the
select dbo.Minutes_to_HrsMts (SUM(OTTime)) from dbo.TableOT where ....
Function:
CREATE Function dbo.Minutes_to_HrsMts (@minutes INT)
RETURNS nvarchar(30)
AS
BEGIN
declare @hours nvarchar(20)
SET @hours =
CASE WHEN @minutes >= 60 THEN
(SELECT CAST((@minutes / 60) AS VARCHAR(2)) + ':' +
CASE WHEN (@minutes % 60) > 0 THEN
CAST((@minutes % 60) AS VARCHAR(2))
ELSE
''
END)
ELSE
CAST((@minutes % 60) AS VARCHAR(2))
END
return @hours
END
Upvotes: 0