Cummulative Total

I'm trying to show a running sum on a set of records ordered by
Date and Order Number to prioritize shipments by first-in-first-out.

CREATE TABLE #TMP (
DUE_DATE DATETIME,
ORD_NUM CHAR(10),
PRODUCT CHAR(3),
TONS REAL)

INSERT INTO #TMP
SELECT '2/23/07', '07026.0030', 'ABC', 3.375
UNION ALL
SELECT '2/23/07', '07047.0059', 'ABC', 3.375
UNION ALL
SELECT '2/23/07', '07053.0080', 'ABC', 3.375
UNION ALL
SELECT '2/24/07', '07045.0030', 'ABC', 2.25
UNION ALL
SELECT '2/25/07','07045.0027','ABC',1.125
UNION ALL
SELECT '2/25/07','07046.0070','ABC',6.75

SELECT T1.PRODUCT, T1.DUE_DATE, T1.ORD_NUM, T1.TONS,
'TTL_TONS'=(SELECT SUM(TONS) AS CUMM
FROM #TMP T2
WHERE T1.DUE_DATE>=T2.DUE_DATE AND T1.ORD_NUM>=T2.ORD_NUM )
FROM #TMP T1

DROP TABLE #TMP

The first 3 records returned show the right numbers in the ttl_tons column,
but then it falls apart after that?

Any hints?Maybe


CREATE TABLE #TMP (
DUE_DATE DATETIME,
ORD_NUM CHAR(10),
PRODUCT CHAR(3),
TONS REAL)
INSERT INTO #TMP
SELECT '20070223', '07026.0030', 'ABC', 3.375
UNION ALL
SELECT '20070223', '07047.0059', 'ABC', 3.375
UNION ALL
SELECT '20070223', '07053.0080', 'ABC', 3.375
UNION ALL
SELECT '20070224', '07045.0030', 'ABC', 2.25
UNION ALL
SELECT '20070225','07045.0027','ABC',1.125
UNION ALL
SELECT '20070225','07046.0070','ABC',6.75
SELECT T1.PRODUCT
, T1.DUE_DATE
, T1.ORD_NUM
, T1.TONS
, 'TTL_TONS'=(SELECT SUM(TONS) AS CUMM
FROM #TMP T2
WHERE T1.DUE_DATE>=T2.DUE_DATE
AND T1.ORD_NUM >= CASE
WHEN T1.DUE_DATE=T2.DUE_DATE THEN
T2.ORD_NUM
ELSE
T1.ORD_NUM
END)
FROM #TMP T1
ORDER BY
due_date
, ord_num
DROP TABLE #TMP|||I assume you want to avoid a cursor...but, since the dattime vaalue is not unique, it still would be random|||That's what I was looking for.

Thanks|||I assume you want to avoid a cursor...but, since the dattime vaalue is not unique, it still would be random

Yeah, I'm actually rewriting an older sproc that uses a cursor. I'm trying to get away from them whenever I can.|||Interesting reading. Don't know if he ever sorted out his summary\ conclusion though - it bears little relation to information in the article. Anyhoo -

http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp|||I'm thinking along the lines of an IDENTITY

SELECT T1.PRODUCT, T1.DUE_DATE, T1.ORD_NUM, T1.TONS,
'TTL_TONS'=( SELECT SUM(TONS) AS CUMM
FROM #TMP T2
WHERE T1.DUE_DATE > T2.DUE_DATE
-- AND T1.ORD_NUM > T2.ORD_NUM
AND T1.RowID > T2.RowId)
FROM #TMP T1|||Do you not feel a little dirty altering a table schema to meet the derived-data requirment of a query? More efficient but... well... naughty.|||I was actually on the verge of adding an identity column before posting this question. I really wanted to see if it could be done without one, because this method will make the code more understandable when I have to come back to it in 6 months.|||Do you not feel a little dirty altering a table schema to meet the derived-data requirment of a query? More efficient but... well... naughty.

Listen, if you want to use SQL to do ostuff that should be a presentation issue, then all bets are off.

Besides, I like being naughty|||Listen, if you want to use SQL to do ostuff that should be a presentation issue, then all bets are off.

...

