Recursive SUM using DATEDIFF with record VALUE UPDATE

Recursive SUM using DATEDIFF with record VALUE UPDATE

I am working on implementing a points tracking system for monitoring
performance. The employee's objective is to have as few as points as
necessary. There is a points system in place that allows an employee to
reduce their points by performing better for various periods of time.
Points reduced by up to 1 for 30 days of performance, 2 points if they
reach 60 days without incident, and 4 points if they reach 90 days without
incident. They cannot accumulate a negative value, and points roll off
from oldest date to newest. The sample table below is a representation of
an existing table that has been imported into SQL Server 2012 Database.
Employee Points Date Previous Note
Smith, Joe 0.25 3/21/2013
Smith, Joe 1 4/1/2013
Smith, Joe 0.25 5/6/2013
Smith, Joe 0.5 5/8/2013
Smith, Joe 1 7/10/2013
Jones, Tom 1 4/10/2013
Jones, Tom 1 4/18/2013
Jones, Tom 0.5 4/22/2013
Jones, Tom 2 6/25/2013
Jones, Tom 0.25 7/26/2013
Jones, Tom 0.25 7/28/2013
Because of the dynamic use of data, going to multiple sources such as C#,
Excel, Email, there is a need to build deterministic functions and
generate either a Table or View that is updated that will display
something similar to the following. The example below would be as the
report would look were it pulled on May, 18, 2013. 1 point would have been
rolled off of each of the Employees. Because Joe's first point was .25, it
would be set to 0, and the next point which was 1.00 would be reduced by
the remaining .75 to a value of .25. Because Tom had 1 full point as his
first point, it would be subtracted from the 1 point, resulting in 0
points for that date. The function should also notate the previous value
of the Points Column before updating the Point value. The function should
supply a note as to the reason for altering the point, the date would be
nice in the note field, but a seperate Column for NoteDate could contain
the date. The function can run on a INSERT/UPDATE trigger as there is very
low daily usage. It should check current date with last point date.
Employee Points Date Previous Note
Smith, Joe 0.00 3/21/2013 0.25 Rolled Off by System - 30 Day
Policy 05/01/2013
Smith, Joe 0.25 4/1/2013 1.00 Rolled Off by System - 30 Day
Policy 05/01/2013
Smith, Joe 0.25 5/6/2013
Smith, Joe 0.50 5/8/2013
Jones, Tom 0.00 4/10/2013 1.00 Rolled Off by System - 30 Day
Policy 05/22/2013
Jones, Tom 1.00 4/18/2013
Jones, Tom 0.50 4/22/2013
By today's date of 08/24/2013 the results should reflect the following.
Joe would have a total of 0 points, and Tom would have a total of 2
points, and by the 28th would have only 1 point. There are approximately
2,000 records so it would be difficult to alter these manually. With the
nature of this system and it's implementation it would be possible to run
1 script to reconcile the current records, and have a seperate script to
manage ongoing entries.
Employee Points Date Previous Note
Smith, Joe 0.00 3/21/2013 0.25 Rolled Off by System - 30 Day
Policy 05/01/2013
Smith, Joe 0.00 4/1/2013 0.25 Rolled Off by System - 30 Day
Policy 06/07/2013
Smith, Joe 0.00 5/6/2013 0.25 Rolled Off by System - 30 Day
Policy 06/07/2013
Smith, Joe 0.00 5/8/2013 0.50 Rolled Off by System - 30 Day
Policy 06/07/2013
Smith, Joe 0.00 7/10/2013 1.00 Rolled Off by System - 30 Day
Policy 08/10/2013
Jones, Tom 0.00 4/10/2013 1.00 Rolled Off by System - 30 Day
Policy 05/22/2013
Jones, Tom 0.00 4/18/2013 1.00 Rolled Off by System - 60 Day
Policy 06/21/2013
Jones, Tom 0.00 4/22/2013 0.50 Rolled Off by System - 30 Day
Policy 07/25/2013
Jones, Tom 1.50 6/25/2013 2.00 Rolled Off by System - 30 Day
Policy 07/25/2013
Jones, Tom 0.25 7/26/2013
Jones, Tom 0.25 7/28/2013
I began using the following on the EmployeePerformance TABLE, to create a
view to show the number of days between each record for each employee by
their ID. Then I used a DENSE_RANK() to number each employee with all of
the information from the Attendance VIEW. My original thought was to use
an incrementing VARIABLE to cycle through a WHILE loop for each employee,
then checking the DATE_DIFFERENCE to see if there are any values greater
than 30. If not I had planned on INSERTING all records for that employee
into a final table, and removing them from the Attend2 Table. Much more
scripting than I think is necessary. I thought instead about using CURSOR,
to create a one-time pass, and then another function once reconcilled. I
have little experience with CURSOR though.
CREATE VIEW Attendance AS
SELECT A.ID, A.FullName, A.EmployeeID, A.AttendanceDate,
A.OccurrenceAmount, A.Comments, A.RecordCreatedDate, A.RecordCreatedUser,
(DATEDIFF(DAY, A.AttendanceDate, B.AttendanceDate))*-1 AS DATE_DIFFERENCE
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY AttendanceDate)
AS Row_Num, *
FROM dbo.EmployeePerformance) AS A
LEFT JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY AttendanceDate)
AS Row_Num, *
FROM dbo.EmployeePerformance) AS B
ON A.EmployeeID=B.EmployeeID AND A.Row_Num=B.Row_Num+1
SELECT *, DENSE_RANK() OVER (ORDER BY Attendance.EmployeeID) AS Row_Num
INTO dbo.Attend2
FROM dbo.Attendance
I have a few ideas I can think of to resolve this, but I am hoping that
someone might have had a similar situation or worked with a problem like
this before. I would much rather build this into a concise piece of code I
can trigger and with the SQL database being used in a low volume
enviornment I am not terribly worried about performance, but I would like
to find a good solution that will also keep performance in mind. Thanks
for any help or feedback you may have.