Home Forums SQL Server 2012 SQL Server 2012 - T-SQL What's wrong with my DISTINCT RE: What's wrong with my DISTINCT

  • February 5, 2013 at 12:04 pm

    #1584547

    momba (2/5/2013)

    I'm trying to pull out the last record for each employee, i.e. if they appear in the table two or more times, I just want the record attached to the later adm_date (which should result in just 6474 records).

    if you want the latest date, you want to use GROUP BY., and actually use the MAX*() function on the date so you get that last(latest/max 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

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!