Find missing dates

Problem: You wish to identify missing dates from a table which contains multiple objects
eg

Object,Date
1,Jan-01-2012
1,Jan-03-2012
2,Jan-02-2012
2,Jan-03-2012
2,Jan-04-2012

Solution:

You should have a calender table, for this example I will restrict the table to just 4 days

Date
Jan-01-2012
Jan-02-2012
Jan-03-2012
Jan-04-2012

The missing date output we want to achieve is

Object,Date
1,Jan-02-2012
1,Jan-04-2012
2,Jan-01-2012

a. Set up some data

declare @a table (f1 int, f2 datetime)
declare @b table (f2 datetime)

insert into @a VALUES (1,’2012-Jan-01′),(1,’2012-Jan-03′), (2,’2012-Jan-02′),(2,’2012-Jan-04′),(2,’2012-Jan-03′)
insert into @b VALUES (‘2012-Jan-01’),(‘2012-Jan-02’),(‘2012-Jan-03’),(‘2012-Jan-04’)

b. create a distinct list of objects

select * from
(select distinct a.f1
from @a a) a

c. add all dates from the calender table for each object

select * from
(select distinct a.f1
from @a a) a
cross join @b b

d. join on the table which contains your objects and dates

select * from
(select distinct a.f1
from @a a) a
cross join @b b
left join @a c on c.f1=a.f1 and c.f2 = b.f2

e. we only want the records where the c table is not matched

select * from
(select distinct a.f1
from @a a) a
cross join @b b
left join @a c on c.f1=a.f1 and c.f2 = b.f2
where c.f1 is null

f. tidy up the select

select a.f1,b.f2
from
(select distinct a.f1
from @a a) a
cross join @b b
left join @a c on c.f1=a.f1 and c.f2 = b.f2
where c.f1 is null