# 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:

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 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 oWHERE o.ProductId = @ProductIdORDER 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

• 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 operationsas (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)), productsas (select *from (values  (cast(1 as int), cast(100. as decimal(20,10))),(2, 150)) t(id, price)), calcsas (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 c1inner join (select id, max(c2.priorityorder) as priorityorder            from calcs c2            group by id            ) ton t.id = c1.idand t.priorityorder = c1.priorityorder`
• This was removed by the editor as SPAM

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