August 23, 2013 at 7:53 am
Hey,
I have a smallint column that is used to store total 'units' worked on a specific thing. Let's take 177 units as an example, which is a sum of a say 15 rows. 1 unit is 6 minutes, so 10 units is 1 hour.
I've casted the smallint column to real so it will divide by 10 to give me 17.70 hours. Well 70 minutes doesn't exist, so it needs to show as 18.10.
Never done this before!
Thanks
August 23, 2013 at 8:07 am
Shouldn't it be 17 hours and 42 minutes?
SELECT CAST(177/10 AS VARCHAR) + ':' + CAST(177 % 10 * 6 AS VARCHAR)
should do it.
August 23, 2013 at 8:40 am
Here you have the code where each column is a part of the calculation used to get the desired display in the final column. In your own code you only have to use the part I stated as the last column.
declare @unit int
set @unit = 151
select
@unit * 6.0 as 'total_minutes'
, (@unit * 6.0) / 60 as 'hours_incl_decimal'
, cast((@unit * 6.0) / 60 as int) as 'complete_hours'
, cast((@unit % 10.0) * 6 as int) as 'remaining_minutes'
, cast(cast((@unit * 6.0) / 60 as int) as varchar(2)) as 'hours_converted'
, right('00' + cast(cast((@unit % 10.0) * 6 as int) as varchar(2)), 2) as 'minutes_converted'
, cast(cast((@unit * 6.0) / 60 as int) as varchar(2)) + ':' + right('00' + cast(cast((@unit % 10.0) * 6 as int) as varchar(2)), 2) as 'time_notation'
August 23, 2013 at 8:59 am
Richard Warr (8/23/2013)
Shouldn't it be 17 hours and 42 minutes?
SELECT CAST(177/10 AS VARCHAR) + ':' + CAST(177 % 10 * 6 AS VARCHAR)should do it.
Not sure, it's been a LONG week! What does % do?
August 23, 2013 at 9:14 am
lanky,
% gives you the modulo, that is, it divides the first number by the second number and gives you the remainder. So in your example, 177 % 10 will give you 7, as 10 divides into 170 exactly and the 7 is left over. Multiplying this by 6 gives you units*minutes, the 42 minutes are the minutes left after the whole hours are taken into account.
August 23, 2013 at 9:20 am
Yes, 17.70 is 17.42.
For those that have 0 hours, ie 0.48 it errors out saying can't convert varchar to int.
EDIT: It's not just 0 hours that it does this with. It's the % part of it, as without it works;
sum(case when wh.WIPCODE = 'TIMCHG' then (cast(wh1.TOTALUNITS / 10 as varchar) + ':' + cast(wh1.TOTALUNITS % 10 * 6 as varchar)) else 0 end) [Time Hours],
August 23, 2013 at 9:34 am
Another option:
DECLARE @units int=177
SELECT DATEADD(MINUTE,@units*6,CONVERT(TIME,'00:00'))
August 23, 2013 at 9:41 am
lanky,
I think you're doing your formatting in the wrong place, you need to do the formatting to hh:mm after the SUM is done. See following example:
DECLARE @tester TABLE (WIPCODE varchar(10), TotalUnits int)
INSERT INTO @tester
VALUES('TIMCHG',5),('OTHER',4),('TIMCHG',8)
SELECT * FROM @tester
SELECT
CAST(SUM(CASE WHEN wh.WIPCODE = 'TIMCHG' THEN TotalUnits ELSE 0 END)/10 AS varchar) + ':' +
CAST(SUM(CASE WHEN wh.WIPCODE = 'TIMCHG' THEN TotalUnits ELSE 0 END) % 10 * 6 AS varchar)
AS [TIMCHG],
CAST(SUM(CASE WHEN wh.WIPCODE = 'OTHER' THEN TotalUnits ELSE 0 END)/10 AS varchar) + ':' +
CAST(SUM(CASE WHEN wh.WIPCODE = 'OTHER' THEN TotalUnits ELSE 0 END) % 10 * 6 AS varchar)
FROM @tester wh
August 27, 2013 at 2:55 am
HowardW (8/23/2013)
Another option:
DECLARE @units int=177SELECT DATEADD(MINUTE,@units*6,CONVERT(TIME,'00:00'))
This works, but for me it has hh:mm:ss.ms, i.e. 17:42:00.0000000
Can I get it to just be hh:mm?
August 27, 2013 at 2:58 am
lanky_doodle (8/27/2013)
HowardW (8/23/2013)
Another option:
DECLARE @units int=177SELECT DATEADD(MINUTE,@units*6,CONVERT(TIME,'00:00'))
This works, but for me it has hh:mm:ss.ms, i.e. 17:42:00.0000000
Can I get it to just be hh:mm?
Pretty simple:
DECLARE @units int=177
SELECT LEFT(DATEADD(MINUTE,@units*6,CONVERT(TIME,'00:00')),5)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply