select from table
Hi folk I have a table where amongst other fields, I have empcode C(10), gross N(10,2), cycle N(5) empcode is the employee code, gross is total pay, cycle is the pay number I want to: select empcode, sum(gross) from mytable order by empcode group by empcode into table newtable BUT I ONLY WANT THE LAST 52 CYCLES (Some may have 300 cycles) Some may only have (say) 30 cycles Is this feasible ? Regards Sytze ___ Post Messages to: ProFox@leafe.com 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.
RE: select from table
WAG Select empcode, (select sum(gross) from table1 sumtable where sumtable.empcode = table1.empcode top 52 order by cycle desc) as grosssum from table1 group by empcode Tracy > -Original Message- > From: Sytze de Boer > Sent: Wednesday, January 17, 2007 1:39 PM > > Hi folk > I have a table where amongst other fields, I have empcode > C(10), gross N(10,2), cycle N(5) empcode is the employee > code, gross is total pay, cycle is the pay number > > I want to: > select empcode, sum(gross) from mytable order by empcode > group by empcode into table newtable BUT I ONLY WANT THE LAST > 52 CYCLES (Some may have 300 cycles) Some may only have (say) > 30 cycles > > Is this feasible ? > > Regards > Sytze ___ Post Messages to: ProFox@leafe.com 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.
Re: select from table
My WAG would be you need to group on empcode & cycles in order to have the TOP clause give you what you want. Tracy Pearson wrote: > WAG > > Select empcode, (select sum(gross) from table1 sumtable where > sumtable.empcode = table1.empcode top 52 order by cycle desc) as grosssum > from table1 group by empcode > > Tracy > >> -Original Message- >> From: Sytze de Boer >> Sent: Wednesday, January 17, 2007 1:39 PM >> >> Hi folk >> I have a table where amongst other fields, I have empcode >> C(10), gross N(10,2), cycle N(5) empcode is the employee >> code, gross is total pay, cycle is the pay number >> >> I want to: >> select empcode, sum(gross) from mytable order by empcode >> group by empcode into table newtable BUT I ONLY WANT THE LAST >> 52 CYCLES (Some may have 300 cycles) Some may only have (say) >> 30 cycles >> >> Is this feasible ? >> >> Regards >> Sytze >> -- Richard Kaye Vice President Artfact/RFC Systems Voice: 617.219.1038 Fax: 617.219.1001 For the fastest response time, please send your support queries to: Technical Support - [EMAIL PROTECTED] Australian Support - [EMAIL PROTECTED] Internet Support - [EMAIL PROTECTED] All Other Requests - [EMAIL PROTECTED] - This message has been checked for viruses before sending. - ___ Post Messages to: ProFox@leafe.com 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.
Re: select from table
Sytze de Boer wrote: > Hi folk > I have a table where amongst other fields, I have empcode C(10), gross > N(10,2), cycle N(5) > empcode is the employee code, gross is total pay, cycle is the pay number > > I want to: > select empcode, sum(gross) from mytable order by empcode group by > empcode into table newtable > BUT I ONLY WANT THE LAST 52 CYCLES (Some may have 300 cycles) > Some may only have (say) 30 cycles > > Is this feasible ? > if you want the cycles with the biggest 52 numbers : select empcode ; into cursor newtable ; from mytable my ; where my.cycle in (SELECT TOP 52 aux.cycle ; from mytable as aux ; order by cycle desc) ; order by empcode ; group by empcode 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: ProFox@leafe.com 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.
Re: select from table
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 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: ProFox@leafe.com 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.
Re: select from table
You may want to have a look at hentzenwerke.com - Taming VFP SQL. On 1/18/07, Sytze de Boer <[EMAIL PROTECTED]> 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 > > 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: ProFox@leafe.com 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.
Re: select from table
Short but a great book. Helped a LOT when switching back and forth from MS SQL and VFP SQL - Original Message - From: "Grigore Dolghin" <[EMAIL PROTECTED]> To: profox@leafe.com Date: Thu, 18 Jan 2007 13:22:45 -0600 Subject: Re: select from table > You may want to have a look at hentzenwerke.com - Taming VFP SQL. > > ___ Post Messages to: ProFox@leafe.com 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.
Re: select from table
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: ProFox@leafe.com
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.
