How to check if a list have any different value

Question

Hello, I have a table like the following:

(date1, date2, date3, date4, date5)

and I want to check if ANY of these dates is different than any other. The trivial solution is:

WHERE date1 <> date2
   OR date1 <> date3
   OR date1 <> date4
   OR date1 <> date5
   OR date2 <> date3
   OR date2 <> date4
   OR date2 <> date5
   OR date3 <> date4
   OR date3 <> date5
   OR date4 <> date5

Any nontrivial solutions?

Answer 1

group by each value, compare the grouped by count to the original count

Answer 2

If you are using SQL Server 2005+, you could do something like:

With Dates As
    (
    Select PK, 'Date1' As DateType, Date1 As [Date] From Table
    Union All Select PK, 'Date2', Date2 From Table
    Union All Select PK, 'Date3', Date3 From Table
    Union All Select PK, 'Date4', Date4 From Table
    Union All Select PK, 'Date5', Date5 From Table
    )
Select D.PK, D.DateType, D.[Date]
From Dates As D
Where Exists    (
                Select 1
                From Dates As D1
                Where D1.PK = D.PK
                    And D1.[Date] <> D.[Date]
                )

Answer 3

if the table has a primary key, I guess this is not trivial.

select key, "There are duplicates"
from
(
    select key,date1 from table
    union all
    select key,date2 from table
    union all
    select key,date3 from table
    union all
    select key,date4 from table
    union all
    select key,date5 from table
) as aa
group by
  key, date1
having 
  count(*) > 1

Answer 4

Just as an aside, your trivial case can really be simplified to just

WHERE date1 <> date2
   OR date1 <> date3
   OR date1 <> date4
   OR date1 <> date5

Using DeMorgan's Law, this is the same as

WHERE NOT(date1 = date2
      AND date1 = date3
      AND date1 = date4
      AND date1 = date5)

and then by the transitive property of the equality relation, we'd know that if date1 is equal to the other 4 values, then all 5 are equal to each other.

Answer 5

This was easier for me to picture with an example. This works, but I am not sure if I have overcomplicated it.

CREATE TABLE #Dates( ID int, date1 datetime, date2 datetime, date3 datetime, date4 datetime )
INSERT INTO #Dates VALUES( 1, '1 Jan 2008', '2 Feb 2979', '8 Nov 1967', '31 Dec 2001' ) 
INSERT INTO #Dates VALUES( 2, '1 Jan 2008', '1 Jan 2008', '1 Jan 2008', '1 Jan 2008' ) 
INSERT INTO #Dates VALUES( 3, '1 Jan 2008', '1 Jan 2008', '1 Jan 2008', '31 Jan 2008' ) 
INSERT INTO #Dates VALUES( 4, '1 Jan 2008', '1 Jan 2008', '31 Jan 2008', '1 Jan 2008' ) 

-- look at example data - note only row 2 has all 4 dates the same
SELECT * FROM #Dates

-- return rows where the dates are not all the same 
SELECT ID as RowsWithDatesNotAllTheSame
FROM 
    (
    SELECT ID, Date
    FROM 
        (
        SELECT ID, DateCol, Date
        FROM 
            (SELECT ID, date1, date2, date3, date4 
            FROM #Dates) p 

        UNPIVOT
            ( Date FOR DateCol IN
                (date1, date2, date3, date4)
        ) AS unpvt
        ) x
    GROUP BY ID, Date
    ) y 
GROUP BY ID
HAVING count(*) > 1