How to get the missing dates

Hi Everyone,
I was wondering how I could get the missing dates. I have the following tabl
e:
--Attendance--
ClientId
AttendDate
--Patients--
PatientId
LastName
FirstName
ect...
This table holds dates that our client has been in our office. But I need to
find out what dates the clinet was not there given a date range. What is the
best way to do this? I have the following query. It will work for only one
client. The problem is that I may need to do it for all patients or just one
patient.
CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
@.PatientId varchar(20),
@.ToDate smalldatetime,
@.FromDate smalldatetime
AS
create table #range1(dt smalldatetime)
while (@.ToDate <= @.FromDate)
begin
insert into #range1 values(@.ToDate)
Set @.ToDate = dateadd(dd,1,@.ToDate)
end
Select dt from #range1
where dt not in (Select AttendDate from Attendance
where Patientid = @.PatientId)
drop table #range1
GO
The also only returns the dates themself, while I still need other data to
be included in the query like the first and last name of the Patient. I
played with the query some but could not get the results I was looking for.
Thanks for any comments.
MichaelYou may benefit from a calendar table. Try this resource:
http://www.aspfaq.com/show.asp?id=2519
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:81388BE6-50B5-459A-8919-C4C949097FB1@.microsoft.com...
> Hi Everyone,
> I was wondering how I could get the missing dates. I have the following
> table:
> --Attendance--
> ClientId
> AttendDate
> --Patients--
> PatientId
> LastName
> FirstName
> ect...
> This table holds dates that our client has been in our office. But I need
> to
> find out what dates the clinet was not there given a date range. What is
> the
> best way to do this? I have the following query. It will work for only one
> client. The problem is that I may need to do it for all patients or just
> one
> patient.
> CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
> @.PatientId varchar(20),
> @.ToDate smalldatetime,
> @.FromDate smalldatetime
> AS
> create table #range1(dt smalldatetime)
> while (@.ToDate <= @.FromDate)
> begin
> insert into #range1 values(@.ToDate)
> Set @.ToDate = dateadd(dd,1,@.ToDate)
> end
> Select dt from #range1
> where dt not in (Select AttendDate from Attendance
> where Patientid = @.PatientId)
> drop table #range1
> GO
> The also only returns the dates themself, while I still need other data to
> be included in the query like the first and last name of the Patient. I
> played with the query some but could not get the results I was looking
> for.
> Thanks for any comments.
> Michael
>sql