create a query dragging the previous value

  • JaimePC

    SSC Eights!

    Points: 864

    Hi

    Does someone know how to implement a query that performs multiples dynamic operations based on an operator column and drag the value from the previous calculation to the last one?

    Example:

    Imagine the following table dbo.Products with this data:

    Products_Table

    Now we have a dbo.Operations table where there are some operation for productID, but the operation are cumulative based on the priorityOrder:

    Operations_Table

    Finally, the calculation for the price would be:

    Product 1:

    100+50=150

    150/2=75

    75*0.8=60

    And Product 2 would be:

    150-25=125

    In my case, I need to update the original table dbo.Products based on the table dbo.Operations.

    Any suggestion?

    Thanks

     

    • This topic was modified 3 months, 4 weeks ago by  JaimePC.
  • Phil Parkin

    SSC Guru

    Points: 243682

    I don't like this design much, but here's an idea that might help.

    DROP TABLE IF EXISTS #Ops;

    CREATE TABLE #Ops
    (
    ProductId INT
    ,PriorityOrder INT
    ,Operator CHAR(1)
    ,Value DECIMAL(18, 2)
    );

    INSERT #Ops
    (
    ProductId
    ,PriorityOrder
    ,Operator
    ,Value
    )
    VALUES
    (1, 1, '+', 50)
    ,(1, 2, '/', 2)
    ,(1, 3, '*', 0.8)
    ,(2, 1, '-', 25);

    SELECT *
    FROM #Ops o;

    DECLARE @ProductId INT = 1;
    DECLARE @Price INT = 100;
    DECLARE @x VARCHAR(8000) = CAST(@Price AS VARCHAR(20));

    SELECT @x = CONCAT('(', @x, ' ', o.Operator, ' ', CAST(o.Value AS VARCHAR(20)), ')')
    FROM #Ops o
    WHERE o.ProductId = @ProductId
    ORDER BY o.PriorityOrder;

    DECLARE @SQL VARCHAR(8000) = CONCAT('SELECT Result = ', @x);

    SELECT @SQL;

    EXEC (@SQL);

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • drew.allen

    SSC Guru

    Points: 76583

    Why are you trying to make a database program behave like a spreadsheet?  They are two very different tools and they both have their uses.  Use the right tool for the job.  SQL is not the right tool for this job.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • frederico_fonseca

    SSChampion

    Points: 14172

    following will work with sample data.

    constraints:

    PriorityOrder always starts at 1

    PriorityOrder increments by exactly 1 (e.g. there are no gaps)

    with operations
    as (
    select *
    from (values
    (cast(1 as int), cast(1 as int), cast ('+' as char(1)), cast(50. as decimal(20,10)))
    ,(1, 2, '/', 2)
    ,(1, 3, '*', .8)
    ,(2, 1, '-', 25)
    ) t(id, priorityorder, op, value)
    )
    , products
    as
    (
    select *
    from (values
    (cast(1 as int), cast(100. as decimal(20,10)))
    ,(2, 150)
    ) t(id, price)
    )
    , calcs
    as (select p.id
    , p.price as original_price
    , op.priorityorder
    , cast(case
    when op.op = '-' then p.price - op.value
    when op.op = '*' then p.price * op.value
    when op.op = '/' then p.price / op.value
    when op.op = '+' then p.price + op.value
    end as decimal(20, 10)) as calculated_price

    from products p
    inner join operations op
    on op.id = p.id
    and op.priorityorder = 1
    union all
    select c1.id
    , c1.original_price
    , op.priorityorder
    , cast(case
    when op.op = '-' then c1.calculated_price - op.value
    when op.op = '*' then c1.calculated_price * op.value
    when op.op = '/' then c1.calculated_price / op.value
    when op.op = '+' then c1.calculated_price + op.value
    end as decimal(20, 10)) as calculated_price
    from calcs c1
    inner join operations op
    on op.id = c1.id
    and op.priorityorder = c1.priorityorder + 1
    )
    select *
    from calcs c1
    inner join (select id, max(c2.priorityorder) as priorityorder
    from calcs c2
    group by id
    ) t
    on t.id = c1.id
    and t.priorityorder = c1.priorityorder
  • This was removed by the editor as SPAM

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

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