November 10, 2010 at 1:09 pm
Hi
I have data values like '00058', this means 58 minutes, and trying to sum of all the row values and convert into Minutes and hours.
1st 3 characters are hours and remaining 2 are minutes
i have writen query and giving wrong values, can some one help on this.
Thx
CREATE TABLE #TIMECALC
(EMPCHAR(11),
TRANSTME CHAR(11))
INSERT INTO #TIMECALC
SELECT 'H111', '00058' UNION
SELECT 'H222', '00120' UNION
SELECT 'H222', '00015' UNION
SELECT 'H333', '01100' UNION
SELECT 'H333', '00900' UNION
SELECT * FROM #TIMECALC
SELECT EMP,
SUM(CONVERT(decimal, SUBSTRING(TRANSTME, 1, 3))) as FirstPart,
SUM(CONVERT(decimal, SUBSTRING(TRANSTME, 4, 2))) as SecondPart,
SUM(CONVERT(decimal, SUBSTRING(TRANSTME, 1, 3))) + SUM(CONVERT(decimal, SUBSTRING(TRANSTME, 4, 2))) as FirstSecond,
(SUM(CONVERT(decimal, SUBSTRING(TRANSTME, 1, 3)))) + (SUM(CONVERT(decimal, SUBSTRING(TRANSTME, 4, 2)))) / 60.00 AS TransHrs,
SUM(CONVERT(decimal, SUBSTRING(TRANSTME, 4, 2))) % 60.00 AS TransMins,
(ROUND(((SUM(CONVERT(decimal, SUBSTRING(TRANSTME, 1, 3))) + SUM(CONVERT(decimal, SUBSTRING(TRANSTME, 4, 2))) / 60.00)
* 60.00 + SUM(CONVERT(decimal, SUBSTRING(TRANSTME, 4, 2))) % 60.00) / 60.00, 2)) AS TotalHours
FROM #TIMECALC
GROUP BY EMP
November 10, 2010 at 1:22 pm
The recursive UNION in your insert crashed SSMS for me. Might want to edit that out.
Does this do what you need:
SELECT EMP,
SUM(CAST(SUBSTRING(TRANSTME, 1, 3) AS INT)) + SUM(CAST(SUBSTRING(TRANSTME, 4, 2) AS INT))/60 AS HRS,
SUM(CAST(SUBSTRING(TRANSTME, 4, 2) AS INT))%60 AS MINS
FROM #TIMECALC
GROUP BY EMP;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 10, 2010 at 1:25 pm
I broke this into ctes to make the logic more obvious. I also added a few entries into H111 to allow for multiple minutes over 60 to force a 1 hour rollup. Let me know if you have questions:
/*
CREATE TABLE #TIMECALC
(EMP CHAR(11),
TRANSTME CHAR(11))
INSERT INTO #TIMECALC
SELECT 'H111', '00058' UNION
SELECT 'H111', '00028' UNION
SELECT 'H111', '00141' UNION
SELECT 'H222', '00120' UNION
SELECT 'H222', '00015' UNION
SELECT 'H333', '01100' UNION
SELECT 'H333', '00900' UNION
SELECT * FROM #TIMECALC
*/
;WITH cte AS
(
SELECT
EMP,
CONVERT( INT, SUBSTRING( Transtme, 1, 3)) AS RowHrs,
CONVERT( INT, SUBSTRING( Transtme, 4, 2)) AS RowMins
FROM
#TimeCalc
)
,cte2 AS
(
SELECT
EMP,
SUM( RowHrs) AS TotHrs,
SUM( RowMins) AS TotMins
FROM
cte
GROUP BY EMP
)
SELECT
EMP,
-- uncomment the two fields below to see hours and minutes before rolling 60 mins into 1 hour.
--TotHrs,
--TotMins,
TotHrs + TotMins/60 AS TotHrs,
TotMins%60 AS TotMins
from
cte2
EDIT: Gus was faster on the draw! His code is as accurate, and should run faster. I split mine out more for example purposes.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 10, 2010 at 1:42 pm
Both solutions works perfectly, thank you very much for the help.
November 10, 2010 at 1:45 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply