CODEWITHSUNDEEP
sqlsql-servert-sql
Almazini
Almazini

Reputation: 1873

how to create table with formulas to calculate data in second table?

I have a ProductTable with 1000 products. For example below is just 7 products. The data for products 1-5 is imported to the table from sources by script. I'll use this data to calculate data for other products.

Then I have a second table with formulas to calculate data for products in the first table. There will be a lot of product and every of them will have a certain formula. For example below are 2 products. This table doesn't use digits but links to first table. Numbers are productsID from first table.

Third table is for a clear understanding and shows how calculations should be done. It is not a desired result. Result will be result of these calculations. (For ProductID6 and year1999 the result should be 3; for productID6 and year2001 result is 1.75, etc.)

1

How can I write a stored procedure to catch these formulas fork. So basically I want to make a way to store formulas for calculating products. And, if needed, be able to change formula and recalculate the result.

Would be happy to hear your ideas.

Upvotes: 1

Views: 365

Answers (1)

John Dewey
John Dewey

Reputation: 7093

Probably a number of ways to go with this, but I would try creating a scaler function that takes the year and ProductID as arguments and returns a single mapped formula.

-- Assumption is a Product table structured like this: Product(ProductID INT, [1999] INT, [2000] INT, [2001] INT);
CREATE FUNCTION dbo.MapFn(@Y INT, @ProductID INT)
RETURNS VARCHAR(200)
AS
BEGIN
    DECLARE @A VARCHAR(10), @B VARCHAR(10), @C VARCHAR(10);
    DECLARE @pvt TABLE(ProductID INT, Yr INT, Qty VARCHAR(10));
    DECLARE @f VARCHAR(80);

    INSERT @pvt
    SELECT ProductID, Yr, Qty
    FROM (
        SELECT ProductID, [1999], [2000], [2001]
        FROM dbo.Product) p
    UNPIVOT (
        Qty FOR Yr IN ([1999], [2000], [2001])
    ) AS u
    WHERE Yr = @Y;

    SELECT @f = formula FROM dbo.Formula WHERE ProductID = @ProductID
    SELECT @A = p.Qty + '.0' FROM @pvt p JOIN dbo.Formula f ON f.ProductID = @ProductID AND f.A = p.ProductID;
    SELECT @B = p.Qty + '.0' FROM @pvt p JOIN dbo.Formula f ON f.ProductID = @ProductID AND f.B = p.ProductID;
    SELECT @C = p.Qty + '.0' FROM @pvt p JOIN dbo.Formula f ON f.ProductID = @ProductID AND f.C = p.ProductID;

    RETURN REPLACE(REPLACE(REPLACE(@f,'A', ISNULL(CAST(@A AS VARCHAR(50)), '')), 'B', ISNULL(CAST(@B AS VARCHAR(50)), '')), 'C', ISNULL(CAST(@C AS VARCHAR(50)), ''));
END
GO

The function is brittle with the fixed year list in the UNPIVOT, but you can work around that by adding more years to the list.

Usage Example

CREATE TABLE dbo.Product(ProductID INT, [1999] INT, [2000] INT, [2001] INT);
GO
CREATE TABLE dbo.Formula(ProductID INT, formula VARCHAR(80), [A] INT, [B] INT, [C] INT);
GO

INSERT dbo.Product values(1,10,20,30)
, (2,15,25,35)
, (3,5,15,20)
, (4,4,8,12)
, (5,6,12,18)
, (6,NULL,NULL,NULL)
, (7,NULL,NULL,NULL);

INSERT dbo.Formula VALUES(6,'A/B',2,3,NULL)
, (7,'A/(B+C)',5,3,1);
GO

CREATE FUNCTION dbo.MapFn(@Y INT, @ProductID INT)
RETURNS VARCHAR(200)
AS
BEGIN
    DECLARE @A VARCHAR(10), @B VARCHAR(10), @C VARCHAR(10);
    DECLARE @pvt TABLE(ProductID INT, Yr INT, Qty VARCHAR(10));
    DECLARE @f VARCHAR(80);

    INSERT @pvt
    SELECT ProductID, Yr, Qty
    FROM (
        SELECT ProductID, [1999], [2000], [2001]
        FROM dbo.Product) p
    UNPIVOT (
        Qty FOR Yr IN ([1999], [2000], [2001])
    ) AS u
    WHERE Yr = @Y;

    SELECT @f = formula FROM dbo.Formula WHERE ProductID = @ProductID
    SELECT @A = p.Qty + '.0' FROM @pvt p JOIN dbo.Formula f ON f.ProductID = @ProductID AND f.A = p.ProductID;
    SELECT @B = p.Qty + '.0' FROM @pvt p JOIN dbo.Formula f ON f.ProductID = @ProductID AND f.B = p.ProductID;
    SELECT @C = p.Qty + '.0' FROM @pvt p JOIN dbo.Formula f ON f.ProductID = @ProductID AND f.C = p.ProductID;

    RETURN REPLACE(REPLACE(REPLACE(@f,'A', ISNULL(CAST(@A AS VARCHAR(50)), '')), 'B', ISNULL(CAST(@B AS VARCHAR(50)), '')), 'C', ISNULL(CAST(@C AS VARCHAR(50)), ''));
END
GO

-- Cursor loop to evaluate formulas
DECLARE @ProductID INT;
DECLARE @fa NVARCHAR(80);
DECLARE @fb NVARCHAR(80);
DECLARE @fc NVARCHAR(80);
DECLARE @sql NVARCHAR(160);
DECLARE @A DECIMAL(10,2), @B DECIMAL(10,2), @C DECIMAL(10,2)
DECLARE @resultSet TABLE(ProductID INT, [1999] DECIMAL(10,2), [2000] DECIMAL(10,2), [2001] DECIMAL(10,2));
DECLARE c CURSOR FOR 
    SELECT f.ProductID
    , [1999] = dbo.MapFn(1999,f.ProductID)
    , [2000] = dbo.MapFn(2000,f.ProductID)
    , [2001] = dbo.MapFn(2001,f.ProductID)
    FROM dbo.Formula f;
OPEN c
FETCH NEXT FROM c INTO @ProductID, @fa, @fb, @fc;

WHILE @@FETCH_STATUS = 0 BEGIN
    -- 1999
    SET @sql = N'SELECT @out = '+@fa;
    EXECUTE sp_executesql @sql, @params = N'@out DECIMAL(10,2) OUTPUT', @out = @A OUTPUT;

    -- 2000
    SET @sql = N'SELECT @out = '+@fb;
    EXECUTE sp_executesql @sql, @params = N'@out DECIMAL(10,2) OUTPUT', @out = @B OUTPUT;

    -- 2001
    SET @sql = N'SELECT @out = '+@fc;
    EXECUTE sp_executesql @sql, @params = N'@out DECIMAL(10,2) OUTPUT', @out = @C OUTPUT;

    INSERT @resultSet VALUES(@ProductID, @A, @B, @C);
    FETCH NEXT FROM c INTO @ProductID, @fa, @fb, @fc;
END
SELECT * FROM @resultSet;
CLOSE c;
DEALLOCATE c;

Result

ProductID   1999    2000    2001
6           3.00    1.67    1.75
7           0.40    0.34    0.36

Upvotes: 2

Related Questions