Running Totals using SQL and the Text File ODBC driver

With a data file containing detailed dated transactions how can you use SQL to record a running total?

Here's the RTData.txt data file:

Item	Date	Amount
A	01/01/2011	12.30
A	15/01/2011	16.00
B	16/01/2011	5.69
A	01/02/2011	65.12
B	01/02/2011	0.58
A	14/02/2011	8.91
A	28/02/2011	1.00
A	15/03/2011	9.00
B	01/04/2011	31.45
B	01/05/2011	738.00
B	21/05/2011	9.11
A	21/05/2011	10.93

Schema.ini looks as follows:

[RTData.txt]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0
Col1=Item Text width 255
Col2=Date Date
Col3=Amount Currency

The SQL written to show the running totals is as follows:

SELECT Cur.Item, Cur.Date, Cur.Amount, Sum(Cum.Amount) AS 'Running Total'
FROM RTData.txt Cum, RTData.txt Cur
WHERE Cum.Item = Cur.Item AND Cum.Date <= Cur.Date
GROUP BY Cur.Item, Cur.Date, Cur.Amount
ORDER BY Cur.Item, Cur.Date

The output is as follows:

Item	Date	Amount	Running Total
A	01/01/2011	12.30	12.30
A	15/01/2011	16.00	28.30
A	01/02/2011	65.12	93.42
A	14/02/2011	8.91	102.33
A	28/02/2011	1.00	103.33
A	15/03/2011	9.00	112.33
A	21/05/2011	10.93	123.26
B	16/01/2011	5.69	5.69
B	01/02/2011	0.58	6.27
B	01/04/2011	31.45	37.72
B	01/05/2011	738.00	775.72
B	21/05/2011	9.11	784.83

It adds another layer of complexity if we want to generate monthly totals and also keep a running total.  This SQL will do it:

SELECT Cur.item, Cur.month, sum(Cur.Amount)/count(Cur.Amount) AS Amount, sum(Cum.Amount) AS 'Running Total'
FROM (SELECT RTD.Item, (year(RTD.Date)*100)+month(RTD.Date) AS Month, Sum(RTD.Amount) AS Amount
FROM RTData.txt RTD
Group By RTD.Item, (year(RTD.Date)*100)+month(RTD.Date)) as Cur,
(SELECT RTD.Item, (year(RTD.Date)*100)+month(RTD.Date) AS Month, Sum(RTD.Amount) AS Amount
FROM RTData.txt RTD
Group By RTD.Item, (year(RTD.Date)*100)+month(RTD.Date)) as Cum
WHERE Cum.Item = Cur.Item AND Cum.Month <= Cur.Month
Group By Cur.Item, Cur.Month
ORDER BY Cur.Item, Cur.Month

The output is as follows:

Item	Month	Amount	Running Total
A	201101	28.30	28.30
A	201102	75.03	103.33
A	201103	9.00	112.33
A	201105	10.93	123.26
B	201101	5.69	5.69
B	201102	0.58	6.27
B	201104	31.45	37.72
B	201105	747.11	784.83

The trick here is to realise that the Amount column inflates artificially as we progress and we need to correct for this by dividing by count(Cur.Amount) which returns 1, 2, 3 ... with successive records.

Comments welcome.