We have an hour table in our application which stores the working hours for each associate. It has hour values as follow
0.30 ,
0.30 ,
1.10
0.30 indicates 30 minutes and 1.10 indicates 1 hour 10 minutes. So when I calculate the sum of hours I got 1.7, but I need to get 1.3 (I need to convert 1.10 to 0.70).
How to achieve this?
you can use some math calculation to achieve what you want ..
SELECT workHours - FLOOR(workHours) + FLOOR(workHours)*0.60
sample value:
SELECT 1.10 - FLOOR(1.10) + FLOOR(1.10)* 0.60
result: 0.70
A simple solution would be to store the time in minutes making arithmetic simple. Then in the presentation layer convert it to the desired format.
To convert 1.1 into 0.7
(int(1.1)*60+1.1%1*10)/100=.7
Try it like this:
DECLARE @tbl TABLE(TimeValue VARCHAR(100));
INSERT INTO @tbl VALUES
('0.30'),('0.30'),('1.10');
WITH Splitted AS
(
SELECT CAST('<x>' + REPLACE(TimeValue,'.','</x><x>') + '</x>' AS XML) AS vals
FROM @tbl
)
,TheSplittedSums AS
(
SELECT SUM(vals.value('/x[1]','int')) AS valHour
,SUM(vals.value('/x[2]','int')) AS valMinute
FROM Splitted
)
SELECT valHour*60 + valMinute
FROM TheSplittedSums
Delivers 130 (minutes)