How to get the last transaction record

[abbasaif]

Hi,

How can I get the last transaction from a table like this;

How can I add in this code

Sele dist <PCode> from a <table> into cursor xyz where <PCode> = 'CC002'

Thanks

abbasaif

 

[abbasaif]

Hi,

I got 50% solution using the following syntax:

SELECT pcode,MAX(Sodate) FROM pimain WHERE sodate > {31/12/2018} into cursor tmp, through which I get the last transaction, but
I need two more fields associate with it, like cifratej,cifraten.
If cifratej or cifraten are different it is showing multiple time if I add "GROUP BY", whereas, I only want the highlighted figure.

Thanks

Abbasaif

 

[Olaf Doschke]

You first detemine pcode and max(sodate) put that as a subquery to inner join the whole data, written out this is inverse, last step first

Select * from <table> as t1 inner join;
(select pcode, max(sodate) maxsodate from <table> group by pcode) as maxdates;
on t1.pcode=maxdates.pcode and t1.sodate = maxdates.maxsodate;
into cursor crsResult

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[abbasaif]

Thanks Mr.Olaf

Abbasaif

 

[Mike Lewis]

Just to demonstrate an alternative:

SELECT Icode, Cifratej, Cifraten, Pidate FROM MyTable  ;
  WHERE Pidate = (SELECT MAX(Pidate) FORM MyTable)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[abbasaif]

Thanks Mr.Mike

Abbasaif

 

[abbasaif]

Mr. Mike, applying your logic gives only the latest records as seen in image.
What about the earlier records. Means before {16/01/2019}. Actually I want the last transaction of all the codes.

Thanks

Abbasaif

 

[Mike Gagnon]

The dates are all the same.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Olaf Doschke]

Abbasaif, if you get what you want with my solution, you mark this thread as done by marking my answer as "Great post!" with the star in the right bottom.
Then others will not stop providing answers, but know you've reached at least a part of your goal and others finding this years later are also guided to what's a solution.

The samplke data you show in your first two posts does not only differ in showing more cases, in one you have icode in the other pcode, adapt my query as necessary in that aspect, please. If you have any questions left, you may ask them.

Mike Gagnon, yes the last image shows exactly that problem, the image of the second post shows the data and each icode has different max dates, illustrated by the highlighted rows, which are the ones wanted in the result. Mike Lewis query does only fetch all data to one max date, he likely started from the image in the first post, which only showed data of one pcode. And the latest image is not a third description of the problem, it just shows the problem with Mike Lewis' alternative solution as it gets all data of a max date, but the different codes have different max dates.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[fitedgar]

Try this, similar to Olaf's elegant answer:

SELECT icode, icode + DTOS(MAX(pidate)) as imaxcode ;
FROM Table1;
GROUP BY icode ;
INTO CURSOR ac_maxpi

SELECT icode, pidate,cfraj, cifraten ;
FROM table1 ;
WHERE icode + DTOS(MAX(pidate)) IN ;
(SELECT imaxcode FROM ac_maxpi) ;
INTO CURSOR ac_final

Edgar
Integrated Bar Code Systems, Inc.

 

[vgulielmus]

In VFP you can do it in threes steps, like this:

* first get the record number of each record
Sele RECNO() as myId,* from a mytable into cursor mycursor
* second get the last record number of each icode
SELECT max(myid) as myid,icode from mycursor group by 2 into cursor mycursormax
* finally get the result
select mycursor.* from mycursor,mycursormax where mycursor.myid = mycursormax.myid

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/