June 18, 2019 at 6:51 pm
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
June 18, 2019 at 7:33 pm
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.
June 18, 2019 at 7:41 pm
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
June 18, 2019 at 7:44 pm
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
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