Get Missing Dates

Hi,
I have 2 tables, one a list of people, and the other a table of daily
diary entries for those people.
Now i need to get a list of all people and dates that don't have an entry.
I have created a calendar table in order to assist but i can't figure
out how to pull back the results as i need.
What i need is something along the lines of
PersonId | Date
--
1 | '2006-01-01'
1 | '2006-01-02'
1 | '2006-01-12'
2 | '2006-01-01'
2 | '2006-01-21'
etc... | etc...
Any help would be greatly appreciated.
Many thanks
MattSo we have some idea...
http://www.aspfaq.com/5006
"Matt Brailsford" <matt@.gradiation.co.uk> wrote in message
news:OkGE6nvQGHA.3972@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have 2 tables, one a list of people, and the other a table of daily
> diary entries for those people.
> Now i need to get a list of all people and dates that don't have an entry.
> I have created a calendar table in order to assist but i can't figure out
> how to pull back the results as i need.
> What i need is something along the lines of
> PersonId | Date
> --
> 1 | '2006-01-01'
> 1 | '2006-01-02'
> 1 | '2006-01-12'
> 2 | '2006-01-01'
> 2 | '2006-01-21'
> etc... | etc...
> Any help would be greatly appreciated.
> Many thanks
> Matt|||Try this:
declare @.Person table (personid int)
insert @.person values (1)
insert @.person values (2)
declare @.Date table (dt datetime)
insert @.Date values ('01 Jan 2006')
insert @.Date values ('02 Jan 2006')
insert @.Date values ('03 Jan 2006')
insert @.Date values ('04 Jan 2006')
insert @.Date values ('05 Jan 2006')
insert @.Date values ('06 Jan 2006')
insert @.Date values ('07 Jan 2006')
declare @.Diary table (personid int, dt datetime)
insert @.Diary values(1, '01 Jan 2006')
insert @.Diary values(1, '03 Jan 2006')
insert @.Diary values(1, '04 Jan 2006')
insert @.Diary values(2, '05 Jan 2006')
insert @.Diary values(2, '06 Jan 2006')
select p.personid, d.dt
from @.Person p
cross join @.Date d
left outer join @.Diary e
on p.personid = e.personid and d.dt = e.dt
where e.personid is null|||Excellent,
That looks exactly what i need.
I'll give it a try.
jeff.bolton@.citigatehudson.com wrote:
> Try this:
> declare @.Person table (personid int)
> insert @.person values (1)
> insert @.person values (2)
> declare @.Date table (dt datetime)
> insert @.Date values ('01 Jan 2006')
> insert @.Date values ('02 Jan 2006')
> insert @.Date values ('03 Jan 2006')
> insert @.Date values ('04 Jan 2006')
> insert @.Date values ('05 Jan 2006')
> insert @.Date values ('06 Jan 2006')
> insert @.Date values ('07 Jan 2006')
> declare @.Diary table (personid int, dt datetime)
> insert @.Diary values(1, '01 Jan 2006')
> insert @.Diary values(1, '03 Jan 2006')
> insert @.Diary values(1, '04 Jan 2006')
> insert @.Diary values(2, '05 Jan 2006')
> insert @.Diary values(2, '06 Jan 2006')
> select p.personid, d.dt
> from @.Person p
> cross join @.Date d
> left outer join @.Diary e
> on p.personid = e.personid and d.dt = e.dt
> where e.personid is null
>