Why do it at the presentaton level, when it's so much easier to do it in SQL?|||performance, performance, performance.|||pootle,

Not sure this is what you meant, but...

Yes, it's all about performance: I imagine it would take me a few weeks to rewrite the 3 reports that hit the recordset my sproc supplies. Once that was done, my guess is that it would take 5 minutes for the biggest report to generate "on demand". My users would throw a fit over a report taking that long. This sproc takes 6 seconds now, (it took 6 minutes when I was using a cursor), and the report comes up instantly.

So why not use the most efficient tool for the job?|||You don't think iot would be fatser on the fron end?

What reporting tool are you using?

Go Rangers!|||I'm using Actuate report writer. Since I can use a sproc to supply data to the report, and pass parameters back and forth between the two, I guess I've always viewed the sproc as just a part of the report.|||No experience of actuate. In access (where you know most of my experience lies :D) there is no real overhead for running totals. I am surprised you anticipate such a hit.

The performance thing is:
You have (probably) one database server.
You have n clients.

If you make the server perform presentation manipulations for every data request for these three reports then you are using up scarce resources on every call. Get the client to do this and you distribute the processing around your network. Even if the server does this more quickly it does not follow that the process is more efficient.

Case in point see below:
CREATE TABLE #TMP
(
DUE_DATE DATETIME,
ORD_NUM CHAR(10),
PRODUCT CHAR(3),
TONS REAL
)

INSERT INTO #TMP
SELECT'20070223','07026.0030','ABC', 3.375
UNION ALL
SELECT'20070223','07047.0059','ABC', 3.375
UNION ALL
SELECT'20070223','07053.0080','ABC', 3.375
UNION ALL
SELECT'20070224','07045.0030','ABC', 2.25
UNION ALL
SELECT'20070225','07045.0027','ABC',1.125
UNION ALL
SELECT'20070225','07046.0070','ABC',6.75

SET STATISTICS IO ON

SELECT T1.PRODUCT
, T1.DUE_DATE
, T1.ORD_NUM
, T1.TONS
,'TTL_TONS'=(SELECT SUM(TONS)AS CUMM
FROM #TMP T2
WHERE T1.DUE_DATE>=T2.DUE_DATE
AND T1.ORD_NUM >=CASE
WHEN T1.DUE_DATE=T2.DUE_DATE THEN
T2.ORD_NUM
ELSE
T1.ORD_NUM
END)
FROM #TMP T1
ORDER BY
due_date
, ord_num

SELECT T1.PRODUCT
, T1.DUE_DATE
, T1.ORD_NUM
, T1.TONS
FROM #TMP T1
ORDER BY
due_date
, ord_num

SET STATISTICS IO OFF

DROP TABLE #TMP


This results in (slightly edited but only for dsiplay puposes):
Table '#TMP'. Scan count 7, logical reads 7, physical reads 0, read-ahead reads 0.

Table '#TMP'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Seven fold increase in logical reads if working out the total in the BE.|||might latest readings have been telling me running totals are a legitimate use of cursors with regards to performance.|||This is something of a series of related blog posts on running totals. First one shows why a cursor is faster than one type of set-based method. The middle link has a faster method which is set based. the last method is a bit faster still but requires a CLR proc.

http://www.sqljunkies.com/WebLog/amachanic/archive/2006/02/28/18286.aspx
http://blogs.conchango.com/jamiethomson/archive/2006/02/28/3001.aspx
http://www.sqljunkies.com/WebLog/amachanic/archive/2006/02/28/18309.aspx

EDIT: i think it's generally true that if you find yourself writing a cursor and don't see another way, the CLR will be much faster if you can use it. requires 2005 obviously.|||Post six in this thread. Come on chaps - keep up :p|||Thanks to all for the help. After reading the posts (and links), I'm afraid I probably did this the wrong way - but hey, it's a lot faster and more accurate than it used to be :)

What I was looking for here was just a small piece of what I was trying to do,
and these totals were necessary for processing further down in the sproc.

Thanks again

Oh, and Pootle...
My knowledge of servers is limited to: They are a magic box located in a different building, that cause the 50 year old men
that work on them to grow pony-tails.