Find missing dates 2

[mlee1steph]

Hi Everyone,
I was wondering how I could date the following table:
ClientId
AttendDate
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? Thanks for any help.
Michael

 

[maswien]

try this

declare @dt1 as smalldatetime
declare @dt2 as smalldatetime
declare @clientID

create table #range(dt smalldatetime)

while (@dt1 <= @dt2)
begin
  insert into #range values(@dt1)
  set @dt1 = dateadd(dd, 1, @dt1)
end

select dt from #range
 where dt not in ( select AttendDate from myTable where ClientID = @clientID)

 

[jbenson001]

very clever maswein!

 

[bborissov]

Here one HUGE select w/o creatin temporary tables (table variable is just for testing, just replace it with your actual table name)
Period is almost endless. Play with @dBegin and @dEnd

DECLARE @dBegin datetime, @dEnd datetime
SET @dBegin = '20060101'
SET @dEnd   = '20060131'
DECLARE @Test TABLE (custId Int, VisitDate DateTime)
INSERT INTO @Test VALUES (1, '20060101')
INSERT INTO @Test VALUES (1, '20060105')
INSERT INTO @Test VALUES (1, '20060110')


SELECT Fulldates.* 
FROM (
SELECT Test.CustId, R.range_date
FROM (
select (@dBegin + 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 @dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd) 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

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[mlee1steph]

Hi maswein,
Thanks for the reply. This works great, but I was given an extra twist to this one. I need to get the dates for all the clients. I tried to modify the query like so:
declare @dt1 smalldatetime
Declare @dt2 smalldatetime

select @dt1 = '1/2/2005'
Select @dt2 = '7/20/2006'
create table #range1(dt smalldatetime)

while (@dt1 <= @dt2)
begin
insert into #range1 values(@dt1)
Set @dt1 = dateadd(dd,1,@dt1)
end
Select dt from #range1
where dt not in (SELECT Patients.PatientID, Patients.LastName, Patients.FirstName,
ATTENDANCE.AttendDate
FROM ATTENDANCE RIGHT JOIN Patients
ON ATTENDANCE.PatientID = Patients.PatientID
where Patients.Patientid = '10071')

But I get the following error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Do you have another way to do this. I'm looking into EXISTS to see if I can get it to work that way. Thanks again for the reply.
Michael

 

[mlee1steph]

Hi bborissov,
Thank you so much for the help. This appears to be working great. Could you please explain whats happening in the query. I see alot up sub queries but not sure why they where needed. I want to get a better understanding on the query because I have to add the Patient name (from the Patients table) into the result set. Thanks again the help.
Michael

 

[r937]

you need to cross join the patients to the dates before doing the LEFT OUTER JOIN to the data (which i prefer to the NOT EXISTS -- i don't why, i just do)

r937.com | rudy.ca

 

[bborissov]

This select:

select (@dBegin + 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 @dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd
ORDER BY range_date

build an derived table with all dates between your desired period (that way you don't need to create temporary tables or have ones in your DataBase). You could try it in QA and play with different period.
What it do:
select 0 b1 union select 1 b1 you have following result:

b1
--
0
1

select 0 b2 union select 2 b2 you have following result:

b2
--
0
2

with cross join you will get:
b1 b2
-----
0 0
0 2
1 0
1 2

etc (for all until 2048)
that way with:
select (@dBegin+b1+b2) AS range_date
you will have:
@dBegin + 0 + 0
@dBegin + 0 + 1
@dBegin + 1 + 0
@dBegin + 1 + 2
etc.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[mlee1steph]

Hi r937,
Thanks for the reply.
>you need to cross join the patients to the dates before >doing the LEFT OUTER JOIN to the data (which i prefer to >the NOT EXISTS -- i don't why, i just do)
I didn't see any Left Outer join in the above query. Could you give an example of what you ment. Thank so much.
Michael

 

[r937]

look for WHERE NOT IN ( subselect )

that can be done with a LEFT OUTER JOIN

i would write

FROM patients
CROSS
JOIN range-of-dates
LEFT OUTER
JOIN data

WHERE data.somekey IS NULL

r937.com | rudy.ca

 

[mlee1steph]

Hi bborissov,
I would like to thank everyone for the help. I did end up using your solution with a few slight changes (only cause I could not get the join with the declared table to work. So I added a table to hold the data. I ended up with the following:
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
Insert into @Test Select PatientID, AttendDate from Attendance
where AttendDate between @FromDate and @ToDate
end

if @PatientId > -1
begin
Insert into @Test Select PatientID, AttendDate from Attendance
where AttendDate between @FromDate and @ToDate
and PatientId = @PatientId

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

I want to thank you all again. Take care.
Michael

 

[mlee1steph]

Hi bborissov,
I ran into one little issue, I hope you can help. If I enter the same date for both the Begin and End dates I will not get any results back even though I know there is data for that date. I have a feeling that it may have something to with this line:
where @FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @ToDate)

Do you have any suggestions for this situation. By the way, why is it adding all the b's to @FromDate. Tank for any suggestions.
Michael