Bhavbhuti Nathwani wrote:

>I have a list as in the below SQL. This SELECT generates a list based
>on cMSF value 'G' or 'R' and cDirPattern '2+2', '2+1', '3+1'. I want
>to limit this list to only such records which had cMSF 'R' in the last
>record in the dDt order (14 DESCENDING), but the last record may not
>be yesterday's date but a day or two back.
>
>Is this possible using SQL or would I have to tackle this
>programatically on the data itself.
>
>Please advise.
>
>
>SELECT 1 AS iOrder, tDaily.*
> FROM tDaily
> WHERE cMSF = 'G' AND cDirPattern = '2+2' AND dDt = :vp_dAsOfDt
>UNION ALL
>SELECT 2 AS iOrder, tDaily.*
> FROM tDaily
> WHERE cMSF = 'G' AND cDirPattern = '2+1' AND dDt = :vp_dAsOfDt
>UNION ALL
>SELECT 3 AS iOrder, tDaily.*
> FROM tDaily
> WHERE cMSF = 'G' AND cDirPattern = '3+1' AND dDt = :vp_dAsOfDt
> ORDER BY 1, 14 DESCENDING, 4, 5, 6
>
>

I think this ought to work:

SELECT 1 AS iOrder, tDaily.*
FROM tDaily
WHERE cMSF = 'G' AND cDirPattern = '2+2' AND dDt = :vp_dAsOfDt
and (select last cMSF from tDaily where dDt < :vp_dAsOfDt and cDirPattern = '2+2' order by dDt desc)
UNION ALL
SELECT 2 AS iOrder, tDaily.*
FROM tDaily
WHERE cMSF = 'G' AND cDirPattern = '2+1' AND dDt = :vp_dAsOfDt
and (select last cMSF from tDaily where dDt < :vp_dAsOfDt and cDirPattern = '2+1' order by dDt desc)
UNION ALL
SELECT 3 AS iOrder, tDaily.*
FROM tDaily
WHERE cMSF = 'G' AND cDirPattern = '3+1' AND dDt = :vp_dAsOfDt
and (select last cMSF from tDaily where dDt < :vp_dAsOfDt and cDirPattern = '3+1' order by dDt desc)

ORDER BY 1, 14 DESCENDING, 4, 5, 6

--
Best regards,
Jonathan Neve
_______________
CopyCat - advanced database replication
components for Delphi/C++Builder!
_______________________________________
Version 1.0 now available!
Web : http://www.microtec.fr/copycat