Post reply

show percentage between 2 fileds

  • February 19, 2022 at 11:03 pm

    Go to Answer

    #3985897

    I want to show a percentage in a new column that reflects a gain\loss % between col1 and col2(The decimal fields in tables) returned in my query. The query produces a query result for each one of the decimal fields. We are doing some new calculations in the earns table and trying to see how those look compared to the earnsprod table seeing how close using a percentage. The tables listed are identical so I just listed one schema.

    DECLARE @ColName varchar(100)
    DECLARE @Table1 varchar(100) = 'Earns'
    DECLARE @Table2 varchar(100) = 'Earns_Prod'


    IF (OBJECT_ID('tempdb..#col') IS NOT NULL) DROP TABLE #col
    SELECT  IDENTITY(INT, 1, 1) RowNum , c.name
    INTO    #col
    FROM    SYS.Objects o 
            JOIN SYS.columns c on o.object_id = c.object_id
    WHERE   o.name = @Table1 AND NOT c.Name IN ('PlantCd','PartNbr','CostKey','RequestedBy','FreezeTime','FreezeYN','ApprovedBy','opn','level_code','machine')

    DECLARE @Counter INT = (SELECT MAX(RowNum) FROM #col)

        WHILE @Counter > 0

            BEGIN
                SET @ColName = (SELECT name FROM #Col WHERE RowNum= @Counter)
                    EXEC ('SELECT  t1.partnbr
                            ,t1.'+@ColName+' AS '+@Table1+@ColName+'
                            ,t2.'+@ColName+' AS '+@Table2+@ColName+'
                    FROM    '+@Table1+' t1
                            LEFT JOIN '+@Table2+' t2 ON t1.partnbr = t2.partnbr and t1.opn = t2.opn
                    WHERE   t1.'+@ColName+' <> t2.'+@ColName)
                SET @Counter = @Counter - 1 
    END

     

    CREATE TABLE [dbo].[Earns](
    [CostKey] [varchar](9) NOT NULL,
    [PlantCd] [varchar](9) NOT NULL,
    [PartNbr] [varchar](20) NOT NULL,
    [Opn] [varchar](9) NOT NULL,
    [Level_code] [int] NOT NULL,
    [Machine] [varchar](10) NULL,
    [Matl] [decimal](16, 8) NOT NULL,
    [Labor] [decimal](16, 8) NOT NULL,
    [VarOH] [decimal](16, 8) NOT NULL,
    [FixOH] [decimal](16, 8) NOT NULL,
    [Total] [decimal](16, 8) NOT NULL,
    [CuRecovery] [decimal](16, 8) NOT NULL,
    [CuLbs] [decimal](16, 8) NOT NULL,
    [RequestedBy] [varchar](9) NULL,
    [FreezeTime] [varchar](22) NULL,
    [FreezeYN] [varchar](1) NULL,
    [ApprovedBy] [varchar](9) NULL,
     CONSTRAINT [PK_Earns] PRIMARY KEY NONCLUSTERED 
    (
    [CostKey] ASC,
    [PlantCd] ASC,
    [PartNbr] ASC,
    [Opn] ASC,
    [Level_code] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

  • February 20, 2022 at 9:28 am

    #3986009

    This is not a question. What is the problem?

  • February 20, 2022 at 11:52 am

    #3986027

    How do I create the 3rd field which would be a percentage(difference) from col1 and col2?

     

    thx

  • February 20, 2022 at 12:01 pm

    #3986030 

    DROP TABLE IF EXISTS #T1;

    CREATE TABLE #T1
    (
        Col1 DECIMAL(19, 6) NOT NULL
       ,Col2 DECIMAL(19, 6) NOT NULL
       ,PCDiff AS CAST((Col1 - Col2) * 100 / Col1 AS DECIMAL(7, 4))
    );

    INSERT #T1
    (
        Col1
       ,Col2
    )
    VALUES
    (5, 7);

    SELECT *
    FROM #T1 t;

  • February 20, 2022 at 1:53 pm

    #3986055

    Looks good, easy there an easy check to eliminate:

     

    Divide by zero error encountered, and order by high to low %?

     

    Thanks again.

    • This reply was modified 3 years, 6 months ago by Bruin.

  • February 20, 2022 at 6:10 pm

    #3986100

    You'd have to check for zero in the divisor first. There's no DIVIDE() function in T-SQL.

    IF(divisorColumn = 0, NULL, NumeratorColumn/DivisorColumn)

  • February 20, 2022 at 6:38 pm

    #3986102

    pietlinden wrote:

    You'd have to check for zero in the divisor first. There's no DIVIDE() function in T-SQL. IF(divisorColumn = 0, NULL, NumeratorColumn/DivisorColumn)

    There's a more concise way of achieving that:

    NULLIF(Divisor,0)

  • February 20, 2022 at 9:12 pm

    #3986128

    Is there any easy way just to skip those records rather than return Null?

    THx.

  • February 21, 2022 at 1:03 am

    #3986160

    Please consider the possibility of SQL Injection, especially on @Table2.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 21, 2022 at 1:20 am

    #3986162

    Can you elaborate?

     

    Thx.

  • February 21, 2022 at 11:05 am

    #3986330

    Bruin wrote:

    Is there any easy way just to skip those records rather than return Null?

    THx.

    The column is defined for every row of data in the table, therefore the idea of 'skipping' makes no sense.

  • February 21, 2022 at 2:30 pm

    #3986433

    I ended up with something like this. Do you see any issues I would run into?

     

     

    Thx for comments...

    IF (OBJECT_ID('tempdb..#col') IS NOT NULL) DROP TABLE #col
    SELECT  IDENTITY(INT, 1, 1) RowNum , c.name
    INTO    #col
    FROM    SYS.Objects o 
            JOIN SYS.columns c on o.object_id = c.object_id
    WHERE   o.name = @Table1 AND NOT c.Name IN ('PlantCd','PartNbr','CostKey','RequestedBy','FreezeTime','FreezeYN','ApprovedBy','opn','level_code','machine')

    DECLARE @Counter INT = (SELECT MAX(RowNum) FROM #col)

        WHILE @Counter > 0

            BEGIN
                SET @ColName = (SELECT name FROM #Col WHERE RowNum= @Counter)
                    EXEC ('SELECT  t1.partnbr
                            ,t1.'+@ColName+' AS '+@Table1+@ColName+'
                            ,t2.'+@ColName+' AS '+@Table2+@ColName+'
                     ,case when (t1.'+@ColName+' = 0 or t2.'+@ColName+' = 0) Then
    Null
    Else 
        CAST((t1.'+@ColName+' - t2.'+@ColName+') * 100 / t1.'+@ColName+' AS DECIMAL(16, 8))
    end
                        FROM    '+@Table1+' t1
                            LEFT JOIN '+@Table2+' t2 ON t1.partnbr = t2.partnbr and t1.opn = t2.opn
                    WHERE   t1.'+@ColName+' <> t2.'+@ColName)
                SET @Counter = @Counter - 1 
    END

  • February 21, 2022 at 3:54 pm

    Answer

    #3986465

    I ended up with something like this. Do you see any issues I would run into?

    As I have completely failed to grasp what it is you are going to be using this for, not really. But it does seem like a weird solution.

  • February 21, 2022 at 8:07 pm

    #3986567

    Bruin wrote:

    Can you elaborate?

    Thx.

    Sure.  Do a search for SQL Injection and try some of the methods for the way you're using @Table2 and @ColName.  @Table1 may not have the same problem because you first use the contents of @Table1 to qualify things as an actual table name.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply