Hi Anurag,

>In my application I am storing the visits made to customer in a visits file of the following format -
>
>customercode C 6
>visitdate D 8
>visitstatus C 20
>visitremarks M
>
>I need an sql to create a table with the latest visit along with the previous date (the one just before that) i.e. somthing like
>
>customercode
>lastvisitdate
>lastvisitstatus
>previousvisitdate
>previousvisitstatus
>

This example assumes there is only one visit per date. Otherwise, I'd add a unique VisitNbr.
You could then do something like this:

** Find most recent visit
SELECT CustomerCode, MAX(VisitDate) ;
	FROM Visits ;
	GROUP BY 1 ;
	INTO CURSOR csrMostRecent NOFILTER
	
** find next-most recent visit
SELECT CustomerCode, MAX(VisitDate) AS Max_VisitDate ;
	FROM Visits ;
	WHERE CustomerCode + dtos(Max_VisitDate) NOT IN (SELECT CustomerCode + dtos(VisitDate) FROM Visits) ;
	GROUP BY 1 ;
	INTO CURSOR csrNextMostRecent NOFILTER

** Get status for most recent visit
SELECT csrMostRecent.CustomerCode, ;
		csrMostRecentMax_VisitDate AS LastVisitDate, ;
		Visits.VisitStatus AS LastVisitStatus ;
	FROM csrMostRecent INNER JOIN Visits ;
		ON csrMostRecent.CustomerCode = Visits.CustomerCode ;
		AND csrMostRecent.Max_VisitDate = Visits.VisitDate ;
	INTO CURSOR csrMostRecent NOFILTER

** Get status for next-most recent visit
SELECT csrNextMostRecent.CustomerCode, ;
		csrNextMostRecentMax_VisitDate AS PreviousVisitDate, ;
		Visits.VisitStatus AS PreviousVisitStatus ;
	FROM csrNextMostRecent INNER JOIN Visits ;
		ON csrNextMostRecent.CustomerCode = Visits.CustomerCode ;
		AND csrNextMostRecent.Max_VisitDate = Visits.VisitDate ;
	INTO CURSOR csrNextMostRecent NOFILTER

SELECT csrMostRecent.*, csrNextMostRecent.PreviousVisitDate, csrNextMostRecent.PreviousVisitStatus ;
	FROM csrMostRecent ;
		LEFT OUTER JOIN csrNextMostRecent ;
			ON csrMostRecent.CustomerCode = csrNextMostRecent.CustomerCode ;
	INTO TABLE RecentVists

HTH,