momba (2/5/2013)
:0( Now it says I'm missing the "GROUP BY" clause... (still working on it)...
No group by needed., forgot to take off the MAX.
WITH BaseData AS (
SELECT
rn = ROW_NUMBER() OVER (PARTITION BY EMPLOYEE_ID ORDER BY ADMISSION_DTE DESC),
CONVERT (VARCHAR(9), EMPLOYEE_ID) AS EMP_id,
ADMISSION_DTE
FROM
CurrentTable
WHERE
[BranchID] = '950'
AND ( DISC_DTE IS NULL
OR ( DISC_DTE > 20120630
AND DISC_DTE < 20130201 ) )
)
SELECT
*
FROM
BaseData
WHERE
rn = 1;