Creating column based on calculations

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating column based on calculations

Author

Topic

HowlingOdin
Starting Member

4 Posts

Posted - 2011-11-18 : 14:00:42

Hello,

I need to create an "inventory" column. If you take a look at the structure below, the inventory column tracks the inventory for a particular item in a particular store. It lowers in value any time there's an associated value in the sales qty. Any ideas on how I would create such an inventory column?

I did not set up this database; please do not ask me why it's set up the way it is. I'm going to pretend that all items started with 100 Inventory

Store Item Sales Qty Inventory
A x 3 97
A y 1 99
B x 1 96
C z 2 98
B y 3 96
A y 2 94

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-19 : 01:38:32

since its already existing, what you could do is to add a trigger to do this process something like


CREATE TRIGGER SetInventory
ON tablename
FOR INSERT,UPDATE
AS
BEGIN
UPDATE t
SET t.Inventory = t.Inventory - (t.SalesQty + COALESCE(PrevTotal,0))
FROM Tablename t
INNER JOIN INSERTED i
ON i.Store=t.Store
AND i.Item = t.Item
LEFT JOIN (SELECT Item,SUM(SalesQty) AS PrevTotal
            FROM Tablename t1
            LEFT JOIN INSERTED i1
            ON i1.Store=t1.Store
            AND i1.Item = t1.Item
            WHERE i1.Store IS NULL
            GROUP BY Item
           )p
ON p.Item = t.Item
END

Also do you've any field (may id or datefield) which indicates sequence of items in this table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources