Help with addition to Missing date query

Hi Everyone,
I hope someone can help out some. I have a report that displays the days our
clients missed there sessions. That was working great, and I've got some new
requirements now for the report. They needed to store the number of days the
client is suppose to attend, so I created a field called ClientSchedule that
stores a value 1-7 (days as week). The thing that has got me now is that they
also want the report to represent this new field. So the report needs to not
include the client in the report if they have attended for there scheduled
days for that week. But the kicker is that the report is based on a date
range and that range is variable, it could be set to one week, two weeks, 1
1/2 weeks, ect.. I have a query that partially works, but I could not figure
out how to work out the missed days within a week with a variable date range.
Somehow it needs to compare the number of days the client is scheduled
(ClientSchedule) with the number of days attended for a week, if days
attended is lower than ClientSchedule for that week, include the missed days
in the report(including all weeks that may be part of the Date range the user
selected).
The base query I have is as follows:
CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
@.PatientId varchar(20),
@.CounselorId int,
@.FromDate smalldatetime,
@.ToDate smalldatetime
AS
SET NOCOUNT ON
DECLARE @.Test TABLE (custId varchar(20), VisitDate DateTime)
Delete from RangeTest
if @.PatientId = -1
begin
if @.FromDate = @.ToDate
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate = @.ToDate
Print 'First Set'
end
else
begin
Insert into RangeTest 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 RangeTest Select PatientID, AttendDate from Attendance
where AttendDate = @.ToDate and PatientId = @.PatientId
Print 'Third Set'
end
else
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate between @.FromDate and @.ToDate
and PatientId = @.PatientId
Print 'Last Set'
end
end
Delete from Range
if @.PatientId = -1
begin
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 PatientId as CustId FROM Attendance) Test ON 1
= 1
) FullDates
LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
FullDates.Range_Date = Test.VisitDate
WHERE Test.CustId IS NULL
ORDER BY FullDates.CustId, FullDates.range_date
end
if @.PatientId > -1
begin
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 PatientId as CustId FROM Attendance where
Patientid = @.PatientId) Test ON 1 = 1
) FullDates
LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
FullDates.Range_Date = Test.VisitDate
WHERE Test.CustId IS NULL
ORDER BY FullDates.CustId, FullDates.range_date
end
if @.CounselorId = -1
begin
SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
PATIENTS.FirstName as pName,
Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
COUNSELORS.C_LastName,
dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID = dbo.nf_GetLastDayVisited.PatientId
where PATIENTS.ActiveClient = 1
and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
ORDER BY pName
end
if @.CounselorId <> -1
begin
SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
PATIENTS.FirstName as pName,
Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
COUNSELORS.C_LastName,
dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID = dbo.nf_GetLastDayVisited.PatientId
Where COUNSELORS.C_ID = @.CounselorId and PATIENTS.ActiveClient = 1
and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
ORDER BY pName
end
GO
I have worked on it for several hours and not sure where to take it from
here. Do I have to start to use a curser, or is there a way to do this with
just a modification to the query I have so far. Thanks for any suggestions.
MichaelHi Michael
Have you thought of using a calendar table for this ?
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
John
"Michael" wrote:
> Hi Everyone,
> I hope someone can help out some. I have a report that displays the days our
> clients missed there sessions. That was working great, and I've got some new
> requirements now for the report. They needed to store the number of days the
> client is suppose to attend, so I created a field called ClientSchedule that
> stores a value 1-7 (days as week). The thing that has got me now is that they
> also want the report to represent this new field. So the report needs to not
> include the client in the report if they have attended for there scheduled
> days for that week. But the kicker is that the report is based on a date
> range and that range is variable, it could be set to one week, two weeks, 1
> 1/2 weeks, ect.. I have a query that partially works, but I could not figure
> out how to work out the missed days within a week with a variable date range.
> Somehow it needs to compare the number of days the client is scheduled
> (ClientSchedule) with the number of days attended for a week, if days
> attended is lower than ClientSchedule for that week, include the missed days
> in the report(including all weeks that may be part of the Date range the user
> selected).
> The base query I have is as follows:
> CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
> @.PatientId varchar(20),
> @.CounselorId int,
> @.FromDate smalldatetime,
> @.ToDate smalldatetime
> AS
> SET NOCOUNT ON
> DECLARE @.Test TABLE (custId varchar(20), VisitDate DateTime)
> Delete from RangeTest
> if @.PatientId = -1
> begin
> if @.FromDate = @.ToDate
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate = @.ToDate
> Print 'First Set'
> end
> else
> begin
> Insert into RangeTest 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 RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate = @.ToDate and PatientId = @.PatientId
> Print 'Third Set'
> end
> else
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate between @.FromDate and @.ToDate
> and PatientId = @.PatientId
> Print 'Last Set'
> end
> end
>
> Delete from Range
> if @.PatientId = -1
> begin
> 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 PatientId as CustId FROM Attendance) Test ON 1
> = 1
> ) FullDates
> LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
> FullDates.Range_Date = Test.VisitDate
> WHERE Test.CustId IS NULL
> ORDER BY FullDates.CustId, FullDates.range_date
> end
> if @.PatientId > -1
> begin
> 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 PatientId as CustId FROM Attendance where
> Patientid = @.PatientId) Test ON 1 = 1
> ) FullDates
> LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
> FullDates.Range_Date = Test.VisitDate
> WHERE Test.CustId IS NULL
> ORDER BY FullDates.CustId, FullDates.range_date
> end
> if @.CounselorId = -1
> begin
> SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
> dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
> PATIENTS.FirstName as pName,
> Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
> COUNSELORS.C_LastName,
> dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
> FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
> LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
> LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID => dbo.nf_GetLastDayVisited.PatientId
> where PATIENTS.ActiveClient = 1
> and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
> ORDER BY pName
> end
> if @.CounselorId <> -1
> begin
> SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
> dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
> PATIENTS.FirstName as pName,
> Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
> COUNSELORS.C_LastName,
> dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
> FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
> LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
> LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID => dbo.nf_GetLastDayVisited.PatientId
> Where COUNSELORS.C_ID = @.CounselorId and PATIENTS.ActiveClient = 1
> and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
> ORDER BY pName
> end
> GO
> I have worked on it for several hours and not sure where to take it from
> here. Do I have to start to use a curser, or is there a way to do this with
> just a modification to the query I have so far. Thanks for any suggestions.
> Michael
>