Sub Select

[goofaholix]

I have a selection formula to produce a result set for a report which I feel could be done in a more elegant way but the syntax escapes me. I've done it with SQL Server Views but it won't necessarily be done with Views as a final product.

The problem is that I need to select the most recent row, this is determined by the latest date in the VACANCY_PROGRESS.DATE_STARTED field, and if more than one row have the same date the highest SELECTION_STAGE.SEQ_NO determines between them.

So I've converted the date to yyyymmdd format and added the SEQ_NO to it to get the latest row. Then I've attached another view that has determined the highest value for each Applicant-Vacancy combination using a group by to filter out the older rows.

Here is the main select:

SELECT dbo.APPLICANT.APP_NO, dbo.APPLICANT.SALUTATION, dbo.APPLICANT.INTERNAL_EXTERNAL, dbo.VACANCY.VACANCY_NO,
dbo.VACANCY.JOB_TITLE, dbo.VACANCY.ORG_UNIT_CODE, dbo.VACANCY.VACANCY_DATE, dbo.SELECTION_STAGE.DESCRIPTION,
dbo.VACANCY_PROGRESS.DATE_ENDED, dbo.SELECTION_STAGE.SEQ_NO, CONVERT(varchar(19), dbo.VACANCY_PROGRESS.DATE_STARTED, 112)
+ dbo.SELECTION_STAGE.SEQ_NO AS RANK, dbo.Rpt_Applicant_Status_Subselect.HIGH_RANK, dbo.APPLICATION.DATE_OF_APPLICATION,
dbo.APPLICATION.APPLICATION_STATUS, dbo.VACANCY_PROGRESS.DATE_STARTED
FROM dbo.APPLICANT INNER JOIN
dbo.APPLICATION ON dbo.APPLICANT.APP_NO = dbo.APPLICATION.APP_NO INNER JOIN
dbo.VACANCY ON dbo.APPLICATION.VACANCY_NO = dbo.VACANCY.VACANCY_NO INNER JOIN
dbo.VACANCY_PROGRESS ON dbo.VACANCY.VACANCY_NO = dbo.VACANCY_PROGRESS.VACANCY_NO INNER JOIN
dbo.SELECTION_STAGE ON dbo.VACANCY_PROGRESS.PLAN_NAME = dbo.SELECTION_STAGE.PLAN_NAME AND
dbo.VACANCY_PROGRESS.SELECTION_STAGE = dbo.SELECTION_STAGE.SELECTION_STAGE INNER JOIN
dbo.Rpt_Applicant_Status_Subselect ON dbo.APPLICANT.APP_NO = dbo.Rpt_Applicant_Status_Subselect.APP_NO AND
dbo.VACANCY_PROGRESS.VACANCY_NO = dbo.Rpt_Applicant_Status_Subselect.VACANCY_NO

Here is the subselect View Rpt_Applicant_Status_Subselect:

SELECT dbo.APPLICANT.APP_NO, dbo.VACANCY.VACANCY_NO, MAX(CONVERT(varchar(19), dbo.VACANCY_PROGRESS.DATE_STARTED, 112)
+ dbo.SELECTION_STAGE.SEQ_NO) AS HIGH_RANK
FROM dbo.APPLICANT INNER JOIN
dbo.APPLICATION ON dbo.APPLICANT.APP_NO = dbo.APPLICATION.APP_NO INNER JOIN
dbo.VACANCY ON dbo.APPLICATION.VACANCY_NO = dbo.VACANCY.VACANCY_NO INNER JOIN
dbo.VACANCY_PROGRESS ON dbo.VACANCY.VACANCY_NO = dbo.VACANCY_PROGRESS.VACANCY_NO INNER JOIN
dbo.SELECTION_STAGE ON dbo.VACANCY_PROGRESS.PLAN_NAME = dbo.SELECTION_STAGE.PLAN_NAME AND
dbo.VACANCY_PROGRESS.SELECTION_STAGE = dbo.SELECTION_STAGE.SELECTION_STAGE
GROUP BY dbo.APPLICANT.APP_NO, dbo.VACANCY.VACANCY_NO

Is there a better way?

Thanks.

Bruce