Hi all

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