Remove decimal

1

Hello!
What would be the best way to show results without decimals.

in this example the firts row for example I want to it to display 14% instead of 0,140625

image
image895×165 40.1 KB 

;WITH

oplog_data AS (
    SELECT


        shortl62 AS shortl62,
        partno AS product_id,
        CONVERT(FLOAT, COUNT(*)) AS annulled_order_lines
    FROM
       L16T3 WITH (NOLOCK)
    WHERE
        datreg >= dateadd(day, 0, CONVERT(char(10), GETDATE(),126))
        AND l16lcode = 45
    GROUP BY
        shortl62,
        partno   
)

SELECT
 
    oplog_data.product_id,
    oplog_data.annulled_order_lines,

    SUM(oplog_data.annulled_order_lines) OVER (PARTITION BY 1) AS total_annulled_order_lines,
  
     oplog_data.annulled_order_lines /  SUM(oplog_data.annulled_order_lines) OVER (PARTITION BY 1) as procent, 

    COUNT(oplog_data.product_id) OVER (PARTITION BY 1) AS total_annulled_products,
    
    ROW_NUMBER() OVER (ORDER BY oplog_data.annulled_order_lines DESC) AS rank_by_order_lines,
    TRIM(L62T1.partdsc1)  AS product_name,
    TRIM(L62T1.pmha) AS pick_mha,
    SUBSTRING(L62T1.pmha, 1, 3) AS mha_calssification
FROM oplog_data
LEFT JOIN
    L62T1 WITH (NOLOCK)
    ON L62T1.shortl62 = oplog_data.shortl62
where L62T1.partdsc1 not like 'Sampling%'
2

The best way is to do it: don't do it.

You can better do it at the frontsize, on your report or app. Developers can choose if they want 14%, or 14,1%.

If you really want to do it:

4 Ways to Convert a Number to a Percentage in SQL Server (T-SQL) (database.guide)

3

The user will use the result shown in the attached picture, So I really need it to display without the decimals.

Have checked the link but cant really figure out how I should use it in my query.

4

Now I got this result, but there are way to many decimals...only need 2 decimals

image
image927×192 39.5 KB 

;WITH

oplog_data AS (
    SELECT


        shortl62 AS shortl62,
        partno AS product_id,
    
        CONVERT(FLOAT, COUNT(*)) AS annulled_order_lines
    FROM
       L16T3 WITH (NOLOCK)
    WHERE
        datreg >= dateadd(day, 0, CONVERT(char(10), GETDATE(),126))
        AND l16lcode = 45
    GROUP BY
        shortl62,
        partno   
)

SELECT


    oplog_data.product_id,
    oplog_data.annulled_order_lines,

    SUM(oplog_data.annulled_order_lines) OVER (PARTITION BY 1) AS total_annulled_order_lines,

   
    oplog_data.annulled_order_lines *100 /  SUM(oplog_data.annulled_order_lines) OVER (PARTITION BY 1) as procent,




    COUNT(oplog_data.product_id) OVER (PARTITION BY 1) AS total_annulled_products,
    
    ROW_NUMBER() OVER (ORDER BY oplog_data.annulled_order_lines DESC) AS rank_by_order_lines,
    TRIM(L62T1.partdsc1)  AS product_name,
    TRIM(L62T1.pmha) AS pick_mha,
    SUBSTRING(L62T1.pmha, 1, 3) AS mha_calssification
FROM oplog_data
LEFT JOIN
    L62T1 WITH (NOLOCK)
    ON L62T1.shortl62 = oplog_data.shortl62
where L62T1.partdsc1 not like 'Sampling%'
5

Try this:

CAST(oplog_data.annulled_order_lines *100.00 /  SUM(oplog_data.annulled_order_lines) OVER (PARTITION BY 1) AS DECIMAL(5,2)) as procent,
6

Why do you have a LEFT JOIN and a WHERE condition? What happens when L62T1.partdsc1 is NULL?

7

Thanks!