Running total using day before record.

I have 1 record per day with a Daily Difference. I would like to also keep
a
running difference. So I am looking for a way to insert the value for
RunningDiff
RunningDiff = PreviousDay's RunningDiff - current day's DailyDiff
date, $ShouldHave, $Have, DailyDiff, RunningDiff
1/23/2006, 12.50, 10.00, 2.50, 4.00
1/24/2006, 10.00, 7.00, 3.00, 1.00
1/25/2006, 10.00, 15.00, - 5.00, 6.00
1/26/2006, 17.00, 13.00, 4.00, 2.00
The RunnningDiff I can have update at the time a record is added, or can
have a stored procedure run multiple times throughout the day to update the
value.
thank you for any ideas,Ashley,
This could be achieved using cursors although I wouldn't recomend it if
the Table is going to be huge and processing time is going to be an
issue.
Barry|||Using a cursor would be find in this problem. Any suggestions of an example
to look at?
thanks!
"Barry" wrote:

> Ashley,
> This could be achieved using cursors although I wouldn't recomend it if
> the Table is going to be huge and processing time is going to be an
> issue.
> Barry
>|||Ahsley,
Have a look at this - I would test it in some different scenarios...I
haven't got time to fully test it I'm afraid.
HTH
Barry
Create Table Test
(
ProcessingDate Datetime Not Null,
TargetTotal Money Not Null,
CurrentTotal Money Not Null,
DailyDiff Money Null,
RunningDiff Money Null
)
Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
Values ('2006-01-23', 12.50, 10.00)
Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
Values ('2006-01-24', 10.00, 7.00)
Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
Values ('2006-01-25', 10.00, 15.00)
Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
Values ('2006-01-26', 17.00, 13.00)
Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
Values ('2006-01-27', 16.50, 14.00)
Declare @.ProcessingDateFirst Datetime,
@.TargetTotalFirst Money,
@.CurrentTotalFirst Money,
@.DailyDiffFirst Money,
@.RunningDiffFirst Money,
@.ProcessingDateSecond Datetime,
@.TargetTotalSecond Money,
@.CurrentTotalSecond Money,
@.DailyDiffSecond Money,
@.RunningDiffSecond Money
Declare FirstRowCursor Cursor For
Select ProcessingDate, TargetTotal, CurrentTotal, DailyDiff,
RunningDiff
From Test
Open FirstRowCursor
Fetch Next From FirstRowCursor Into @.ProcessingDateFirst,
@.TargetTotalFirst, @.CurrentTotalFirst,
@.DailyDiffFirst, @.RunningDiffFirst
Declare SecondRowCursor Cursor For
Select ProcessingDate, TargetTotal, CurrentTotal, DailyDiff,
RunningDiff
From Test
Open SecondRowCursor
Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
@.TargetTotalSecond, @.CurrentTotalSecond,
@.DailyDiffSecond, @.RunningDiffSecond
Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
@.TargetTotalSecond, @.CurrentTotalSecond,
@.DailyDiffSecond, @.RunningDiffSecond
While @.@.Fetch_Status = 0
Begin
If @.DailyDiffFirst Is Null And @.RunningDiffFirst Is Null
Begin
Update Test
Set DailyDiff = TargetTotal - CurrentTotal, @.DailyDiffFirst =
TargetTotal - CurrentTotal,
RunningDiff = TargetTotal - CurrentTotal, @.RunningDiffFirst =
TargetTotal - CurrentTotal
Where ProcessingDate = @.ProcessingDateFirst
End
If @.DailyDiffFirst Is Not Null And @.RunningDiffFirst Is Not Null
Begin
Update Test
Set DailyDiff = TargetTotal - CurrentTotal
Where ProcessingDate = @.ProcessingDateSecond
Update Test
Set RunningDiff = @.RunningDiffFirst - DailyDiff
Where ProcessingDate = @.ProcessingDateSecond
End
Fetch Next From FirstRowCursor Into @.ProcessingDateFirst,
@.TargetTotalFirst, @.CurrentTotalFirst,
@.DailyDiffFirst, @.RunningDiffFirst
Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
@.TargetTotalSecond, @.CurrentTotalSecond,
@.DailyDiffSecond, @.RunningDiffSecond
End
Close FirstRowCursor
Close SecondRowCursor
Deallocate FirstRowCursor
Deallocate SecondRowCursor
Select * From Test|||Perfect.. Thank you!
"Barry" wrote:

