Working with date ranges 2

[TomBorcoman]

Hello, I have a table that looks like this:

NAME DATE HOURS
Smith 2/4/2004 4
Smith 2/9/2004 2
Smith 5/8/2004 1
Smith 7/1/2004 1
Smith 11/5/2004 5
Johnson 1/2/2003 2
Johnson 4/1/2003 1
Johnson 5/12/2003 6

What I'd like to do is have a running subtotal that is dependent upon a 90 day window where dates would be eliminated...that is, keep a running subtotal of the hours but when 90 days are elapsed, do not include a record...

So, this would be the desired output:

NAME DATE HOURS SUBTOTAL
Smith 2/4/2004 4 4
Smith 2/9/2004 2 6
Smith 5/8/2004 1 3
Smith 7/1/2004 1 4
Smith 11/5/2004 5 5
Johnson 1/2/2003 2 2
Johnson 4/1/2003 1 3
Johnson 5/12/2003 6 7


Most if not all date functions that I've reviewed simply look at the previous record and not all preceding records with each iteration. Thanks for looking...

 

[focwizard]

Interesting problem. Let me rephrase it. You want each record to look at ALL other records for the same person, and include ALL records which are within the past 90 days. Notice how NOW, we're not talking about cumulative totals.

Here's a code sample that does what you want:

-* create a file of original data, with index on name, for join              
TABLE FILE filename                       
PRINT *                 
ON TABLE HOLD AS fileix FORMAT FOCUS INDEX NAME  END                                                                          
-* issue 'join to all', so each name will match to all records with same name
JOIN NAME IN filename TO ALL NAME IN fileix AS J1.   
TABLE FILE filename                                 
SUM filename.HOURS fileix.HOURS AS 'SUBTOTAL'   
BY HIGHEST filename.NAME BY filename.DATE     
-* only include if within the last 90 days 
WHERE filename.DATE - fileix.DATE LE 90  
-* and dates in the past               
      AND filename.DATE GE fileix.DATE 
END

If the original file has direct access to the name field, you don't have to do the initial extract, but can JOIN the file to itself.

 

[TomBorcoman]

Thank you, aptly named focwizard! It did exactly what you said it would do....