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

• 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