> Ahsley,
> Have a look at this - I would test it in some different scenarios...I
> haven't got time to fully test it I'm afraid.
> HTH
> Barry
>
> Create Table Test
> (
> ProcessingDate Datetime Not Null,
> TargetTotal Money Not Null,
> CurrentTotal Money Not Null,
> DailyDiff Money Null,
> RunningDiff Money Null
> )
>
> Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
> Values ('2006-01-23', 12.50, 10.00)
> Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
> Values ('2006-01-24', 10.00, 7.00)
> Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
> Values ('2006-01-25', 10.00, 15.00)
> Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
> Values ('2006-01-26', 17.00, 13.00)
> Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
> Values ('2006-01-27', 16.50, 14.00)
>
> Declare @.ProcessingDateFirst Datetime,
> @.TargetTotalFirst Money,
> @.CurrentTotalFirst Money,
> @.DailyDiffFirst Money,
> @.RunningDiffFirst Money,
> @.ProcessingDateSecond Datetime,
> @.TargetTotalSecond Money,
> @.CurrentTotalSecond Money,
> @.DailyDiffSecond Money,
> @.RunningDiffSecond Money
>
> Declare FirstRowCursor Cursor For
> Select ProcessingDate, TargetTotal, CurrentTotal, DailyDiff,
> RunningDiff
> From Test
>
> Open FirstRowCursor
> Fetch Next From FirstRowCursor Into @.ProcessingDateFirst,
> @.TargetTotalFirst, @.CurrentTotalFirst,
> @.DailyDiffFirst, @.RunningDiffFirst
>
>
> Declare SecondRowCursor Cursor For
> Select ProcessingDate, TargetTotal, CurrentTotal, DailyDiff,
> RunningDiff
> From Test
>
> Open SecondRowCursor
> Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
> @.TargetTotalSecond, @.CurrentTotalSecond,
> @.DailyDiffSecond, @.RunningDiffSecond
> Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
> @.TargetTotalSecond, @.CurrentTotalSecond,
> @.DailyDiffSecond, @.RunningDiffSecond
>
>
> While @.@.Fetch_Status = 0
> Begin
> If @.DailyDiffFirst Is Null And @.RunningDiffFirst Is Null
> Begin
> Update Test
> Set DailyDiff = TargetTotal - CurrentTotal, @.DailyDiffFirst =
> TargetTotal - CurrentTotal,
> RunningDiff = TargetTotal - CurrentTotal, @.RunningDiffFirst =
> TargetTotal - CurrentTotal
> Where ProcessingDate = @.ProcessingDateFirst
>
> End
>
> If @.DailyDiffFirst Is Not Null And @.RunningDiffFirst Is Not Null
> Begin
>
> Update Test
> Set DailyDiff = TargetTotal - CurrentTotal
> Where ProcessingDate = @.ProcessingDateSecond
> Update Test
> Set RunningDiff = @.RunningDiffFirst - DailyDiff
> Where ProcessingDate = @.ProcessingDateSecond
> End
>
> Fetch Next From FirstRowCursor Into @.ProcessingDateFirst,
> @.TargetTotalFirst, @.CurrentTotalFirst,
> @.DailyDiffFirst, @.RunningDiffFirst
> Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
> @.TargetTotalSecond, @.CurrentTotalSecond,
> @.DailyDiffSecond, @.RunningDiffSecond
>
> End
> Close FirstRowCursor
> Close SecondRowCursor
> Deallocate FirstRowCursor
> Deallocate SecondRowCursor
>
> Select * From Test
>