momba (2/5/2013)
RE: Lowell's post....results are still too high... when I try to follow your logic in the code I get lost in the syntax around "GROUP BY" so I started back at the basics. If I just do emp_id and adm_date the numbers are right...SELECT
CONVERT (VARCHAR(9), EMPLOYEE_ID) AS EMP_id,
MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_date
from CurrentTable
WHERE [BranchID] = '950'
AND ( DISC_DTE IS NULL
OR ( DISC_DTE > 20120630
AND DISC_DTE < 20130201 ) )
GROUP BY
EMPLOYEE_ID
.... but as soon as I add in additional fields the numbers get higher and higher. ???
I'm going to try the next example.
This:
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,
MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_date
FROM
CurrentTable
WHERE
[BranchID] = '950'
AND ( DISC_DTE IS NULL
OR ( DISC_DTE > 20120630
AND DISC_DTE < 20130201 ) )
)
SELECT
*
FROM
BaseData
WHERE
rn = 1;
Any columns you need added, ad to the SELECT in the CTE. You can also define which columns to select from the CTE instead of using the *, this way you can eliminate the column rn.