Bret,
Listed below is what I came up with. I listed the sample data, sample results, and the program.
The one anomaly occurred in the final select. For
some reason the 'ISNULL' statement was not working correctly. In order to make
it work I had to put my exception filtering in the HAVING statement.
-myron kirby-
myronk@flash.net
-------------------------------------
Sample data:
RECKEY RECDTE RUNTOTAL
BILL 04/05/98 100.00
BILL 04/12/98 105.00
BILL 04/19/98 120.00
BILL 04/26/98 130.00
SARAH 04/05/98 30.00
SARAH 04/12/98 60.00
FRED 04/12/98 100.00
FRED 04/19/98 110.00
FRED 04/26/98 125.00
KEVEN 04/05/98 222.00
-----------------------------------
Sample results:
RECKEY CURRECSEQ FROMDTE FROMTOT TODTE TOTOT WKTOT
BILL 1 04/05/98 100.00 04/12/98 105.00 5.00
BILL 2 04/12/98 105.00 04/19/98 120.00 15.00
BILL 3 04/19/98 120.00 04/26/98 130.00 10.00
FRED 1 04/12/98 100.00 04/19/98 110.00 10.00
FRED 2 04/19/98 110.00 04/26/98 125.00 15.00
KEVEN 1 04/05/98 222.00 / / 0.00 222.00
SARAH 1 04/05/98 30.00 0 4/12/98 60.00 30.00
------------------------------------
Sample program
PRIVATE;
pcRecKey ,;
plIsPrePass,;
pnSeq
pcRecKey = [@adf@]
pnSeq = 0
plIsPrePass = .T.
*--Sequence the file
SELECT;
RecKey ,;
RecDte ,;
RunTotal ;
FROM C:\Temp\Mlk1;
ORDER BY 1, 2;
INTO TABLE C:\Temp\FPass1
*--Add sequence identifier
SELECT;
RecKey ,;
CalcSeq(RecKey) AS [RecSeq],;
RecDte ,;
RunTotal ;
FROM C:\Temp\FPass1;
ORDER BY 1, 2;
INTO TABLE C:\Temp\FPass2
*--Create weekly totals
SELECT;
Cur.RecKey ,;
Cur.RecSeq AS [CurRecSeq],;
Cur.RecDte AS [FromDte] ,;
Cur.RunTotal AS [FromTot] ,;
;
IIF(;
ISNULL(Nxt.RecSeq),;
{} ,;
Nxt.RecDte) AS [ToDte] ,;
;
IIF(;
ISNULL(Nxt.RecSeq),;
0000000 ,;
Nxt.RunTotal) AS [ToTot] ,;
;
IIF(;
ISNULL(Nxt.RecSeq),;
Cur.RunTotal ,;
Nxt.RunTotal - Cur.RunTotal) AS [WkTot],;
;
Nxt.RecKey AS [NxtRecKey];
FROM;
C:\Temp\FPass2 Cur;
LEFT OUTER JOIN;
C:\Temp\FPass2 Nxt;
ON Cur.RecKey = Nxt.RecKey;
AND Cur.RecSeq = Nxt.RecSeq - 1;
ORDER BY 1, 2;
HAVING;
ISNULL(NxtRecKey) = .F.;
OR CurRecSeq = 1 ;
INTO TABLE C:\Temp\FWkTot
CLOSE TABLE ALL
RETURN .T.
* -----------------
FUNCTION CalcSeq
LPARAMETER tcRecKey
IF tcRecKey == pcRecKey && Same record
pnSeq = pnSeq + 1
ELSE
pcRecKey = tcRecKey
pnSeq = 1
ENDIF && ..IF tcRecKey == pcRecKey
*--First time through VFP runs the query in
* order to set-up the query for processing
IF plIsPrePass = .T.
pnSeq = 0
plIsPrePass = .F.
ENDIF && ..IF IsPrePass = .T.
RETURN pnSeq
----------------------------
>>>My first query challenge was how to make a running total query. Michael Levy and Ed Pikman answered that one. Now I need to do the opposite. I am receiving data consisting of running totals of expended hours. I would like a query that extracts the amount expended each week. For example, if my data looks like this:
>>>
>>>
>>>4/5/98 100
>>>4/12/98 105
>>>4/19/98 120
>>>4/26/98 130
>>>
>>>
>>>...I would like a query to return this:
>>>
>>>
>>>4/12/98 5
>>>4/19/98 15
>>>4/26/98 10
>>>