Finding Missing Dates Continued

[mlee1steph]

Hi Everyone,
I'm having a issue with a query I got help here on (I also what to thank bborissov again for the help). Here is what I need. We need to create a query that returns the name, id and dates that the patients have missed (did not showed up). The result must be based on a date range and this is where I run into an issue. If I enter a date range like 7/1/2006 to 7/15/2006 everything works fine. But if I enter a range of 7/5/2006 to 7/5/2006 and dont get any data back. It does not matter what PatientId I use. Here is the stored proc:
CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
@PatientId varchar(20),
@FromDate smalldatetime,
@ToDate smalldatetime
AS
SET NOCOUNT ON

DECLARE @Test TABLE (custId varchar(20), VisitDate DateTime)

if @PatientId = -1
begin
if @FromDate = @ToDate
begin
Insert into @Test Select PatientID, AttendDate from Attendance
where AttendDate = @ToDate
Print 'First Set'
end
else
begin
Insert into @Test Select PatientID, AttendDate from Attendance
where AttendDate between @FromDate and @ToDate
Print 'Second Set'
end
end

if @PatientId > -1
begin
if @FromDate = @ToDate
begin
Insert into @Test Select PatientID, AttendDate from Attendance
where AttendDate = @ToDate and PatientId = @PatientId
Print 'Third Set'
end
else
begin
Insert into @Test Select PatientID, AttendDate from Attendance
where AttendDate between @FromDate and @ToDate
and PatientId = @PatientId
Print 'Last Set'
end
end


DECLARE @DateResults TABLE (PatientId varchar(20), AttendDate DateTime)

Delete from Range

Insert into Range

SELECT Fulldates.*
FROM (
SELECT Test.CustId, R.range_date
FROM (
select (@FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
from (select 0 b1 union select 1 b1) t1
cross join (select 0 b2 union select 2 b2) t2
cross join (select 0 b3 union select 4 b3) t3
cross join (select 0 b4 union select 8 b4) t4
cross join (select 0 b5 union select 16 b5) t5
cross join (select 0 b6 union select 32 b6) t6
cross join (select 0 b7 union select 64 b7) t7
cross join (select 0 b8 union select 128 b8) t8
cross join (select 0 b9 union select 256 b9) t9
cross join (select 0 b10 union select 512 b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @ToDate) r
FULL join (SELECT DISTINCT CustId FROM @Test) Test ON 1 = 1
) FullDates
LEFT JOIN @Test Test ON FullDates.CustId = Test.CustId AND FullDates.Range_Date = Test.VisitDate
WHERE Test.CustId IS NULL
ORDER BY FullDates.CustId, FullDates.range_date


SELECT Distinct PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName, Range.AttendDate
FROM PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId
ORDER BY Range.AttendDate, PATIENTS.PatientID
GO

THe partial table structure is as follows:
***Patients***
PatientId - Key
Lastname
Firstname
ect...
***Attendnace***
PatientId - Key
AttendDate - Key
ect..
***Range*** Only used to temp store results
PatientId
AttendDate


Thanks for any help.
Michael

 

[bborissov]

Michael,
Change
FULL join (SELECT DISTINCT CustId FROM @Test) Test ON 1 = 1
to
FULL JOIN (SELECT DISTINCT PatientID AS CustId FROM Attendance) Test ON 1 = 1

Or if you have table with patients:


FULL JOIN (SELECT DISTINCT PatientID AS CustId FROM Patients) Test ON 1 = 1


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[mlee1steph]

Hi bborissov,
Thanks again for the reply. That worked with alittle modifications.
I have what may seem like a simple question, but I can't seem to quit get it, if its possible. Is there a way to break down a query in a If block or Case statement. For example, taking only a portion of the query:
where @FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @ToDate) r
FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance where Patientid = @PatientId) Test ON 1 = 1
) FullDates
LEFT JOIN @Test Test ON FullDates.CustId = Test.CustId AND FullDates.Range_Date = Test.VisitDate
WHERE Test.CustId IS NULL
ORDER BY FullDates.CustId, FullDates.range_date

Since I pass a -1 to indicate to return all Patients I was using a If statement with the whole query, but to make the Stored procedure shorter can't I do something like:
SELECT DISTINCT PatientId as CustId FROM Attendance
If Not @Patientid = '-1'
Where PatientId = @PatientId

In other words I'm trying to make the where statement dynamic Thanks for any suggestions?
By the way, What book or source would you suggest for Transact SQL? I've been using it for several years, but this is the first time I was asked to create a query like this. I had a good book once but it burned up in the World Trade center and have not seen a good one since. Thanks again.
Michael

 

[bborissov]

SELECT DISTINCT PatientId as CustId 
       FROM Attendance
WHERE (@Patientid = '-1' OR PatientId = @PatientId)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.