While Using Distinct still getting duplicate values?

Question

Using SQL Server 2000

How to avoid the duplicate values?

Query

SELECT DISTINCT 
    Modification.dbo.Reference.Dates AS DailyDate, 
    tmp_Cardevent2.PERSONID, 
    tmp_Cardevent2.empname, 
    tmp_cardevent2.cardno, 
    tmp_Cardevent2.titlecode, 
    tmp_Cardevent2.titlename, 
    tmp_Cardevent2.departname, 
    CASE 
      WHEN tmp_Cardevent2.CardEventDate = Modification.dbo.Reference.Dates 
        THEN tmp_Cardevent2.CardEventDate 
        ELSE '-----' 
    END AS EMPDATE, 
    CASE 
      WHEN tmp_Cardevent2.CardEventDate = Modification.dbo.Reference.Dates 
        THEN tmp_Cardevent2.Intime 
        ELSE '-----' 
    END AS INTIME
WHEN tmp_Cardevent2.CardEventDate = Modification.dbo.Reference.Dates 
            THEN tmp_Cardevent2.outtime 
            ELSE '-----' 
        END AS outtime 
    FROM tmp_Cardevent2 
    CROSS JOIN  Modification.dbo.Reference
    ORDER BY 
       PERSONID, DAILYDATE DESC

Output:

DailyDates, Personid, empname, cardno, titlecode, titlename, departname, empdate, intime, outtime

12/30/2008 A201 A Cherian 3201 018 Chief Air Traffic Service Assistant Air Traffic Services ----- ----- 

12/30/2008 A201 A Cherian 3201 018 Chief Air Traffic Service Assistant Air Traffic Services 20081230 07:51:31 15:54:38

12/30/2008 A201 A Cherian 3201 018 Chief Air Traffic Service Assistant Air Traffic Services 20081230 07:51:31 15:54:38

What problem in my query, why am getting duplicate values? How to avoid the duplicate values.

Please can any help to solve my problem.

Answer 1

Are these dates of type DateTime or just Date? If they include a time, this will be the reason why they are not equal. The time is just cut when the result is formatted.

Try to select only the date part of these values.

Answer 2

first off, your given result set:

DailyDates  Personid  empname    cardno  titlecode  titlename                            departname            empdate   intime    outtime
----------  --------  ---------  ------  ---------  -----------------------------------  --------------------  --------  --------  --------
12/30/2008  A201      A Cherian  3201    018        Chief Air Traffic Service Assistant  Air Traffic Services  -----     -----    
12/30/2008  A201      A Cherian  3201    018        Chief Air Traffic Service Assistant  Air Traffic Services  20081230  07:51:31  15:54:38
12/30/2008  A201      A Cherian  3201    018        Chief Air Traffic Service Assistant  Air Traffic Services  20081230  07:51:31  15:54:38

does not match your given query, there is no "outtime" column in the query:

SELECT DISTINCT 
    Modification.dbo.Reference.Dates AS DailyDate
        ,tmp_Cardevent2.PERSONID
        ,tmp_Cardevent2.empname
        ,tmp_cardevent2.cardno
        ,tmp_Cardevent2.titlecode
        ,tmp_Cardevent2.titlename
        ,tmp_Cardevent2.departname
        ,CASE 
             WHEN tmp_Cardevent2.CardEventDate = Modification.dbo.Reference.Dates THEN tmp_Cardevent2.CardEventDate 
             ELSE '-----'
         END AS EMPDATE
        ,CASE
             WHEN tmp_Cardevent2.CardEventDate = Modification.dbo.Reference.Dates THEN tmp_Cardevent2.Intime
             ELSE '-----' 
         END AS INTIME
    FROM tmp_Cardevent2
        CROSS JOIN Modification.dbo.Reference
    ORDER BY PERSONID, DAILYDATE DESC

Without knowing the table schemas and how they relate to each other, as well as the column datatypes of:

Modification.dbo.Reference.Dates
tmp_Cardevent2.CardEventDate
tmp_Cardevent2.Intime

it is hard to understand what is the true cause of the duplicates. However, based on the limited info inthe question, it is most likely that the formatting of one one the date and/or time columns is hiding an actual difference in values.

Answer 3

You could try wrapping another select distinct around that as a subquery, e.g.

select distinct * from (SELECT DISTINCT 
    Modification.dbo
    ...) q

Where q is required and names query as a pseudo-table.

Not really sure why the first distinct isn't working, perhaps the CASE is confusing its optimizer?