Hi
I have a table which stores every date a case is posted. Now with the below query I was able to pick out the latest stage of a case based on the date.
Code:
SELECT *
FROM CaseStagesTran AS a
WHERE (((Exists (SELECT 1 FROM CaseStagesTran b 
                     WHERE b.caseID = a.caseID
                         AND b.caseDate > a.caseDate 
      ))=False));
As it happens when a case is posted for some purpose say, 'arguments' and the case is disposed off the same day (i.e. decreed/dismissed) the table would continue to hold both the data and date of transaction of both the events arguments & decree would be the same.
There is a case stage master and tran which hold the casestageID
So how do I go about to do my desired task of retrieving the last record of each case and exclude the resultset it if it has certain (31,55,65,68,70) caseStageID
presently my query is as below but if a "case" has the same date then it picks up only the 1st record it encounters (obviously) so Iam of the view some query must run deeper if the dates are same for a caseID and determine if it falls in any of the exception caseStageIDs (eg 31,55,65,68,70) if so exclude it for the query results

Iam of opinion we make a query to retrieve latest records of each case and group them by caseID. Then have some code to check if there exist the excluded list of caseStageIDs (31,55,65,68,70) for each particular case, if its true then that record will not form part of the query result..is it ok.

The table data looks like this....
CaseStageTran
Code:
caseID   stageID   stageDate
1            21           1/10/10    
1            32           5/10/10
2            45           29/9/10
3            12           5/10/10
2            10           5/10/10
1            31           5/10/10
2            70           29/9/10
so how to exclude rec having caseid 1, 2 as it falls within the exclusions having caseStageID 31,55,65,68,70

Any suggestion is welcome