• CELKO (9/11/2012)


    The short answer is that this is a running total problem which can be done with the SUM () OVER (PARTITION BY ..

    ORDER BY ..

    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    The better answer is that the DDL you attached is full of fundamental design errors. I also loved the leading commas, which I use as a symptom to diagnosis bad SQL. Your mindset is still stuck in punch cards and sequential magnetic tape files. Trust me; I have been at this for a few decades.

    Why is your vague “<nothing in particular>_qty” a FLOAT? Is it the on hand quantity, the back order quantity, the sold quantity? A fifty character “<nothing in particular>_status”? Invite crap data like this and it will come. You have no keys, so this is not a table at all.

    CREATE TABLE Stockroom

    (item_name VARCHAR(25) NOT NULL),

    trans_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,

    transaction_qty INTEGER NOT NULL

    CHECK (onhand_qty >= 0));

    The “id” and “seq” were obviously redundant, caused by an attempt to mimic a mag tape record number in in SQL. But so is the transaction type. Issuing something is a negative quantity; stocking it is positive. Use the sign of the quantity to put that fact into one column.

    INSERT INTO Stockroom

    VALUES

    ('ACTIFED', '2004-01-01', +100),

    ('ACTIFED', '2004-03-28', -5),

    ('ACTIFED', '2004-04-01', -5),

    ('ACTIFED', '2004-05-05', -5),

    ('ACTIFED', '2004-05-10', -5),

    ('AGIOLAX', '2004-01-01', +50),

    ('AGIOLAX', '2004-02-12', -10),

    ('AGIOLAX', '2004-03-28', +50),

    ('AGIOLAX', '2004-05-05', -25),

    ('OTRIVIN CHILD 0.5% NDrops', '2004-01-01', +200),

    ('OTRIVIN CHILD 0.5% NDrops', '2004-03-28', +50),

    ('OTRIVIN CHILD 0.5% NDrops', '2004-04-01', -15),

    ('OTRIVIN CHILD 0.5% NDrops', '2004-05-05', -10);

    SELECT item_name, trans_date,

    SUM (transaction_qty)

    OVER (PARTITION BY item_name ORDER BY trans_date)

    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    AS ohhand_qty

    FROM Stock_Room;

    Quite true Mr. Celko, IF (and the operative word here is IF) we were using SQL Server 2012. This is a SQL Server 2005 forum so I would suggest, sir, that keep your solutions to something that will work in the version of SQL Server that the individual is currently using. It might be nice to show someone what they are missing, but it doesn't help solve the current problem, now does it?