Sytze de Boer wrote:
> Wow, I knew that the "select" command was powerful, but this is incredible
> Is there a book out on this ?
>
> Anyhow, Ricardo, many thanks for below example.
> Unfortunately, it is not quite right. It gives me the result for the
> most recent 52 records
> I need the result for the most recent 52 records PER EMPLOYEE (empcode)
>
> Are you able to assist, please
>
I would have wanted to do :
select my.empcode, sum(my.gross) as GrossTotal ;
into cursor newtable ;
from mytable my ;
where my.cycle in (SELECT x.cycle ;
FROM (SELECT TOP 52 aux.cycle, RECNO();
from mytable as aux ;
where aux.empcode = my.empcode ;
order by 2 desc) as x) ;
order by empcode ;
group by empcode
But I cant. The only think I could think of would be :
create cursor myresult (empcode C(10), gross N(10,2), cycle N(5))
select empcode into cursor emps from mytable group by empcode
scan
select empcode, sum(gross) as GrossTotal ;
into cursor newtable ;
from mytable my ;
where my.cycle in (SELECT x.cycle ;
FROM (SELECT TOP 52 aux.cycle, RECNO();
from mytable as aux ;
where aux.empcode=emps.empcode ;
order by 2 desc) as x) ;
and my.empcode = emps.empcode
group by empcode ;
order by empcode
select myresult
append from dbf('mytable')
use in select('mytable')
select emps
endscan
But can't think of something with SQL only. You might also use an
INSERT INTO myresult SELECT (and here comes the select)
as the only instruction inside the scan. Don't know if it will work
being myresult a cursor (maybe dbf('mycursor')?).
Beware, haven't had the time to test the code, its coming from the top
of my head.
As for books, I have :
Learning SQL By Alan Beaulieu - Publ. O'Reilly
The Art of SQL by Stéphane Faroult and Peter Robson - Publ. O'Reilly
Sams Teach Yourself SQL in 24 Hours By Ronald R. Plew and Ryan K.
Stephens - Publ. Sams Publishing
SQL Bible by Alex Kriegel and Boris M. Trukhnov - Publ. Wiley
> Sytze
>
>
>
>> If you want the last 52 cycles (sequentially last) :
>>
>> select empcode ;
>> into cursor newtable ;
>> from mytable my ;
>> where my.cycle in (SELECT x.cycle ;
>> FROM (SELECT TOP 52 aux.cycle, RECNO();
>> from mytable as aux ;
>> order by 2 desc) as x) ;
>> order by empcode ;
>> group by empcode
>>
>>> Regards
>>> Sytze
>>>
>>>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
