CODEWITHSUNDEEP
sql-server
Jackal
Jackal

Reputation: 3521

Subtract the amount > 100%

I have this stored procedure where it updates the EtiquetasInventariadas column with the number of validated items and the Precisão Column with a % of how many items were validated compared to the total of items available on EtiquetasPorInventariar

ALTER PROCEDURE [dbo].[spx_UPDATE_EtiquetasInventariadas]
@EtiquetasInventariadas int, 
@InventarioID int,
@LocalizacaoID int 
AS
BEGIN



UPDATE xLocalizacao
    SET EtiquetasInventariadas = EtiquetasInventariadas + @EtiquetasInventariadas, IsValid = 1
    WHERE (LocalizacaoID = @LocalizacaoID)

    UPDATE xLocalizacao
    SET Precisao = CAST(EtiquetasInventariadas AS DECIMAL) / CAST(EtiquetasPorInventariar AS DECIMAL)
    WHERE LocalizacaoID = @LocalizacaoID 

    IF NOT EXISTS(SELECT 1 FROM xLocalizacao WHERE InventarioID = @InventarioID AND isValid = 0)
    BEGIN 
        SELECT 1 
    END
    ELSE
    BEGIN
        SELECT -1
    END
END

However, i was asked that if the number of EtiquetasInventariadas are higher than the amount in EtiquetasPorInventariar it should subtract the amount beyond 100% and I can't seem to understand the logic how to do this..

EDIT

By subtract if value is higher than 100 I mean

If the outcome is 120%, it should show 80%

Here's table definition of xLocalizacao

CREATE TABLE [dbo].[xLocalizacao](
    [LocalizacaoID] [int] IDENTITY(1,1) NOT NULL,
    [Localizacao] [nvarchar](20) NOT NULL,
    [EtiquetasPorInventariar] [int] NOT NULL,
    [EtiquetasInventariadas] [int] NOT NULL,
    [IsValid] [bit] NOT NULL,
    [InventarioID] [int] NOT NULL,
    [Precisao] [decimal](3, 2) NULL
)

Upvotes: 0

Views: 127

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

It may not be immediately obvious, but if you want 100% to be the maximum attainable and amounts over are as bad as amount under, then some creative subtractions and ABS help you out.

If you current value is in a variable called @Precisao then if it's already a number between 0 - 100 representing the percentage:

100 - ABS(100 - @Precisao)

If it's a number between 0.0 and 1.0, then:

1.0 - ABS(1.0 - @Precisao)

Will give you the result you seek.

You can of course replace that variable with your current expression, suitably parenthesized.

From fooplot.com

enter image description here

Upvotes: 3

Daniel Brughera
Daniel Brughera

Reputation: 1651

A simple case should work

SET Precisao = CASE WHEN EtiquetasInventariadas >= EtiquetasPorInventariar THEN 1.00 ELSE CAST(EtiquetasInventariadas AS DECIMAL) / CAST(EtiquetasPorInventariar AS DECIMAL) END

Upvotes: 1

Related Questions