>>>>>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
>>>>>
>>>>>
>>>>>If I have to, I will, once again, write a procedure to scan the table and write the cursor, but it would be nice to make it with a SQL SELECT.
>>>>>
>>>>>Thank you.
>>>>
>>>>Day of puzzles:). At least, this puzzle I know:
>>>>select *,recno() as recno from table3 into cursor tmp1 order by date
>>>>select numtot,recno+1 as recno from tmp1 into cursor tmp2
>>>>select date,tmp1.numtot-tmp2.numtot as exphours from tmp1,tmp2 where tmp1.recno=tmp2.recno
>>>
>>>What if "table3" is actually some cursor? I had the idea that RECNO() wasn't a good thing to use under that circumstance. I also wonder how it is affected by SET DELETED or whatever.
>>>
>>>The basic idea of using RECNO() is to number the records in the query. Is there a convenient way to "autonumber" records in a query, i.e. assign 1,2,3 to subsequent records in your chosen order? Actually, I can think of a way. It involves SELECT *, 1 AS counter FROM mytable ORDER BY date, and then do Michael Levy's non-equi-self-join to make a running total of the "counter" field. If I do the joining right, I may be able to set the counter back to 1 at the beginning of a group. But I wonder if there is something simpler or faster.
>>
>>The approach, i gave you, works OK. If you concerend about initial order, then just run one more select initially:
>>Select * From table1 into cursor tmp nofilter order by date
>>and you make sure that physical order of records (i.e. recno()) you will use in next SQLs is trustworthy.
>>SET DELETED setting will assure that you will skip or not skip all deleted records (it's up to you), so there is no problem here.
>
>I have vfp3. I don't see the "NOFILTER" keyword in Help. Do I need it here? What does it do? Or are you just saying "don't use a WHERE clause"?

Ok. It will work even without nofilter, just add someting like:
Select *,"" as dummy From .....
The only purpose of this select is to provide that recno() will trace order by date.