CODEWITHSUNDEEP
sqlsql-server-2005
apfiru
apfiru

Reputation: 23

Running sum with aggregate function

I am retrieving the results of the mlog table and calculate the subtotal of the qtyn with the help of following code 1. I am stuck with how to join my second code criteria with the first.

Thanks for any help

1.

SELECT autn, date, itcode, qtyn, out,
       date, phstock,
       qtyn + COALESCE(
                (SELECT SUM(qtyn) FROM  dbo.mlog b
                 WHERE b.autn < a.autn
                     AND itcode = '40'), 0) AS balance
FROM dbo.mlog a
WHERE (itcode = '40')
ORDER BY autn

2.

date >=(SELECT MAX([date]) FROM mlog)

Upvotes: 0

Views: 162

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I have heard that SQL Server is rather inefficient with coalesce(), because it runs the first part twice. Here is an alternative way of writing this:

with ml as (
    SELECT ml.autn, ml.date, ml.itcode, ml.qtyn, ml.out, ml.date, ml.phstock
    FROM dbo.mlog ml
    WHERE ml.itcode = '40' AND ml.date >= (SELECT MAX(ml1.date]) FROM mlog ml1)
   )
select ml.*,
       (select sum(m1l.qtyn) from ml ml1 where ml1.autn <= ml.autn) as balance
from ml
ORDER BY ml.autn

I also wonder if the where clause would be more efficient as:

    WHERE ml.itcode = '40' AND ml.date = (SELECT top 1 ml1.date FROM mlog ml1 order by ml1.date desc)

Upvotes: 0

Stuart Blackler
Stuart Blackler

Reputation: 3772

To append a condition to the code, use AND or OR. EG:

SELECT a.autn, a.date, a.itcode, a.qtyn, a.out,
       a.date, a.phstock,
       a.qtyn + COALESCE(
                (SELECT SUM(b.qtyn) FROM  dbo.mlog b
                 WHERE b.autn < a.autn
                     AND b.itcode = '40'), 0) AS balance
FROM dbo.mlog a
WHERE (a.itcode = '40' AND a.date >= (SELECT MAX([c.date]) FROM mlog c) )
ORDER BY a.autn

Not tested, but should do what you want

Upvotes: 2

Related Questions