1. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941

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.

Code:
```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?
Last edited by RedNeckGeek; 02-23-07 at 09:53.

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Maybe

Code:
```
CREATETABLE #TMP (
DUE_DATE DATETIME,
ORD_NUM CHAR(10),
PRODUCT CHAR(3),
TONS REAL)
INSERTINTO #TMP
SELECT'20070223','07026.0030','ABC', 3.375
UNIONALL
SELECT'20070223','07047.0059','ABC', 3.375
UNIONALL
SELECT'20070223','07053.0080','ABC', 3.375
UNIONALL
SELECT'20070224','07045.0030','ABC', 2.25
UNIONALL
SELECT'20070225','07045.0027','ABC',1.125
UNIONALL
SELECT'20070225','07046.0070','ABC',6.75
SELECT T1.PRODUCT
, T1.DUE_DATE
, T1.ORD_NUM
, T1.TONS
,'TTL_TONS'=(SELECTSUM(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
ORDERBY
due_date
, ord_num
DROPTABLE #TMP
```

3. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
I assume you want to avoid a cursor....but, since the dattime vaalue is not unique, it still would be random

4. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
That's what I was looking for.

Thanks

5. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
Originally Posted by Brett Kaiser
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.

6. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
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.co...ly_problem.asp

7. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
I'm thinking along the lines of an IDENTITY

Code:
```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```

8. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
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.

9. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
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.

10. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Originally Posted by pootle flump
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

11. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
Originally Posted by Brett Kaiser
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?

12. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
performance, performance, performance.

13. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
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?

14. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
You don't think iot would be fatser on the fron end?

What reporting tool are you using?

Go Rangers!

15. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•