What's wrong with my DISTINCT

select distinct CONVERT (varchar(9),EMPLOYEE_ID) AS emp_id
, CONVERT(datetime,(CONVERT(varchar(8),ADMISSION_DTE))) AS Adm_date
, ADMISSION_CDE
, ADMIT_TYPE_CDE
, CONVERT(datetime,(CONVERT(varchar(8),DISC_DTE))) AS Dis_date
, DISC_CDE
, DISC_TYPE_CDE
from CurrentTable
WHERE [BranchID]='950' AND (DISC_DTE IS NULL OR (DISC_DTE > 20120630 AND DISC_DTE < 20130201))
order by emp_id , adm_date

SELECT
CONVERT (VARCHAR(9), EMPLOYEE_ID) AS emp_id,
MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_date,
ADMISSION_CDE,
ADMIT_TYPE_CDE,
CONVERT(DATETIME, (CONVERT(VARCHAR(8), DISC_DTE))) AS Dis_date,
DISC_CDE,
DISC_TYPE_CDE
FROM CurrentTable
WHERE [BranchID] = '950'
AND ( DISC_DTE IS NULL
OR ( DISC_DTE > 20120630
AND DISC_DTE < 20130201 ) )
GROUP BY
CONVERT (VARCHAR(9), EMPLOYEE_ID),
ADMISSION_CDE,
ADMIT_TYPE_CDE,
CONVERT(DATETIME, (CONVERT(VARCHAR(8), DISC_DTE))),
DISC_CDE,
DISC_TYPE_CDE
ORDER BY
emp_id,
adm_date

select CONVERT (varchar(9),EMPLOYEE_ID) AS emp_id
, CONVERT(datetime,(CONVERT(varchar(8),ADMISSION_DTE))) AS Adm_date
, ADMISSION_CDE
, ADMIT_TYPE_CDE
, CONVERT(datetime,(CONVERT(varchar(8),DISC_DTE))) AS Dis_date
, DISC_CDE
, DISC_TYPE_CDE
from CurrentTable ct1
WHERE [BranchID]='950' AND (DISC_DTE IS NULL OR (DISC_DTE > 20120630 AND DISC_DTE < 20130201))
and ct1.admission_dte = (SELECT MAX(ct2.admission_dte) from curenttable ct1 WHERE ct1.pkstuff = ct2.pkstuff)
order by emp_id , adm_date

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;

Lynn Pettis (2/5/2013)

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.

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;