February 2, 2016 at 8:31 am
My problem is that in the source system, time is entered in terms of .3 is 30 mins and .15 as 15 mins... but amazingly enough .12 is actually 12 mins - please don't ask - incredulous I know.
Psuedo Select statement is:
Managing to read from source no problem... as follows:
[Code="other"]
select name, SUM(CASE WHEN CallType IN ('Y', 'C', 'E') THEN 0.0
WHEN NoHours = 0.3 THEN 0.5
WHEN NoHours = 0.15 THEN 0.25
ELSE NoHours END
) AS [Total Hours]
FROM tables, GROUP BY etc....
[/code]
But my sum statement is now returning 235.5 hours and I need to convert that back to 235.3
How can I best do this as part of the same select statement ?
Help much appreciated - many thanks in advance.
February 2, 2016 at 8:45 am
WITH AllHours AS (
select name, SUM(CASE WHEN CallType IN ('Y', 'C', 'E') THEN 0.0
WHEN NoHours = 0.3 THEN 0.5
WHEN NoHours = 0.15 THEN 0.25
ELSE NoHours END
) AS TotalHours
FROM tables, GROUP BY etc....
)
SELECT
name
,FLOOR(TotalHours) + (TotalHours - FLOOR(TotalHours))*60/100
FROM AllHours
John
February 2, 2016 at 8:52 am
Thanks John - CTE - perfect thanks
February 2, 2016 at 11:01 am
jez.omahony (2/2/2016)
My problem is that in the source system, time is entered in terms of .3 is 30 mins and .15 as 15 mins... but amazingly enough .12 is actually 12 mins - please don't ask - incredulous I know.Psuedo Select statement is:
Managing to read from source no problem... as follows:
[Code="other"]
select name, SUM(CASE WHEN CallType IN ('Y', 'C', 'E') THEN 0.0
WHEN NoHours = 0.3 THEN 0.5
WHEN NoHours = 0.15 THEN 0.25
ELSE NoHours END
) AS [Total Hours]
FROM tables, GROUP BY etc....
[/code]
But my sum statement is now returning 235.5 hours and I need to convert that back to 235.3
How can I best do this as part of the same select statement ?
Help much appreciated - many thanks in advance.
Why are you only converting 0.3 and 0.15? If 0.25 represents 25 minutes, then after your conversion both 0.25 and 0.15 result in 0.25. If 0.12 represents 12 minutes then it should be converted to 0.20 not left at 0.12.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply