Need trigger halp

[Maii]

I have following sample table

Item Qty Date Running Qty
A 1 01-01-2005 1
A 2 01-02-2005 2
A -1 01-02-2005 2
A 3 01-03-2005 5




Need a trigger which update running Qty using sum of Qty based on Date and Item

 

[TJRTech]

I think you messed up on your data sample. Shouldn't it be:

Item Qty Date Running Qty
A 1 01-01-2005 1
A 2 01-02-2005 3
A -1 01-02-2005 2
A 3 01-03-2005 5


And, if so, do you assume that the first (earliest) record for an item has its running qty, set by qty, and therefore doesn't need to be updated?

Assuming that, and the following table columns:

ITEM varchar(10),
QTY int,
DATE datetime,
RUNNING_QTY int

Then, your insert trigger would look something like this:

DECLARE @PREVIOUS_QTY int

-- Get previous QTY for the item, if there is one
SELECT TOP 1 @PREVIOUS_QTY = RUNNING_QTY 
FROM <table> 
WHERE ITEM=inserted.ITEM and
      DATE<@inserted.DATE

-- If there is a previous qty for item then update
-- the running qty for the newly inserted, updated item
IF @PREV_QTY IS NOT NULL
 BEGIN
   UPDATE inserted
   SET insert.RUNNING_QTY = @PREVIOUS_QTY + inserted.QTY
 END

Note, this trigger would work for INSERT only, not update as well, and will probably only work if the item qtys (orders I assume) come in, in date order.

TJR

 

[ESquared]

TJR,

Your code assumes he only inserts one line at a time. Second, I'm confused... what does updating the inserted table do? I was sure you had to update the underlying table, because in an after-update (normal) trigger the data modification is already done, and in a before-update trigger, you have to do the modification yourself.

If you can assume that new records will never have dates older than the maximum date already in the table:

UPDATE M
SET RunningQty = Qty + IsNull(SELECT TOP 1 RunningQty FROM MainTable P WHERE P.Date < M.Date ORDER BY P.Date DESC)
FROM MainTable M
  INNER JOIN Inserted I ON M.Date = I.Date
WHERE RunningQty IS NULL

And if you can't make that assumption, then take out the where clause.

If there's ever a chance that you'll have the same date in there twice, you'll need to modify things significantly.

 

[TJRTech]

Sorry, yes, you need to update the underlying table, not inserted (I was doing this from memory).

And, yes my solution assumes you insert one record at a time, in order, and no two orders for an item are on the same date.

Frankly I think there are way too many assumptions to take what I wrote, even if correct, and make it work in a meaningful way, and you mentioned several such assumptions.

Maii, if you could post with more information and actual table designs it might be helpful.

Oh, and I might suggest getting away from a trigger, and developing an SP that does a single insert and does the running qty calc for you.

TJR

 

[ESquared]

And I just noticed a flaw in my query. It wouldn't work right, because it wouldn't be able to add the runningqty from newly inserted multiple records in the same operation as it is setting the runningqty for those records. So unless you want a really twisted query (which I know I can write but would probably take 20 minutes to puzzle out), it's better to avoid adding qty to runningqty and just sum the qtys that make up the current value.

UPDATE MainTable
SET
  RunningQty = (SELECT Sum(Qty) FROM MainTable P WHERE P.Date < M.Date)
WHERE
  RunningQty IS NULL

I hate correlated subqueries, but it seems reasonable in this instance. It could also be done with a derived table. I don't know which way is better.