How can I identify record differences between two tables?

Question

I have two queries. The first query:

select in_gentime
from in_time_temp
where cardnumber = 'MCL1570'
order by in_gentime

Output is:

2011-10-11 08:06:00.000
2011-10-12 08:35:00.000
2011-10-13 08:21:00.000
2011-10-15 08:21:00.000
2011-10-16 08:21:00.000
2011-10-17 08:21:00.000
2011-10-18 08:21:00.000
2011-10-19 08:21:00.000
2011-10-20 08:21:00.000
2011-10-21 08:21:00.000
2011-10-22 08:21:00.000
2011-10-24 08:21:00.000
2011-10-25 08:21:00.000
2011-10-26 09:00:00.000
2011-10-27 09:00:00.000
2011-10-28 09:00:00.000
2011-10-29 09:00:00.000
2011-10-31 09:00:00.000

The second query:

select out_gentime
from out_time_temp
where cardnumber = 'MCL1570'
order by out_gentime

Output is:

2011-10-11 22:02:00.000
2011-10-12 21:14:00.000
2011-10-14 21:59:00.000
2011-10-15 21:59:00.000
2011-10-16 21:59:00.000
2011-10-17 21:59:00.000
2011-10-18 21:59:00.000
2011-10-19 21:59:00.000
2011-10-20 21:59:00.000
2011-10-21 21:59:00.000
2011-10-22 21:59:00.000
2011-10-24 21:59:00.000
2011-10-25 21:59:00.000
2011-10-26 18:15:00.000
2011-10-27 18:15:00.000
2011-10-28 18:15:00.000
2011-10-29 23:15:00.000
2011-10-31 22:15:00.000

I need to identify records that have DATE values that appear in one table but not the other. I want to ignore the specific TIME. For example, I want to only return these 2 records:

2011-10-13 08:21:00.000
2011-10-14 21:59:00.000 

How can I write a query to do this?

Answer 1

If you use a FULL OUTER JOIN, you can join together the list of dates and then just pick the records where one or the other is NULL:

SELECT COALESCE(i.in_gentime, o.out_gentime)  -- pick first non-null value
FROM in_time_temp i
FULL OUTER JOIN out_time_temp o 
    ON  CAST(CONVERT(varchar(8), i.in_gentime,  112) AS datetime) = 
        CAST(CONVERT(varchar(8), o.out_gentime, 112) AS datetime)
    AND i.cardnumber = o.cardnumber
WHERE (i.in_gentime is null or o.out_gentime IS NULL)
AND   (i.cardnumber = 'MCL1750' OR o.cardnumber = 'MCL1750')

Update. Nathan, here is the full test code, I'm not sure why you are not getting results, but this returns 4 records when I run it:

CREATE TABLE in_time_temp  
(
    in_gentime   datetime,
    cardnumber   nvarchar(50)
)
insert into in_time_temp values('2011-10-11 08:06:00.000', 'MCL1750')
insert into in_time_temp values('2011-10-12 08:35:00.000', 'MCL1750')
insert into in_time_temp values('2011-10-13 08:21:00.000', 'MCL1750')
insert into in_time_temp values('2011-10-15 08:21:00.000', 'MCL1750')
insert into in_time_temp values('2011-10-16 08:21:00.000', 'MCL1750')
insert into in_time_temp values('2011-10-17 08:21:00.000', 'MCL1750')


CREATE TABLE out_time_temp
(
    out_gentime    datetime,
    cardnumber   nvarchar(50)
)
insert into out_time_temp values('2011-10-11 22:02:00.000', 'MCL1750')
insert into out_time_temp values('2011-10-12 21:14:00.000', 'MCL1750')
insert into out_time_temp values('2011-10-14 21:59:00.000', 'MCL1750')
insert into out_time_temp values('2011-10-15 21:59:00.000', 'MCL1750')
insert into out_time_temp values('2011-10-16 21:59:00.000', 'MCL1750')
insert into out_time_temp values('2011-10-17 21:59:00.000', 'MCL1750')
insert into out_time_temp values('2011-10-18 21:59:00.000', 'MCL1750')
insert into out_time_temp values('2011-10-19 21:59:00.000', 'MCL1750')


SELECT COALESCE(i.in_gentime, o.out_gentime)  -- pick first non-null value
FROM in_time_temp i
FULL OUTER JOIN out_time_temp o 
    ON  CAST(CONVERT(varchar(8), i.in_gentime,  112) AS datetime) = 
        CAST(CONVERT(varchar(8), o.out_gentime, 112) AS datetime)
    AND i.cardnumber = o.cardnumber
WHERE (i.in_gentime is null or o.out_gentime IS NULL)
AND   (i.cardnumber = 'MCL1750' OR o.cardnumber = 'MCL1750')

And the output is:

2011-10-13 08:21:00.000
2011-10-14 21:59:00.000
2011-10-18 21:59:00.000
2011-10-19 21:59:00.000

Answer 2

select in_gentime
from in_time_temp
where cardnumber = 'MCL1570' and
      dateadd(day, datediff(day, 0, in_gentime), 0) not in
        (select dateadd(day, datediff(day, 0, out_gentime), 0)
         from out_time_temp 
         where cardnumber = 'MCL1570')
union all         
select out_gentime
from out_time_temp
where  cardnumber = 'MCL1570' and
      dateadd(day, datediff(day, 0, out_gentime), 0) not in
        (select dateadd(day, datediff(day, 0, in_gentime), 0)
         from in_time_temp 
         where cardnumber = 'MCL1570')

Answer 3

This should provide you with the times that are in one of the two tables:

SELECT CASE
          WHEN in_gentime IS NOT NULL THEN in_gentime
          ELSE out_gentime
       END AS THETIME
FROM in_time_temp AS i
FULL OUTER JOIN out_time_temp AS out ON i.cardnumber = out.cardnumber
   AND i.in_gentime = out.out_gentime
WHERE i.in_gentime IS NULL
   OR out.i_gentime IS NULL
order by in_gentime

When you want to only reference the DATE and not TIME portion, that answer is here (much like what JohnD has provided as well, biggest difference is my use of CASE and his (probably more efficient) use of COALESCE)

SELECT CASE
          WHEN i.in_gentime IS NOT NULL THEN i.in_gentime
          ELSE o.out_gentime
       END AS THETIME
FROM in_time_temp AS i
FULL OUTER JOIN out_time_temp AS o ON i.cardnumber = o.cardnumber
   AND CAST(CONVERT(VARCHAR(8), i.in_gentime, 112) AS DATETIME) =
      CAST(CONVERT(VARCHAR(8), o.out_gentime, 112) AS DATETIME)
WHERE i.in_gentime IS NULL
   OR o.out_gentime IS NULL
order by i.in_gentime

Answer 4

something like this...

select in_gentime as THETIME from in_time_temp where cardnumber = 'MCL1570' 
 and in_gentime not in 
(select out_gentime from out_time_temp where cardnumber = 'MCL1570')
union
select out_gentime  as THETIME from out_time_temp where cardnumber = 'MCL1570'
 and out_gentime not in 
(select in_gentime as THETIME from in_time_temp where cardnumber = 'MCL1570' )
order by THETIME

This should give you all the unique items accross the two tables.