SQL Server : query to compare records in a single table and return those that don't match -

i using ssis query database , return records same query every night table in db date stamp. want able use ssrs / sql query compare records yesterday records returned query today , return don't match.

this include:

• new additions (new rows)
• new deletions (rows there yesterday , aren't here today)
• any columns may have changed between yesterday , today

an example of data in table follows:

servername    cpus    ram    disk    date   ==========    ====    ===    ====    ====   server1        1       2      20     8/8/2013   server2        2       4      40     8/8/2013   server3        2       4      40     8/8/2013   server1        1       2      20     9/8/2013   server3        2       6      40     9/8/2013   

query comparing differences return (or similar) - don't mind returning 3 tables cater additions / deletions / changes:

servername    cpus    ram    disk    date   ==========    ====    ===    ====    ====   server2        2       4      40     8/8/2013   null           null    null   null   9/8/2013   server3        2       4      40     8/8/2013   server3        2       6      40     9/8/2013   

any assistance appreciated!

this first post apologies if formatting messed up...

assuming there can no duplicate server names per date, try following:

with allservers (   select distinct servername groupingname   yourtable   date in (@yesterday, @today) ) select s.groupingname, x.* allservers s left join yourtable y on s.groupingname = y.servername , y.date = @yesterday left join yourtable t on s.groupingname = t.servername , t.date = @today cross apply (   select @yesterday groupingdate, y.*   union   select @today     groupingdate, t.* ) x exists (   select y.cpus, y.ram, y.disk   except   select t.cpus, t.ram, t.disk ) order s.groupingname, x.groupingdate; 

the output produced query doesn't match yours can adjusted in main select clause. main purpose of 2 columns added, groupingname , groupingdate, keep relevant rows , in specific order (yesterday's row followed today's).

you can play live demo of query at sql fiddle.