Rolling Sum

[dgp]

Hi,

this is my first question in this site.

I have a database with the following fileds and associated values


areacode dt rainfall
---------------------------
AAA 1/1/2005 10
AAA 2/1/2005 4
AAA 6
. 0
.
.
.
AAA 31/12/2005

SOME BODY HELP ME IN FINDING OUT ROLLING SUM OF RAINFALL
FOR THE WHOLE YEAR ,FOR EVERY FIVE DAYS.

To put it simple ,consider the following data
rainfall rollingsum
1 15
2 20
3 25
4
5
6
7

Also ,i would apprecite on tips how to address the fact that a leap year has 366 days in the above scenario.

 

[namliam]

It aint pretty, and isnt going to be fast... but you can do it using a query and a DSum...

Something like this SQL

SELECT Table1.Area
,      Table1.TheDate
,      Table1.Rainfall
,      DSum("[Rainfall]","[Table1]","Area= """ & [Area] & """ and TheDate<=#" & Format([thedate],"yyyy-mm-dd") & "# and TheDate>=#" & Format([thedate]-5,"yyyy-mm-dd") & "#") AS 5DaySum
FROM Table1;

Will return this:

Area	TheDate	Rainfall	5DaySum
1	1/1/2005	5	5
1	1/2/2005	4	9
1	1/3/2005	7	16
1	1/4/2005	45	61
1	1/5/2005	5	66
1	1/6/2005	8	74
1	1/7/2005	9	78
1	1/8/2005	3	77
1	1/9/2005	5	75
1	1/10/2005	7	37
1	1/11/2005	2	34
1	1/12/2005	2	28
2	1/1/2005	7	7
2	1/2/2005	54	61
2	1/3/2005	5	66
2	1/4/2005	3	69
2	1/5/2005	5	74
2	1/6/2005	7	81
2	1/7/2005	8	82
2	1/8/2005	3	31
2	1/9/2005	6	32
2	1/10/2005	8	37
2	1/11/2005	3	35
2	1/12/2005	6	34

I hope this is what you are looking for....

 

[dgp]

hi , The Mailman ,ur response gives me a 'cumulative sum',I am in the
requirement of 'Rolling Sum'.Something like this:

RAINFALL , ROLLING_RAINFALL
A , A+B+C+D+E
B , B+C+D+E+F
C , C+D+E+F+G
D , D+E+F+G+H
E , E+F+G+H +I
F , F+G+H +I+J
G ,
H,
I,
J,

Both Columns are Decimal numbers .
I would be thankful for the forum for finding a solution to this query coz this is urgent to me.Fine with me if the solution is given using VBA OR VB

 

[namliam]

RAINFALL , ROLLING_RAINFALL
A , A+B+C+D+E
B , B+C+D+E+F
C , C+D+E+F+G
D , D+E+F+G+H
E , E+F+G+H +I
F , F+G+H +I+J
G ,
H,
I,
J,

That is exactly what my query does
Except it works like so:

Rainfall Rolling
A        A
B        B+A
C        C+B+A
D        D+C+B+A
E        E+D+C+B+A
F        F+E+D+C+B
etc...

My way is summing the past 5 days, including today. Your way is summing today + 5 days in the future, which to me doesnt make sence... but if that is your requirement....

SELECT Table1.Area
,      Table1.TheDate
,      Table1.Rainfall
,      DSum("[Rainfall]","[Table1]","Area= """ & [Area] & """ and TheDate[b]>[/b]=#" & Format([thedate],"yyyy-mm-dd") & "# and TheDate[b]<[/b]#" & Format([thedate][b]+[/b]5,"yyyy-mm-dd") & "#") AS 5DaySum
FROM Table1;

3 small changes to the SQL and presto... done ...

Not having any values for the last 4 days is going to be little harder....

But a query more or less like this one:

SELECT Day0.Area, Day0.TheDate, IIf(IsNull([day4].[rainfall]),"",[Day0].[rainfall]+[Day1].[rainfall]+[Day2].[rainfall]+[Day3].[rainfall]+[Day4].[rainfall]) AS Expr1
FROM (((Table1 AS Day0 LEFT JOIN Table1 AS Day1 ON (Day0.TheDate = Day1.TheDate-1) AND (Day0.Area = Day1.Area)) 
LEFT JOIN Table1 AS Day2 ON (Day1.TheDate = Day2.TheDate-1) AND (Day1.Area = Day2.Area)) 
LEFT JOIN Table1 AS Day3 ON (Day2.TheDate = Day3.TheDate-1) AND (Day2.Area = Day3.Area)) 
LEFT JOIN Table1 AS Day4 ON (Day3.TheDate = Day4.TheDate-1) AND (Day3.Area = Day4.Area);

Will produce these results:

Area	TheDate	Rainfall	Expr1
1	1/1/2005	5	66
1	1/2/2005	4	69
1	1/3/2005	7	74
1	1/4/2005	45	70
1	1/5/2005	5	30
1	1/6/2005	8	32
1	1/7/2005	9	26
1	1/8/2005	3	19
1	1/9/2005	5	
1	1/10/2005	7	
1	1/11/2005	2	
1	1/12/2005	2	
2	1/1/2005	7	74
2	1/2/2005	54	74
2	1/3/2005	5	28
2	1/4/2005	3	26
2	1/5/2005	5	29
2	1/6/2005	7	32
2	1/7/2005	8	28
2	1/8/2005	3	26
2	1/9/2005	6	
2	1/10/2005	8	
2	1/11/2005	3	
2	1/12/2005	6

I hope this gets you where you want to go....

 

[Cosmos75]

Say I have this table.

tblData
MyDateField (Date/Time)
MyValue (Long)

To create a sum of the current record and all previous records that are withing the past 4 days, for a total of 5 days, you can use correlated subqueries to do somethinig like this.

SELECT tblData.MyDateField, tblData.MyValue, 
             [COLOR="Blue"](SELECT SUM(a.MyValue) 
              FROM tblData As a 
              WHERE DateDiff("d",a.MyDateField,tblData.MyDateField) 
                           Between 0 and 4) 
             AS Past5DaySum[/COLOR]
FROM tblData;

To get the sum of the current record, plus the most recent 4 records (whether or not there is a gap in data, you can use a subquery as criteria to do something like this.

SELECT tblData.MyDateField, tblData.MyValue, 
             [COLOR="Blue"]Sum([B][COLOR="Red"]tblData_Values.MyValue[/COLOR][/B]) AS Prev5DataSum[/COLOR]
FROM tblData, tblData AS tblData_Values
WHERE [B][COLOR="Red"]tblData_Values.MyDateField[/COLOR][/B] 
             [COLOR="Blue"]In [COLOR="Blue"](SELECT TOP 5 a.MyDateField 
                   FROM tblData as a 
                   WHERE a.MyDateField<=tblData.MyDateField 
                   ORDER BY a.MyDateField DESC)[/COLOR][/COLOR]
GROUP BY tblData.MyDateField, tblData.MyValue
ORDER BY tblData.MyDateField;

You can find links to sample files here - Summing a numeric field for previous days (calendar OR days with data) using a subquery

 

[dgp]

Thanks guys ,

I figured the solution using VB6 ,hope this would be useful to others
as well.

Here goes the code in VB6:
-------------------------
Dim adoConn As New ADODB.Connection
Dim adoRecSet As New ADODB.Recordset
Dim adoRecSetSub As New ADODB.Recordset

adoConn.ConnectionString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.mdb;Persist Security Info=False "
adoConn.CursorLocation = adUseClient
adoConn.Open

adoRecSet.CursorType = adOpenDynamic
adoRecSet.LockType = adLockOptimistic
Set adoRecSet = adoConn.Execute("SELECT * FROM TABLE1 ")




Dim curr As Integer
Dim reccount As Integer
reccount = adoRecSet.RecordCount


Do While Not adoRecSet.EOF
curr = adoRecSet(0)

List1.AddItem adoRecSet(1)
If curr <= reccount - 4 Then
Set adoRecSetSub = adoConn.Execute("SELECT SUM(RF) FROM (SELECT TOP 5 * FROM Table1 WHERE ID >= " & curr & " )")
List2.AddItem adoRecSetSub(0) '<-------------"Here is the rolling sum"
End If

adoRecSet.MoveNext
Loop


adoRecSetSub.Close
adoRecSet.Close
adoConn.Close