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.

Cummulative

I'm having trouble creating a cummulative column in a table.

I've got the following table, sorted ASC PAID

Claim_Number Paid
123 $21.22
124 $34,87
456 $42.90
478 $67.00
890 $100.10

I want to add a column (in a new table) with the cummulative total paid. It should look like

Claim_Number Paid Cummulative
123 $21.00 $21.00
124 $34.00 $55.00
456 $42.00 $97.00
478 $67.00 $164.00
890 $100.00 $264.00

Thanks for the help.

RayIf you don't have thousands of rows, try using a CURSOR like this (I've assumed your table is named tblCumulative):

DECLARE @.Paid MONEY
DECLARE @.Claim_Number INT
DECLARE @.Total MONEY
DECLARE @.Cumulative MONEY

SET @.Total = 0.0

DECLARE cur_Cumulative CURSOR FOR
SELECT Paid, Claim_Number, Cumulative
FROM tblCumulative
ORDER BY Paid ASC, Claim_Number ASC

OPEN cur_Cumulative
FETCH NEXT FROM cur_Cumulative INTO @.Paid, @.Claim_Number, @.Cumulative
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Total = @.Total + @.Paid
UPDATE tblCumulative
SET Cumulative = @.Total
WHERE Paid = @.Paid and Claim_Number = @.Claim_Number
FETCH NEXT FROM cur_Cumulative INTO @.Paid, @.Claim_Number, @.Cumulative
END

CLOSE cur_Cumulative
DEALLOCATE cur_Cumulative

Note that this falls down if the combination of Paid and Claim_Number is not unique. If this is possible, consider having an IDENTITY column as a primary key, and referencing that new column in the CURSOR and in the UPDATE WHERE clause.|||CURSORS!?!?!?!?!? NOOOOOOOOOO!!! Stomp on them! Crush their little heads! AAArrrggghhh! :mad:

Select YourTable.ClaimNumber,
YourTable.Paid,
Sum(SubTable.Paid) as Cummulative
From YourTable
Inner join YourTable SubTable
on YourTable.Paid >= SubTable.Paid
and YourTable.ClaimNumber >= Subtable.ClaimNumber --In case two claims paid the same...
Group by YourTable.ClaimNumber,
YourTable.Paid
Order by YourTable.Paid,
YourTable.ClaimNumber

Aaahhh...I feel better now. There is one less cursor in the world. :)

...though why you want cumulative sorted by paid amount is beyond me...|||Close but no cigar. Using Blindman's approach, with 2 invoices for the same amount (I added Claim_Number = 1, Paid = 34.87) ...

123 21.2200 21.2200
1 34.8700 34.8700
124 34.8700 90.9600
456 42.9000 133.8600
478 67.0000 200.8600
890 100.0000 300.8600

... but using the hated cursor (yes, I hate them too)

123 21.2200 21.2200
1 34.8700 56.0900
124 34.8700 90.9600
456 42.9000 133.8600
478 67.0000 200.8600
890 100.0000 300.8600

I'm willing to bet Blindman can fix his solution with the next post, so although the cursor solution still works, I'd hold off for a few more hours yet!|||Yes, that was a gotcha, but fortunately I do not smoke cigars.

set nocount on

create table #Invoices (ClaimNumber int, Paid money)

insert into #Invoices (ClaimNumber, Paid) values (123, $21.22)
insert into #Invoices (ClaimNumber, Paid) values (1, $34.87)
insert into #Invoices (ClaimNumber, Paid) values (124, $34.87)
insert into #Invoices (ClaimNumber, Paid) values (456, $42.90)
insert into #Invoices (ClaimNumber, Paid) values (478, $67.00)
insert into #Invoices (ClaimNumber, Paid) values (890, $100.10)

Select #Invoices.ClaimNumber,
#Invoices.Paid,
Sum(SubTable.Paid) as Cummulative
From #Invoices
Inner join #Invoices SubTable
on #Invoices.Paid > SubTable.Paid
or (#Invoices.Paid = SubTable.Paid and #Invoices.ClaimNumber >= Subtable.ClaimNumber)
Group by #Invoices.ClaimNumber,
#Invoices.Paid
Order by #Invoices.Paid,
#Invoices.ClaimNumber

Drop table #Invoices

I still think the requirement is a little odd, as far as including the Paid value in the record ordering.|||Blindman.
Thanks for the help. Why order the paid cummulative is because we do stratified sampling for audits. You sort everything by dollars and then cut into strata. That's basically a crappy way of saying that it enables you to sample claims in a manner that you audit more dollars per claim than you would if you sampled straight random.

Ray|||Manipulating data to give the impression of better performance!? What is business in this country coming to?

"I am shocked, shocked, to find accounting going on here."|||No, not manipulating data for the sake of performance. But perhaps a bit misleading. Stratification is a used on lots of sampling algorithms (stratify by age, income, claim dollars,...). You're basically sorting the population (by age, paid dollars, income,...) and then chopping it into strata to make sure your sample is represented by the right age or dollar or income levels.

The average healthcare claim in the U.S. is about $175. And the average person has about 10 a year. Health insurance companies are required to measure payment accuracy. If they straight random sample they end up with a sample (of say 400 claims) of which 80 to 90% are claims under $200. Stratification means they'll put more high dollar claims in their sample and so they can audit them and make sure they're being paid correctly.|||Why are U.S. corporations always talking about the "average person"? What about those of us who are below average? Nearly 50% of us are, you know, and some of us are significantly below average. We are people too. I'm tired of being marginalized just because, well, just because I'm on the margins.

Hopefully our situation will improve now that we have one of our own in the White House.|||...but fortunately I do not smoke cigars.

THAT is just WRONG...sick and WRONG, I tell ya... SICK and WRONG!!!! ;)