Help with cross products and functions

Hello,
I have two tables that i need to join. The first table has a column which
is an encoded addition of one or more values in a second table.
table 1:
col1 col2 col3
1 a b 12
...
table 2:
col1 col2
1 x 4
2 y 8
3 z 16
...
the record for table 1, maps to records 1 and 2 from table 2. I know that
the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of this
math function that decodes the encryption.
I have a function that is able to return a table that lists all the values
in table 2 that correspond to the supplied value in table 1..ie if i call
function fn_mappings(12) i get a table called @.result:
table2value
1 4
2 8
what i need to do is then combine these records together to get a view as
follows
col1 col2 table2value
1 a b 4
2 a b 8
...
is this possible to do? i need this to happen for EACH row in table 1 to
find corresponding records in table 2.
thanks for any and all help!
BenIn order to link both tables, I took the liberty of "re-create" a convenient
environment to get the results you asked. I hope it is ok.
Let me know if it works for you
-- Begin Script
create table tbl1
( id int primary key
, col1 varchar(10)
, col2 varchar(10)
, col3 int
)
create table tbl2
(
id int primary key
, fkid int
, col1 varchar(10)
, col2 int
)
insert into tbl1
values (1, 'a', 'b', 12)
insert into tbl2
values (1, 1, 'x', 4)
insert into tbl2
values (2, 1, 'x', 8)
insert into tbl2
values (3, 2, 'x', 16)
-- View the output of both tables
select * from tbl1
select * from tbl2
go
create function dbo.fn_mappings(@.in int)
returns table
as
return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
col3 = @.in))
go
-- View the dbo.fn_mappings() output
select * from dbo.fn_mappings(12)
-- Output requested
select t1.id
, t1.col1
, t1.col2
, t2.col2
from tbl1 t1
inner join tbl2 t2
on t1.id = t2.fkid
-- Drop all objects
drop function dbo.fn_mappings
drop table tbl1
drop table tbl2
"Ben" wrote:

> Hello,
> I have two tables that i need to join. The first table has a column which
> is an encoded addition of one or more values in a second table.
> table 1:
> col1 col2 col3
> 1 a b 12
> ...
> table 2:
> col1 col2
> 1 x 4
> 2 y 8
> 3 z 16
> ...
> the record for table 1, maps to records 1 and 2 from table 2. I know that
> the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of thi
s
> math function that decodes the encryption.
> I have a function that is able to return a table that lists all the values
> in table 2 that correspond to the supplied value in table 1..ie if i call
> function fn_mappings(12) i get a table called @.result:
> table2value
> 1 4
> 2 8
> what i need to do is then combine these records together to get a view as
> follows
> col1 col2 table2value
> 1 a b 4
> 2 a b 8
> ...
> is this possible to do? i need this to happen for EACH row in table 1 to
> find corresponding records in table 2.
> thanks for any and all help!
> Ben|||Edgardo,
Thank you for your response...however, I do not have the liberty of changing
table2's structure. The data is provided by an external source and I am
creating custom reporting for it. Therefor i cant give it a foreign key typ
e
column.
do you have any other ideas?
Thanks again
Ben
"Edgardo Valdez, MCSD, MCDBA" wrote:
> In order to link both tables, I took the liberty of "re-create" a convenie
nt
> environment to get the results you asked. I hope it is ok.
> Let me know if it works for you
> -- Begin Script
> create table tbl1
> ( id int primary key
> , col1 varchar(10)
> , col2 varchar(10)
> , col3 int
> )
> create table tbl2
> (
> id int primary key
> , fkid int
> , col1 varchar(10)
> , col2 int
> )
> insert into tbl1
> values (1, 'a', 'b', 12)
> insert into tbl2
> values (1, 1, 'x', 4)
> insert into tbl2
> values (2, 1, 'x', 8)
> insert into tbl2
> values (3, 2, 'x', 16)
> -- View the output of both tables
> select * from tbl1
> select * from tbl2
> go
> create function dbo.fn_mappings(@.in int)
> returns table
> as
> return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
> col3 = @.in))
> go
> -- View the dbo.fn_mappings() output
> select * from dbo.fn_mappings(12)
> -- Output requested
> select t1.id
> , t1.col1
> , t1.col2
> , t2.col2
> from tbl1 t1
> inner join tbl2 t2
> on t1.id = t2.fkid
> -- Drop all objects
> drop function dbo.fn_mappings
> drop table tbl1
> drop table tbl2
>
> "Ben" wrote:
>

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
>

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 the
y
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 use
r
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.aspf...ndar-table.html
John
"Michael" wrote:

> Hi Everyone,
> I hope someone can help out some. I have a report that displays the days o
ur
> clients missed there sessions. That was working great, and I've got some n
ew
> requirements now for the report. They needed to store the number of days t
he
> client is suppose to attend, so I created a field called ClientSchedule th
at
> stores a value 1-7 (days as week). The thing that has got me now is that t
hey
> also want the report to represent this new field. So the report needs to n
ot
> 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 figu
re
> out how to work out the missed days within a week with a variable date ran
ge.
> 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 da
ys
> in the report(including all weeks that may be part of the Date range the u
ser
> 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.FirstNam
e,
> 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.FirstNam
e,
> 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 wit
h
> just a modification to the query I have so far. Thanks for any suggestions
.
> Michael
>sql

Help Trigger

i want to audit transactions done to table TOrig.
I created table TAudit same as TOrig in addition to ActionID (1 for
insert, 2 for update, 3 for delete), System Date and System User.
I created triggers on TOrigto insert into TAudit in case of insert,
update & delete.

TOrig contains text column. So i created instead of Trigger like:

Create TRIGGER TrigDelete
ON dbo.TOrig
Instead of Delete
AS
declare @.id int
select @.id=Liq_ID from Deleted

If Not Exists (Select * from TAudit where Liq_ID=@.id and ActionID=3 )
BEGIN
Insert into TAudit
select * , 3, GetDate(), System_USer
from Deleted

Delete from TOrig where Liq_ID=@.id
END
GO

PROBLEM: is that Torig is a detail table to a master table and has a
cascade delete relationship with the master table.
So Instead Trigger does it work. What do I DO??On 4 Oct 2004 06:21:50 -0700, Marie-Christine wrote:

>PROBLEM: is that Torig is a detail table to a master table and has a
>cascade delete relationship with the master table.
>So Instead Trigger does it work. What do I DO??

Hi Marie-Christine,

1. Create the trigger as an AFTER trigger, not as an INSTEAD OF trigger.

2. Rewrite the trigger, so that it will handle multi-rows inserts, updates
and deletes as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||
I would've liked to use For Delete trigger but i can't since there are
text columns in the table. Neither can i use After Trigger. And i can't
retrieve rows from Deleted table when there are text columns. That's why
i used Instead of Delte trigger. But now i'm hung cause Instead of
Delete trigger doesn't work with Cascade. That's why i'm confused now.
There are 2 problems: text columns in the table, and cascade delete. How
do i solve the 2 problems?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
ANyone!!! no solution???

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||MC <anonymous@.discussions.microsoft.com> wrote in message news:<4163a5d8$0$26140$c397aba@.news.newsgroups.ws>...
> ANyone!!! no solution???
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

If you can't use a trigger, you could put the DELETE and the audit
logic into a procedure, then make users and applications use the
procedure for all deletions.

Simon