create a query dragging the previous value

Question

Post reply

create a query dragging the previous value

JaimePC

SSC Eights!

Points: 864
More actions
June 18, 2019 at 6:51 pm

#3654912
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:
Now we have a dbo.Operations table where there are some operation for productID, but the operation are cumulative based on the priorityOrder:
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 4 years, 10 months ago by JaimePC.

June 18, 2019 at 7:44 pm

This was removed by the editor as SPAM

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

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

Phil Parkin SSC Guru Points: 246712 More actions · Answer 1

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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

drew.allen SSC Guru Points: 76945 More actions · Answer 2

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 SSCoach Points: 16025 More actions · Answer 3

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