T-sql: running total and moving average based on transaction type

SHAHID 2013-10-02 12:14:48

I have a situation like inventory transaction table where each item has

  1. incoming or outgoing transaction including quantity and rate per unit
  2. each transaction have a transaction date column
  3. there are 20-30K transactions per item
  4. table row count is around 8-10 million records
  5. this is also a live table where user inserts/update and delete transaction on actual basis
  6. 50 to 100 users are using this table concurrently

What is needed;

  1. item wise date wise running total for quantity for each item (inward movement will add and outward movement will subtract from running total
  2. item wise date wise running value for quantity x rate (same rule for movement type will apply)
  3. an average for each row (item wise, date wise) based on running value

the most important part is running total, value and average rate for each row need to by updated after each DML operation on this table.

DB design suggestions or procedural suggestions are welcome

regards

Autochartist 2013-10-18 10:13:02
Are there separate columns for inward and outward movements, e.g.

count_received integer,
count_delivered integer

If you're using SQL Server 2012, you could perhaps try Windowing functions.

I think this will work, but I haven't tested it. I'm no expert, I just figure these things out as I go along, but this would be my first attempt:

SELECT itemid, orderdate, val,
SUM(count_received – count_delivered)
OVER(PARTITION BY itemid
ORDER BY orderdate, transactionid
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS runningtotal FROM transactions;

If that doesn't work, then using the above as an inner query should (if you're on SQL Server 2012):

select itemid, orderdate, val, (totalReceived – totalDelivered) as runningTotal
from (
SELECT itemid, orderdate, val,
SUM(count_received) OVER(PARTITION BY itemid
ORDER BY orderdate, transactionid
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS totalReceived FROM transactions
SUM(count_delivered) OVER(PARTITION BY itemid
ORDER BY orderdate, transactionid
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS totalDelivered FROM transactions
) a;

Lemme know how it goes.

SHAHID 2013-10-20 08:52:24
I have same column for inward quantity and outward quantity – just transaction type will determine the nature of quantity e.g plus or minus

I am using sqlserver 2000 – not window function is available

thanks for suggestions

Autochartist 2013-10-20 19:12:59
note: I'm posting this as a comment, since the system is not allowing me to add another answer to your question.

Hi Shahid

Here is a query that demonstrates obtaining a running total of transaction values without using SQL Server's 2012 windowing functionality:

select ov.custid , ov.orderdate ,
( select sum(val)
from sales.ordervalues ov2
where ov2.custid = ov.custid
and ov2.orderdate <= ov.orderdate ) as runningTotal from sales.ordervalues ov order by ov.custid, ov.orderdate

The inner query is within the select of the outer query, and references the table used in the outer query (actually, in my case it's a view, but the same syntax is used for a table).

where ov2.custid = ov.custid

ensures we're summing the transaction values for each custid mentioned in the outer query.

and ov2.orderdate <= ov.orderdate

means we're calculating the to-date running total

It's really not that efficient (compared to the windowing), but that's the best solution I can think of using SQL Server 2000.

It should be relatively easy to alter the above for your schema, to satisfy your first requirement.

Unfortunately I do not clearly understand your 2nd and 3rd requirements (quantity x rate, and average per row). If you still need help with these requirements, could you perhaps explain them in more detail?