CODEWITHSUNDEEP
sqlsql-server-2008-r2
Cornelius
Cornelius

Reputation: 1027

Multiply value, round down and CAST Select in SQL

I have a SQL function that gathers labour times from multiple records, and uses STUFF(CAST AS VARCHAR(20)) to concatenate them into a single column in a temp table.

My issue is the labour values in my table are in hours and rounded down to 7 decimals, I want to convert those values to minutes and round them down to 2 decimals before stuffing them into my temp table.

Update: Forgot to mention, I'm using SQL Server 2008 R2

Here is my code.

  @site nvarchar(20)
  ,@item nvarchar(30)
  ,@enviro nvarchar(30))
RETURNS nvarchar(MAX)
AS
BEGIN
    DECLARE @LbrHours AS nvarchar(MAX) = ''

    IF @enviro = 'Test'
    BEGIN
        IF @site = 'Arborg'
        BEGIN
            SET @LbrHours = STUFF((SELECT ',' + CAST(jrt.run_lbr_hrs AS VARCHAR(20))
                            FROM Arborg_Test_App.dbo.jobroute AS jbr
                                INNER JOIN Arborg_Test_App.dbo.job AS job
                                    ON job.job = jbr.job 
                                    AND job.suffix = jbr.suffix
                                INNER JOIN Arborg_Test_App.dbo.item AS itm
                                    ON itm.job = job.job
                                INNER JOIN Arborg_Test_App.dbo.jrt_sch AS jsh
                                    ON jbr.job = jsh.job 
                                    AND jbr.suffix = jsh.suffix 
                                    AND jbr.oper_num = jsh.oper_num
                                LEFT OUTER JOIN Arborg_Test_App.dbo.jrt_sch AS jrt
                                    ON jbr.job = jrt.job
                                    AND jbr.suffix = jrt.suffix
                                    AND jbr.oper_num = jrt.oper_num
                            WHERE  job.suffix = '0' and job.type = 'S' AND itm.item IS NOT NULL
                                AND itm.item = @item 
                                AND jbr.suffix = CASE -- Return Standard cost if Standard Operation exist, else return current cost
                                                    WHEN itm.cost_type = 'S'
                                                        THEN '1' -- '1' for standard operation
                                                    ELSE '0' -- '0' for current operations
                                                  END
                            ORDER BY itm.item, jbr.oper_num
                            FOR XML PATH('')), 1, 1, '')
            END 
        END

    RETURN @LbrHours
END

jrt.run_lbr_hrs is the column that contains the labour times in hours in our ERP's table. How can I multiply that by 60 and round it down to 2 decimals with in my existing STUFF(CASE AS NVARCHAR)?

Upvotes: 1

Views: 2838

Answers (2)

WarAnt
WarAnt

Reputation: 26

Try this Str (jrt.run_lbr_hrs * 60, 2)

Upvotes: 0

Serpiton
Serpiton

Reputation: 3684

CAST to decimal with 2 value after the point and then to varchar

CAST(CAST(jrt.run_lbr_hrs * 60 AS DECIMAL(10, 2)) AS VARCHAR(20))

Change the decimal dimension to what you need

Upvotes: 2

Related Questions