Problem with LEFT JOIN
-
I can only post the same code again:
- select sat.AttendanceTyID
- , sat.Description
- , sar.AttendanceRuID
- , 169 StudentID
- , sar.AttendanceDate
- , isnull(sar.RollupCount,0) RollupCount
- from SchoolAttendanceTy sat
- left join SchoolAttendanceRU sar
on sat.companyID=sar.companyID
- and sat.AttendanceTyID = sar.attendanceTyID
and sar.studentID = 169
- and sar.termID is null
- where sat.companyID= 1370
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Here is an update of what I have done:
set nocount on
create table dbo.SchoolAttendance(
AttendanceRuID int,
StudentID int,
AttendanceDate datetime,
AttendanceTyID int,
RollupCount int,
termID int null
)
create table dbo.SchoolAttendanceTy (
AttendanceTyID int,
Description varchar(25)
)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15097,169,'2006-05-15 00:00:00.000',4,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15098,169,'2006-05-17 00:00:00.000',4,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15172,169,'2006-04-03 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15173,169,'2006-04-05 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15174,169,'2006-04-17 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15175,169,'2006-04-19 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15176,169,'2006-05-01 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15177,169,'2006-05-03 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15178,169,'2006-05-31 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16001,169,'2006-02-01 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16014,169,'2006-02-13 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16194,169,'2006-02-15 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16243,169,'2006-06-16 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16322,169,'2006-02-27 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16408,169,'2006-07-18 00:00:00.000',3,1)
insert into dbo.SchoolAttendanceTy values (1,'Present')
insert into dbo.SchoolAttendanceTy values (2,'Excused Absence')
insert into dbo.SchoolAttendanceTy values (3,'Unexcused Absence')
insert into dbo.SchoolAttendanceTy values (4,'Tardy')
select
*
from
dbo.SchoolAttendance sa
right outer join dbo.SchoolAttendanceTy sat
on (sa.AttendanceTyID = sat.AttendanceTyID)
where
termID is null
select
*
from
dbo.SchoolAttendance sa
right outer join dbo.SchoolAttendanceTy sat
on (sa.AttendanceTyID = sat.AttendanceTyID)
where
termID is null
and (sa.StudentID = 169 or sa.StudentID is null)
select
*
from
dbo.SchoolAttendance sa
right outer join dbo.SchoolAttendanceTy sat
on (sa.AttendanceTyID = sat.AttendanceTyID)
where
termID is null
and (sa.StudentID = 169)
drop table dbo.SchoolAttendance
drop table dbo.SchoolAttendanceTy
set nocount off
With the following results:
AttendanceRuID StudentID AttendanceDate AttendanceTyID RollupCount termID AttendanceTyID Description
-------------- ----------- ----------------------- -------------- ----------- ----------- -------------- -------------------------
15172 169 2006-04-03 00:00:00.000 1 1 NULL 1 Present
15173 169 2006-04-05 00:00:00.000 1 1 NULL 1 Present
15174 169 2006-04-17 00:00:00.000 1 1 NULL 1 Present
15175 169 2006-04-19 00:00:00.000 1 1 NULL 1 Present
15176 169 2006-05-01 00:00:00.000 1 1 NULL 1 Present
15177 169 2006-05-03 00:00:00.000 1 1 NULL 1 Present
15178 169 2006-05-31 00:00:00.000 1 1 NULL 1 Present
16001 169 2006-02-01 00:00:00.000 1 1 NULL 1 Present
16014 169 2006-02-13 00:00:00.000 1 1 NULL 1 Present
16194 169 2006-02-15 00:00:00.000 1 1 NULL 1 Present
16243 169 2006-06-16 00:00:00.000 1 1 NULL 1 Present
16322 169 2006-02-27 00:00:00.000 1 1 NULL 1 Present
NULL NULL NULL NULL NULL NULL 2 Excused Absence
16408 169 2006-07-18 00:00:00.000 3 1 NULL 3 Unexcused Absence
15097 169 2006-05-15 00:00:00.000 4 1 NULL 4 Tardy
15098 169 2006-05-17 00:00:00.000 4 1 NULL 4 Tardy
AttendanceRuID StudentID AttendanceDate AttendanceTyID RollupCount termID AttendanceTyID Description
-------------- ----------- ----------------------- -------------- ----------- ----------- -------------- -------------------------
15172 169 2006-04-03 00:00:00.000 1 1 NULL 1 Present
15173 169 2006-04-05 00:00:00.000 1 1 NULL 1 Present
15174 169 2006-04-17 00:00:00.000 1 1 NULL 1 Present
15175 169 2006-04-19 00:00:00.000 1 1 NULL 1 Present
15176 169 2006-05-01 00:00:00.000 1 1 NULL 1 Present
15177 169 2006-05-03 00:00:00.000 1 1 NULL 1 Present
15178 169 2006-05-31 00:00:00.000 1 1 NULL 1 Present
16001 169 2006-02-01 00:00:00.000 1 1 NULL 1 Present
16014 169 2006-02-13 00:00:00.000 1 1 NULL 1 Present
16194 169 2006-02-15 00:00:00.000 1 1 NULL 1 Present
16243 169 2006-06-16 00:00:00.000 1 1 NULL 1 Present
16322 169 2006-02-27 00:00:00.000 1 1 NULL 1 Present
NULL NULL NULL NULL NULL NULL 2 Excused Absence
16408 169 2006-07-18 00:00:00.000 3 1 NULL 3 Unexcused Absence
15097 169 2006-05-15 00:00:00.000 4 1 NULL 4 Tardy
15098 169 2006-05-17 00:00:00.000 4 1 NULL 4 Tardy
AttendanceRuID StudentID AttendanceDate AttendanceTyID RollupCount termID AttendanceTyID Description
-------------- ----------- ----------------------- -------------- ----------- ----------- -------------- -------------------------
15097 169 2006-05-15 00:00:00.000 4 1 NULL 4 Tardy
15098 169 2006-05-17 00:00:00.000 4 1 NULL 4 Tardy
15172 169 2006-04-03 00:00:00.000 1 1 NULL 1 Present
15173 169 2006-04-05 00:00:00.000 1 1 NULL 1 Present
15174 169 2006-04-17 00:00:00.000 1 1 NULL 1 Present
15175 169 2006-04-19 00:00:00.000 1 1 NULL 1 Present
15176 169 2006-05-01 00:00:00.000 1 1 NULL 1 Present
15177 169 2006-05-03 00:00:00.000 1 1 NULL 1 Present
15178 169 2006-05-31 00:00:00.000 1 1 NULL 1 Present
16001 169 2006-02-01 00:00:00.000 1 1 NULL 1 Present
16014 169 2006-02-13 00:00:00.000 1 1 NULL 1 Present
16194 169 2006-02-15 00:00:00.000 1 1 NULL 1 Present
16243 169 2006-06-16 00:00:00.000 1 1 NULL 1 Present
16322 169 2006-02-27 00:00:00.000 1 1 NULL 1 Present
16408 169 2006-07-18 00:00:00.000 3 1 NULL 3 Unexcused Absence
Lynn
-
Yes, as I would expect. But the poster says the query I posted (which is like your first one) still doesn't work, i.e. still excludes the unmatched record. If teh setup is as described that query should have fixed the problem. So I want to know what code is being run exactly. Testing and retesting what we know to be the solution to the problem as posed won't help.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Actually, not. In your code, you have hardcoded the student id.
your code:
select sat.AttendanceTyID
, sat.Description
, sar.AttendanceRuID
, 169 StudentID
, sar.AttendanceDate
, isnull(sar.RollupCount,0) RollupCount
from SchoolAttendanceTy sat
left join SchoolAttendanceRU sar
on sat.companyID=sar.companyID
and sat.AttendanceTyID = sar.attendanceTyID
and sar.studentID = 169
and sar.termID is null
where sat.companyID= 1370
to make it work you need to do this:
select sat.AttendanceTyID
, sat.Description
, sar.AttendanceRuID
, isnull(sar.studentID, 169) StudentID -- , 169 StudentID
, sar.AttendanceDate
, isnull(sar.RollupCount,0) RollupCount
from SchoolAttendanceTy sat
left join SchoolAttendanceRU sar
on sat.companyID=sar.companyID
and sat.AttendanceTyID = sar.attendanceTyID
and isnull(sar.studentID, 169) -- and sar.studentID = 169
and sar.termID is null
where sat.companyID= 1370
Here is why, in your select statement you are hardcoding the studentid of 169 as StudentID,
but your where clause is "and sar.studentID = 169". If sar.studentID is null as a result of
the outer join (nothing on the right matches what is on the left), this part of the and clause
will cause it to be dropped from the result set.
There is a problem with both solutions above at this time, what if there are multiple student ids:
169, 170, 172, 185, etc?
Thanks,
Lynn
-
I have made some updates to my test code:
set nocount on
create table dbo.SchoolAttendance(
AttendanceRuID int,
StudentID int,
AttendanceDate datetime,
AttendanceTyID int,
RollupCount int,
termID int null
)
create table dbo.SchoolAttendanceTy (
AttendanceTyID int,
Description varchar(25)
)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15097,169,'2006-05-15 00:00:00.000',4,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15098,169,'2006-05-17 00:00:00.000',4,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15172,169,'2006-04-03 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15173,169,'2006-04-05 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15174,169,'2006-04-17 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15175,169,'2006-04-19 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15176,169,'2006-05-01 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15177,169,'2006-05-03 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15178,169,'2006-05-31 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16001,169,'2006-02-01 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16014,169,'2006-02-13 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16194,169,'2006-02-15 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16243,169,'2006-06-16 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16322,169,'2006-02-27 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16408,169,'2006-07-18 00:00:00.000',3,1)
insert into dbo.SchoolAttendanceTy values (1,'Present')
insert into dbo.SchoolAttendanceTy values (2,'Excused Absence')
insert into dbo.SchoolAttendanceTy values (3,'Unexcused Absence')
insert into dbo.SchoolAttendanceTy values (4,'Tardy')
select
sa.AttendanceRuID,
sa.StudentID,
sa.AttendanceDate,
sa.AttendanceTyID,
sa.RollupCount,
sa.termID,
sat.Description
from
dbo.SchoolAttendance sa
right outer join dbo.SchoolAttendanceTy sat
on (sa.AttendanceTyID = sat.AttendanceTyID)
where
termID is null
select
sa.AttendanceRuID,
sa.StudentID,
sa.AttendanceDate,
sa.AttendanceTyID,
sa.RollupCount,
sa.termID,
sat.Description
from
dbo.SchoolAttendance sa
right outer join dbo.SchoolAttendanceTy sat
on (sa.AttendanceTyID = sat.AttendanceTyID)
where
termID is null
and (sa.StudentID = 169 or sa.StudentID is null)
select
sa.AttendanceRuID,
sa.StudentID,
sa.AttendanceDate,
sa.AttendanceTyID,
sa.RollupCount,
sa.termID,
sat.Description
from
dbo.SchoolAttendance sa
right outer join dbo.SchoolAttendanceTy sat
on (sa.AttendanceTyID = sat.AttendanceTyID)
where
termID is null
and (sa.StudentID = 169)
select
sa.AttendanceRuID,
isnull(sa.StudentID, 169) as StudentID,
sa.AttendanceDate,
sa.AttendanceTyID,
sa.RollupCount,
sa.termID,
sat.Description
from
dbo.SchoolAttendance sa
right outer join dbo.SchoolAttendanceTy sat
on (sa.AttendanceTyID = sat.AttendanceTyID)
where
termID is null
select
sa.AttendanceRuID,
isnull(sa.StudentID, 169) as StudentID,
sa.AttendanceDate,
sa.AttendanceTyID,
sa.RollupCount,
sa.termID,
sat.Description
from
dbo.SchoolAttendance sa
right outer join dbo.SchoolAttendanceTy sat
on (sa.AttendanceTyID = sat.AttendanceTyID)
where
termID is null
and (sa.StudentID = 169 or sa.StudentID is null)
select
sa.AttendanceRuID,
isnull(sa.StudentID, 169) as StudentID,
sa.AttendanceDate,
sa.AttendanceTyID,
sa.RollupCount,
sa.termID,
sat.Description
from
dbo.SchoolAttendance sa
right outer join dbo.SchoolAttendanceTy sat
on (sa.AttendanceTyID = sat.AttendanceTyID)
where
termID is null
and (isnull(sa.StudentID, 169) = 169)
drop table dbo.SchoolAttendance
drop table dbo.SchoolAttendanceTy
set nocount off
Results:
AttendanceRuID StudentID AttendanceDate AttendanceTyID RollupCount termID Description
-------------- ----------- ----------------------- -------------- ----------- ----------- -------------------------
15172 169 2006-04-03 00:00:00.000 1 1 NULL Present
15173 169 2006-04-05 00:00:00.000 1 1 NULL Present
15174 169 2006-04-17 00:00:00.000 1 1 NULL Present
15175 169 2006-04-19 00:00:00.000 1 1 NULL Present
15176 169 2006-05-01 00:00:00.000 1 1 NULL Present
15177 169 2006-05-03 00:00:00.000 1 1 NULL Present
15178 169 2006-05-31 00:00:00.000 1 1 NULL Present
16001 169 2006-02-01 00:00:00.000 1 1 NULL Present
16014 169 2006-02-13 00:00:00.000 1 1 NULL Present
16194 169 2006-02-15 00:00:00.000 1 1 NULL Present
16243 169 2006-06-16 00:00:00.000 1 1 NULL Present
16322 169 2006-02-27 00:00:00.000 1 1 NULL Present
NULL NULL NULL NULL NULL NULL Excused Absence
16408 169 2006-07-18 00:00:00.000 3 1 NULL Unexcused Absence
15097 169 2006-05-15 00:00:00.000 4 1 NULL Tardy
15098 169 2006-05-17 00:00:00.000 4 1 NULL Tardy
AttendanceRuID StudentID AttendanceDate AttendanceTyID RollupCount termID Description
-------------- ----------- ----------------------- -------------- ----------- ----------- -------------------------
15172 169 2006-04-03 00:00:00.000 1 1 NULL Present
15173 169 2006-04-05 00:00:00.000 1 1 NULL Present
15174 169 2006-04-17 00:00:00.000 1 1 NULL Present
15175 169 2006-04-19 00:00:00.000 1 1 NULL Present
15176 169 2006-05-01 00:00:00.000 1 1 NULL Present
15177 169 2006-05-03 00:00:00.000 1 1 NULL Present
15178 169 2006-05-31 00:00:00.000 1 1 NULL Present
16001 169 2006-02-01 00:00:00.000 1 1 NULL Present
16014 169 2006-02-13 00:00:00.000 1 1 NULL Present
16194 169 2006-02-15 00:00:00.000 1 1 NULL Present
16243 169 2006-06-16 00:00:00.000 1 1 NULL Present
16322 169 2006-02-27 00:00:00.000 1 1 NULL Present
NULL NULL NULL NULL NULL NULL Excused Absence
16408 169 2006-07-18 00:00:00.000 3 1 NULL Unexcused Absence
15097 169 2006-05-15 00:00:00.000 4 1 NULL Tardy
15098 169 2006-05-17 00:00:00.000 4 1 NULL Tardy
AttendanceRuID StudentID AttendanceDate AttendanceTyID RollupCount termID Description
-------------- ----------- ----------------------- -------------- ----------- ----------- -------------------------
15097 169 2006-05-15 00:00:00.000 4 1 NULL Tardy
15098 169 2006-05-17 00:00:00.000 4 1 NULL Tardy
15172 169 2006-04-03 00:00:00.000 1 1 NULL Present
15173 169 2006-04-05 00:00:00.000 1 1 NULL Present
15174 169 2006-04-17 00:00:00.000 1 1 NULL Present
15175 169 2006-04-19 00:00:00.000 1 1 NULL Present
15176 169 2006-05-01 00:00:00.000 1 1 NULL Present
15177 169 2006-05-03 00:00:00.000 1 1 NULL Present
15178 169 2006-05-31 00:00:00.000 1 1 NULL Present
16001 169 2006-02-01 00:00:00.000 1 1 NULL Present
16014 169 2006-02-13 00:00:00.000 1 1 NULL Present
16194 169 2006-02-15 00:00:00.000 1 1 NULL Present
16243 169 2006-06-16 00:00:00.000 1 1 NULL Present
16322 169 2006-02-27 00:00:00.000 1 1 NULL Present
16408 169 2006-07-18 00:00:00.000 3 1 NULL Unexcused Absence
AttendanceRuID StudentID AttendanceDate AttendanceTyID RollupCount termID Description
-------------- ----------- ----------------------- -------------- ----------- ----------- -------------------------
15172 169 2006-04-03 00:00:00.000 1 1 NULL Present
15173 169 2006-04-05 00:00:00.000 1 1 NULL Present
15174 169 2006-04-17 00:00:00.000 1 1 NULL Present
15175 169 2006-04-19 00:00:00.000 1 1 NULL Present
15176 169 2006-05-01 00:00:00.000 1 1 NULL Present
15177 169 2006-05-03 00:00:00.000 1 1 NULL Present
15178 169 2006-05-31 00:00:00.000 1 1 NULL Present
16001 169 2006-02-01 00:00:00.000 1 1 NULL Present
16014 169 2006-02-13 00:00:00.000 1 1 NULL Present
16194 169 2006-02-15 00:00:00.000 1 1 NULL Present
16243 169 2006-06-16 00:00:00.000 1 1 NULL Present
16322 169 2006-02-27 00:00:00.000 1 1 NULL Present
NULL 169 NULL NULL NULL NULL Excused Absence
16408 169 2006-07-18 00:00:00.000 3 1 NULL Unexcused Absence
15097 169 2006-05-15 00:00:00.000 4 1 NULL Tardy
15098 169 2006-05-17 00:00:00.000 4 1 NULL Tardy
AttendanceRuID StudentID AttendanceDate AttendanceTyID RollupCount termID Description
-------------- ----------- ----------------------- -------------- ----------- ----------- -------------------------
15172 169 2006-04-03 00:00:00.000 1 1 NULL Present
15173 169 2006-04-05 00:00:00.000 1 1 NULL Present
15174 169 2006-04-17 00:00:00.000 1 1 NULL Present
15175 169 2006-04-19 00:00:00.000 1 1 NULL Present
15176 169 2006-05-01 00:00:00.000 1 1 NULL Present
15177 169 2006-05-03 00:00:00.000 1 1 NULL Present
15178 169 2006-05-31 00:00:00.000 1 1 NULL Present
16001 169 2006-02-01 00:00:00.000 1 1 NULL Present
16014 169 2006-02-13 00:00:00.000 1 1 NULL Present
16194 169 2006-02-15 00:00:00.000 1 1 NULL Present
16243 169 2006-06-16 00:00:00.000 1 1 NULL Present
16322 169 2006-02-27 00:00:00.000 1 1 NULL Present
NULL 169 NULL NULL NULL NULL Excused Absence
16408 169 2006-07-18 00:00:00.000 3 1 NULL Unexcused Absence
15097 169 2006-05-15 00:00:00.000 4 1 NULL Tardy
15098 169 2006-05-17 00:00:00.000 4 1 NULL Tardy
AttendanceRuID StudentID AttendanceDate AttendanceTyID RollupCount termID Description
-------------- ----------- ----------------------- -------------- ----------- ----------- -------------------------
15172 169 2006-04-03 00:00:00.000 1 1 NULL Present
15173 169 2006-04-05 00:00:00.000 1 1 NULL Present
15174 169 2006-04-17 00:00:00.000 1 1 NULL Present
15175 169 2006-04-19 00:00:00.000 1 1 NULL Present
15176 169 2006-05-01 00:00:00.000 1 1 NULL Present
15177 169 2006-05-03 00:00:00.000 1 1 NULL Present
15178 169 2006-05-31 00:00:00.000 1 1 NULL Present
16001 169 2006-02-01 00:00:00.000 1 1 NULL Present
16014 169 2006-02-13 00:00:00.000 1 1 NULL Present
16194 169 2006-02-15 00:00:00.000 1 1 NULL Present
16243 169 2006-06-16 00:00:00.000 1 1 NULL Present
16322 169 2006-02-27 00:00:00.000 1 1 NULL Present
NULL 169 NULL NULL NULL NULL Excused Absence
16408 169 2006-07-18 00:00:00.000 3 1 NULL Unexcused Absence
15097 169 2006-05-15 00:00:00.000 4 1 NULL Tardy
15098 169 2006-05-17 00:00:00.000 4 1 NULL Tardy
hth,
Lynn
-
The hardcoded student ID is irrelevant. It's only there for simplicity. Obviously the query is not very versatile if that value (both occurrences) isn't replaced with a variable. But it's got nothing to do with the problem under discussion.
Please, johnsql, can we see the code that you are now running which incorporates the changes but still gives unwanted results?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
After wrestling with it some more, I have come up with a viable solution using a derived table and
a cross join. Please take a close look at the query to se how it is working.
set nocount on
create table dbo.SchoolAttendance(
AttendanceRuID int,
StudentID int,
AttendanceDate datetime,
AttendanceTyID int,
RollupCount int,
termID int null
)
create table dbo.SchoolAttendanceTy (
AttendanceTyID int,
Description varchar(25)
)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15097,169,'2006-05-15 00:00:00.000',4,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15098,169,'2006-05-17 00:00:00.000',4,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15172,169,'2006-04-03 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15173,169,'2006-04-05 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15174,169,'2006-04-17 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15175,169,'2006-04-19 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15176,169,'2006-05-01 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15177,169,'2006-05-03 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15178,169,'2006-05-31 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16001,169,'2006-02-01 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16014,169,'2006-02-13 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16194,169,'2006-02-15 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16243,169,'2006-06-16 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16322,169,'2006-02-27 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16408,169,'2006-07-18 00:00:00.000',3,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (17097,171,'2006-05-15 00:00:00.000',4,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (17098,171,'2006-05-17 00:00:00.000',4,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (17172,171,'2006-04-03 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (17173,171,'2006-04-05 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (17174,171,'2006-04-17 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (17175,171,'2006-04-19 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (17176,171,'2006-05-01 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (17177,171,'2006-05-03 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (17178,171,'2006-05-31 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (18001,171,'2006-02-01 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (18014,171,'2006-02-13 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (17194,171,'2006-02-15 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (18243,171,'2006-06-16 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (18322,171,'2006-02-27 00:00:00.000',1,1)
insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (17408,171,'2006-07-18 00:00:00.000',3,1)
insert into dbo.SchoolAttendanceTy values (1,'Present')
insert into dbo.SchoolAttendanceTy values (2,'Excused Absence')
insert into dbo.SchoolAttendanceTy values (3,'Unexcused Absence')
insert into dbo.SchoolAttendanceTy values (4,'Tardy')
select
sa.AttendanceRuID,
sa.StudentID as StuID,
dt.StudentID,
sa.AttendanceDate,
sa.AttendanceTyID,
dt.AttendanceTyID,
sa.RollupCount,
sa.termID,
dt.Description
from
dbo.SchoolAttendance sa
right outer join (
select distinct
sa1.StudentID,
sat1.AttendanceTyID,
sat1.Description
from
dbo.SchoolAttendance sa1
cross join dbo.SchoolAttendanceTy sat1
) as dt
on (sa.StudentID = dt.StudentID and sa.AttendanceTyID = dt.AttendanceTyID)
where
sa.termID is null
and dt.StudentID = 169
select
sa.AttendanceRuID,
sa.StudentID as StuID,
dt.StudentID,
sa.AttendanceDate,
sa.AttendanceTyID,
dt.AttendanceTyID,
sa.RollupCount,
sa.termID,
dt.Description
from
dbo.SchoolAttendance sa
right outer join (
select distinct
sa1.StudentID,
sat1.AttendanceTyID,
sat1.Description
from
dbo.SchoolAttendance sa1
cross join dbo.SchoolAttendanceTy sat1
) as dt
on (sa.StudentID = dt.StudentID and sa.AttendanceTyID = dt.AttendanceTyID)
where
sa.termID is null
drop table dbo.SchoolAttendance
drop table dbo.SchoolAttendanceTy
set nocount off
With results:
AttendanceRuID StuID StudentID AttendanceDate AttendanceTyID AttendanceTyID RollupCount termID Description
-------------- ----------- ----------- ----------------------- -------------- -------------- ----------- ----------- -------------------------
NULL NULL 169 NULL NULL 2 NULL NULL Excused Absence
15172 169 169 2006-04-03 00:00:00.000 1 1 1 NULL Present
15173 169 169 2006-04-05 00:00:00.000 1 1 1 NULL Present
15174 169 169 2006-04-17 00:00:00.000 1 1 1 NULL Present
15175 169 169 2006-04-19 00:00:00.000 1 1 1 NULL Present
15176 169 169 2006-05-01 00:00:00.000 1 1 1 NULL Present
15177 169 169 2006-05-03 00:00:00.000 1 1 1 NULL Present
15178 169 169 2006-05-31 00:00:00.000 1 1 1 NULL Present
16001 169 169 2006-02-01 00:00:00.000 1 1 1 NULL Present
16014 169 169 2006-02-13 00:00:00.000 1 1 1 NULL Present
16194 169 169 2006-02-15 00:00:00.000 1 1 1 NULL Present
16243 169 169 2006-06-16 00:00:00.000 1 1 1 NULL Present
16322 169 169 2006-02-27 00:00:00.000 1 1 1 NULL Present
15097 169 169 2006-05-15 00:00:00.000 4 4 1 NULL Tardy
15098 169 169 2006-05-17 00:00:00.000 4 4 1 NULL Tardy
16408 169 169 2006-07-18 00:00:00.000 3 3 1 NULL Unexcused Absence
AttendanceRuID StuID StudentID AttendanceDate AttendanceTyID AttendanceTyID RollupCount termID Description
-------------- ----------- ----------- ----------------------- -------------- -------------- ----------- ----------- -------------------------
15172 169 169 2006-04-03 00:00:00.000 1 1 1 NULL Present
15173 169 169 2006-04-05 00:00:00.000 1 1 1 NULL Present
15174 169 169 2006-04-17 00:00:00.000 1 1 1 NULL Present
15175 169 169 2006-04-19 00:00:00.000 1 1 1 NULL Present
15176 169 169 2006-05-01 00:00:00.000 1 1 1 NULL Present
15177 169 169 2006-05-03 00:00:00.000 1 1 1 NULL Present
15178 169 169 2006-05-31 00:00:00.000 1 1 1 NULL Present
16001 169 169 2006-02-01 00:00:00.000 1 1 1 NULL Present
16014 169 169 2006-02-13 00:00:00.000 1 1 1 NULL Present
16194 169 169 2006-02-15 00:00:00.000 1 1 1 NULL Present
16243 169 169 2006-06-16 00:00:00.000 1 1 1 NULL Present
16322 169 169 2006-02-27 00:00:00.000 1 1 1 NULL Present
NULL NULL 169 NULL NULL 2 NULL NULL Excused Absence
16408 169 169 2006-07-18 00:00:00.000 3 3 1 NULL Unexcused Absence
15097 169 169 2006-05-15 00:00:00.000 4 4 1 NULL Tardy
15098 169 169 2006-05-17 00:00:00.000 4 4 1 NULL Tardy
17172 171 171 2006-04-03 00:00:00.000 1 1 1 NULL Present
17173 171 171 2006-04-05 00:00:00.000 1 1 1 NULL Present
17174 171 171 2006-04-17 00:00:00.000 1 1 1 NULL Present
17175 171 171 2006-04-19 00:00:00.000 1 1 1 NULL Present
17176 171 171 2006-05-01 00:00:00.000 1 1 1 NULL Present
17177 171 171 2006-05-03 00:00:00.000 1 1 1 NULL Present
17178 171 171 2006-05-31 00:00:00.000 1 1 1 NULL Present
18001 171 171 2006-02-01 00:00:00.000 1 1 1 NULL Present
18014 171 171 2006-02-13 00:00:00.000 1 1 1 NULL Present
17194 171 171 2006-02-15 00:00:00.000 1 1 1 NULL Present
18243 171 171 2006-06-16 00:00:00.000 1 1 1 NULL Present
18322 171 171 2006-02-27 00:00:00.000 1 1 1 NULL Present
NULL NULL 171 NULL NULL 2 NULL NULL Excused Absence
17408 171 171 2006-07-18 00:00:00.000 3 3 1 NULL Unexcused Absence
17097 171 171 2006-05-15 00:00:00.000 4 4 1 NULL Tardy
17098 171 171 2006-05-17 00:00:00.000 4 4 1 NULL Tardy
I think you will find it works.
Please copy the code and play with it in a test environment to see if it does what you want.
Thanks,
Lynn
-
I stand corrected on stax68's code. My misperception was on where the criteria select was being completed. I have done some testing and I am posting those results here for all to see:
set nocount on
--select sat.*
--from SchoolAttendanceTy sat
--where sat.companyID=1370
--and sat.AttendanceTyID = 2
--
--gave me:
--
--
--CompanyID AttendanceTyID Description Code IsDefault IsReportable LastSortType AttendanceRollupRule RuleModifiedOn RuleModifiedBy LastRule ApplyExclusiveCount ExclusivePriorityOfAttType ApplyInclusiveCount
------------- -------------- ------------------ --------- ------------ ------------ -------------------- -------------- -------------- -------- ------------------- -------------------- -------------------
--1370 2 Excused Absence EA 0 1 Grade 2 2006-05-26 10:44:31.343 242571 3 1 1 0
--
--
--select sar.* from SchoolAttendanceRU sar
--where sar.companyID= 1370 and sar.studentID = 169 and termID is null
--
--gave me:
--
--
--CompanyID AttendanceRuID StudentID AttendanceDate AttendanceTyID CreatedBy CreatedOn ModifiedBy ModifiedOn ModifiedComments IsManuallyModified RollupCount TermID ModifiedRollupCount
------------- -------------- ----------- ----------------------- -------------- ----------------- ------------ -------------- ----------- ---------------- ------------------ ----------- ------ -------------------
--1370 15097 169 2006-05-15 00:00:00.000 4 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
--1370 15098 169 2006-05-17 00:00:00.000 4 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
--1370 15172 169 2006-04-03 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
--1370 15173 169 2006-04-05 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
--1370 15174 169 2006-04-17 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
--1370 15175 169 2006-04-19 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
--1370 15176 169 2006-05-01 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
--1370 15177 169 2006-05-03 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
--1370 15178 169 2006-05-31 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
--1370 16001 169 2006-02-01 00:00:00.000 1 242571 2006-06-15 10:53:21.553 NULL NULL NULL NULL 1 NULL NULL
--1370 16014 169 2006-02-13 00:00:00.000 1 242254 2006-06-15 14:53:29.850 NULL NULL NULL NULL 1 NULL NULL
--1370 16194 169 2006-02-15 00:00:00.000 1 242254 2006-06-15 15:50:40.663 NULL NULL NULL NULL 1 NULL NULL
--1370 16243 169 2006-06-16 00:00:00.000 1 242254 2006-06-16 11:58:45.900 NULL NULL NULL NULL 1 NULL NULL
--1370 16322 169 2006-02-27 00:00:00.000 1 242254 2006-06-21 11:51:39.587 NULL NULL NULL NULL 1 NULL NULL
--1370 16408 169 2006-07-18 00:00:00.000 3 242571 2006-07-18 09:02:14.917 NULL NULL NULL NULL 1 NULL NULL
--
--(15 row(s) affected)
--
--
--select
-- sat.AttendanceTyID
-- , sat.Description
-- , sar.AttendanceRuID
-- , 169 StudentID
-- , sar.AttendanceDate
-- , isnull(sar.RollupCount,0) RollupCount
--from
-- SchoolAttendanceTy sat
-- left join SchoolAttendanceRU sar
-- on (sat.companyID=sar.companyID
-- and sat.AttendanceTyID = sar.attendanceTyID
-- and sar.studentID = 169
-- and sar.termID is null)
--where
-- sat.companyID= 1370
create table dbo.SchoolAttendanceTy (
CompanyID int,
AttendanceTyID int,
DescriptionCode varchar(50),
IsDefault bit,
IsReportable bit,
LastSortType varchar(10),
AttendanceRollupRule int,
RuleModifiedOn datetime,
RuleModifiedBy int,
LastRule int,
ApplyExclusiveCount bit,
ExclusivePriorityOfAttType bit,
ApplyInclusiveCount bit
)
create table dbo.SchoolAttendanceRU (
CompanyID int,
AttendanceRuID int,
StudentID int,
AttendanceDate datetime,
AttendanceTyID int,
CreatedBy int,
CreatedOn datetime,
ModifiedBy int null,
ModifiedOn datetime null,
ModifiedComments varchar(50) null,
IsManuallyModified int null,
RollupCount int null,
TermID int null,
ModifiedRollupCount int null
)
insert into dbo.SchoolAttendanceTy values(1370,2,'Excused Absence EA',0,1,'Grade',2,'2006-05-26 10:44:31.343',242571,3,1,1,0)
insert into dbo.SchoolAttendanceTy values (1370,1,'Present P',0,1,'Grade',2,'2006-05-26 10:44:31.343',242571,3,1,1,0)
insert into dbo.SchoolAttendanceTy values (1370,3,'Unexcused Absence UA',0,1,'Grade',2,'2006-05-26 10:44:31.343',242571,3,1,1,0)
insert into dbo.SchoolAttendanceTy values (1370,4,'Tardy T',0,1,'Grade',2,'2006-05-26 10:44:31.343',242571,3,1,1,0)
insert into dbo.SchoolAttendanceRU values(1370,15097,169,'2006-05-15 00:00:00.000',4,242571,'2006-06-14 12:50:59.240',NULL,NULL,NULL,NULL,1,NULL,NULL)
insert into dbo.SchoolAttendanceRU values(1370,15098,169,'2006-05-17 00:00:00.000',4,242571,'2006-06-14 12:50:59.240',NULL,NULL,NULL,NULL,1,NULL,NULL)
insert into dbo.SchoolAttendanceRU values(1370,15172,169,'2006-04-03 00:00:00.000',1,242571,'2006-06-14 12:50:59.240',NULL,NULL,NULL,NULL,1,NULL,NULL)
insert into dbo.SchoolAttendanceRU values(1370,15173,169,'2006-04-05 00:00:00.000',1,242571,'2006-06-14 12:50:59.240',NULL,NULL,NULL,NULL,1,NULL,NULL)
insert into dbo.SchoolAttendanceRU values(1370,15174,169,'2006-04-17 00:00:00.000',1,242571,'2006-06-14 12:50:59.240',NULL,NULL,NULL,NULL,1,NULL,NULL)
insert into dbo.SchoolAttendanceRU values(1370,15175,169,'2006-04-19 00:00:00.000',1,242571,'2006-06-14 12:50:59.240',NULL,NULL,NULL,NULL,1,NULL,NULL)
insert into dbo.SchoolAttendanceRU values(1370,15176,169,'2006-05-01 00:00:00.000',1,242571,'2006-06-14 12:50:59.240',NULL,NULL,NULL,NULL,1,NULL,NULL)
insert into dbo.SchoolAttendanceRU values(1370,15177,169,'2006-05-03 00:00:00.000',1,242571,'2006-06-14 12:50:59.240',NULL,NULL,NULL,NULL,1,NULL,NULL)
insert into dbo.SchoolAttendanceRU values(1370,15178,169,'2006-05-31 00:00:00.000',1,242571,'2006-06-14 12:50:59.240',NULL,NULL,NULL,NULL,1,NULL,NULL)
insert into dbo.SchoolAttendanceRU values(1370,16001,169,'2006-02-01 00:00:00.000',1,242571,'2006-06-15 10:53:21.553',NULL,NULL,NULL,NULL,1,NULL,NULL)
insert into dbo.SchoolAttendanceRU values(1370,16014,169,'2006-02-13 00:00:00.000',1,242254,'2006-06-15 14:53:29.850',NULL,NULL,NULL,NULL,1,NULL,NULL)
insert into dbo.SchoolAttendanceRU values(1370,16194,169,'2006-02-15 00:00:00.000',1,242254,'2006-06-15 15:50:40.663',NULL,NULL,NULL,NULL,1,NULL,NULL)
insert into dbo.SchoolAttendanceRU values(1370,16243,169,'2006-06-16 00:00:00.000',1,242254,'2006-06-16 11:58:45.900',NULL,NULL,NULL,NULL,1,NULL,NULL)
insert into dbo.SchoolAttendanceRU values(1370,16322,169,'2006-02-27 00:00:00.000',1,242254,'2006-06-21 11:51:39.587',NULL,NULL,NULL,NULL,1,NULL,NULL)
insert into dbo.SchoolAttendanceRU values(1370,16408,169,'2006-07-18 00:00:00.000',3,242571,'2006-07-18 09:02:14.917',NULL,NULL,NULL,NULL,1,NULL,NULL)
select
sat.*
from
dbo.SchoolAttendanceTy sat
where
sat.CompanyID=1370
and sat.AttendanceTyID = 2
select
sar.*
from
SchoolAttendanceRU sar
where
sar.CompanyID = 1370
and sar.StudentID = 169
and TermID is null
select
sat.AttendanceTyID
, sat.DescriptionCode
, sar.AttendanceRuID
, 169 StudentID
, sar.AttendanceDate
, isnull(sar.RollupCount,0) RollupCount
from
dbo.SchoolAttendanceTy sat
left outer join dbo.SchoolAttendanceRU sar
on (sat.CompanyID=sar.CompanyID
and sat.AttendanceTyID = sar.AttendanceTyID
and sar.StudentID = 169
and sar.TermID is null)
where
sat.CompanyID = 1370
select
sat.AttendanceTyID
, sat.DescriptionCode
, sar.AttendanceRuID
, 169 StudentID
, sar.AttendanceDate
, isnull(sar.RollupCount,0) RollupCount
from
dbo.SchoolAttendanceTy sat
left outer join dbo.SchoolAttendanceRU sar
on (sat.AttendanceTyID = sar.AttendanceTyID)
where
sat.CompanyID = 1370
and sar.StudentID = 169
and sar.TermID is null
select
sat.AttendanceTyID
, sat.DescriptionCode
, sar.AttendanceRuID
, 169 StudentID
, sar.AttendanceDate
, isnull(sar.RollupCount,0) RollupCount
from
dbo.SchoolAttendanceTy sat
left outer join dbo.SchoolAttendanceRU sar
on (sat.AttendanceTyID = sar.AttendanceTyID)
where
sat.CompanyID = 1370
and (sar.StudentID = 169 or sar.StudentID is null)
and sar.TermID is null
drop table dbo.SchoolAttendanceTy
drop table dbo.SchoolAttendanceRU
set nocount off
CompanyID AttendanceTyID DescriptionCode IsDefault IsReportable LastSortType AttendanceRollupRule RuleModifiedOn RuleModifiedBy LastRule ApplyExclusiveCount ExclusivePriorityOfAttType ApplyInclusiveCount
----------- -------------- -------------------------------------------------- --------- ------------ ------------ -------------------- ----------------------- -------------- ----------- ------------------- -------------------------- -------------------
1370 2 Excused Absence EA 0 1 Grade 2 2006-05-26 10:44:31.343 242571 3 1 1 0
CompanyID AttendanceRuID StudentID AttendanceDate AttendanceTyID CreatedBy CreatedOn ModifiedBy ModifiedOn ModifiedComments IsManuallyModified RollupCount TermID ModifiedRollupCount
----------- -------------- ----------- ----------------------- -------------- ----------- ----------------------- ----------- ----------------------- -------------------------------------------------- ------------------ ----------- ----------- -------------------
1370 15097 169 2006-05-15 00:00:00.000 4 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
1370 15098 169 2006-05-17 00:00:00.000 4 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
1370 15172 169 2006-04-03 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
1370 15173 169 2006-04-05 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
1370 15174 169 2006-04-17 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
1370 15175 169 2006-04-19 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
1370 15176 169 2006-05-01 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
1370 15177 169 2006-05-03 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
1370 15178 169 2006-05-31 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL
1370 16001 169 2006-02-01 00:00:00.000 1 242571 2006-06-15 10:53:21.553 NULL NULL NULL NULL 1 NULL NULL
1370 16014 169 2006-02-13 00:00:00.000 1 242254 2006-06-15 14:53:29.850 NULL NULL NULL NULL 1 NULL NULL
1370 16194 169 2006-02-15 00:00:00.000 1 242254 2006-06-15 15:50:40.663 NULL NULL NULL NULL 1 NULL NULL
1370 16243 169 2006-06-16 00:00:00.000 1 242254 2006-06-16 11:58:45.900 NULL NULL NULL NULL 1 NULL NULL
1370 16322 169 2006-02-27 00:00:00.000 1 242254 2006-06-21 11:51:39.587 NULL NULL NULL NULL 1 NULL NULL
1370 16408 169 2006-07-18 00:00:00.000 3 242571 2006-07-18 09:02:14.917 NULL NULL NULL NULL 1 NULL NULL
AttendanceTyID DescriptionCode AttendanceRuID StudentID AttendanceDate RollupCount
-------------- -------------------------------------------------- -------------- ----------- ----------------------- -----------
2 Excused Absence EA NULL 169 NULL 0
1 Present P 15172 169 2006-04-03 00:00:00.000 1
1 Present P 15173 169 2006-04-05 00:00:00.000 1
1 Present P 15174 169 2006-04-17 00:00:00.000 1
1 Present P 15175 169 2006-04-19 00:00:00.000 1
1 Present P 15176 169 2006-05-01 00:00:00.000 1
1 Present P 15177 169 2006-05-03 00:00:00.000 1
1 Present P 15178 169 2006-05-31 00:00:00.000 1
1 Present P 16001 169 2006-02-01 00:00:00.000 1
1 Present P 16014 169 2006-02-13 00:00:00.000 1
1 Present P 16194 169 2006-02-15 00:00:00.000 1
1 Present P 16243 169 2006-06-16 00:00:00.000 1
1 Present P 16322 169 2006-02-27 00:00:00.000 1
3 Unexcused Absence UA 16408 169 2006-07-18 00:00:00.000 1
4 Tardy T 15097 169 2006-05-15 00:00:00.000 1
4 Tardy T 15098 169 2006-05-17 00:00:00.000 1
AttendanceTyID DescriptionCode AttendanceRuID StudentID AttendanceDate RollupCount
-------------- -------------------------------------------------- -------------- ----------- ----------------------- -----------
4 Tardy T 15097 169 2006-05-15 00:00:00.000 1
4 Tardy T 15098 169 2006-05-17 00:00:00.000 1
1 Present P 15172 169 2006-04-03 00:00:00.000 1
1 Present P 15173 169 2006-04-05 00:00:00.000 1
1 Present P 15174 169 2006-04-17 00:00:00.000 1
1 Present P 15175 169 2006-04-19 00:00:00.000 1
1 Present P 15176 169 2006-05-01 00:00:00.000 1
1 Present P 15177 169 2006-05-03 00:00:00.000 1
1 Present P 15178 169 2006-05-31 00:00:00.000 1
1 Present P 16001 169 2006-02-01 00:00:00.000 1
1 Present P 16014 169 2006-02-13 00:00:00.000 1
1 Present P 16194 169 2006-02-15 00:00:00.000 1
1 Present P 16243 169 2006-06-16 00:00:00.000 1
1 Present P 16322 169 2006-02-27 00:00:00.000 1
3 Unexcused Absence UA 16408 169 2006-07-18 00:00:00.000 1
AttendanceTyID DescriptionCode AttendanceRuID StudentID AttendanceDate RollupCount
-------------- -------------------------------------------------- -------------- ----------- ----------------------- -----------
2 Excused Absence EA NULL 169 NULL 0
1 Present P 15172 169 2006-04-03 00:00:00.000 1
1 Present P 15173 169 2006-04-05 00:00:00.000 1
1 Present P 15174 169 2006-04-17 00:00:00.000 1
1 Present P 15175 169 2006-04-19 00:00:00.000 1
1 Present P 15176 169 2006-05-01 00:00:00.000 1
1 Present P 15177 169 2006-05-03 00:00:00.000 1
1 Present P 15178 169 2006-05-31 00:00:00.000 1
1 Present P 16001 169 2006-02-01 00:00:00.000 1
1 Present P 16014 169 2006-02-13 00:00:00.000 1
1 Present P 16194 169 2006-02-15 00:00:00.000 1
1 Present P 16243 169 2006-06-16 00:00:00.000 1
1 Present P 16322 169 2006-02-27 00:00:00.000 1
3 Unexcused Absence UA 16408 169 2006-07-18 00:00:00.000 1
4 Tardy T 15097 169 2006-05-15 00:00:00.000 1
4 Tardy T 15098 169 2006-05-17 00:00:00.000 1
thanks,
Lynn
-
Table "SchoolAttendance"
------------------------
AttendanceRuID StudentID AttendanceDate AttendanceTyID RollupCount termID
-------------- ----------- ------------------------------------------------------ -------------- ----------- -----------
15097 169 2006-05-15 00:00:00.000 4 1 NULL
15098 169 2006-05-17 00:00:00.000 4 1 NULL
15172 169 2006-04-03 00:00:00.000 1 1 NULL
15173 169 2006-04-05 00:00:00.000 1 1 NULL
15174 169 2006-04-17 00:00:00.000 1 1 NULL
15175 169 2006-04-19 00:00:00.000 1 1 NULL
15176 169 2006-05-01 00:00:00.000 1 1 NULL
15177 169 2006-05-03 00:00:00.000 1 1 NULL
15178 169 2006-05-31 00:00:00.000 1 1 NULL
16001 169 2006-02-01 00:00:00.000 1 1 NULL
16014 169 2006-02-13 00:00:00.000 1 1 NULL
16194 169 2006-02-15 00:00:00.000 1 1 NULL
16243 169 2006-06-16 00:00:00.000 1 1 NULL
16322 169 2006-02-27 00:00:00.000 1 1 NULL
16408 169 2006-07-18 00:00:00.000 3 1 NULL
(15 row(s) affected)
Table "SchoolAttendanceTy" (I add 1 more column named "CompanyID"; It is included in the table defintion, but earlier I forgot to include it).
----------------------------
CompanyID AttendanceTyID Description
---------- ------------- --------------------------------------------------
1370 1 Present
1370 2 Excused Absence
1370 3 Unexcused Absence
1370 4 Tardy
Somebody proposed I should NOT place criteria on WHERE clause but on LEFT JOIN ... ON like follows:
select sat.AttendanceTyID, sat.Description, sar.AttendanceRuID, coalesce(sar.StudentID, 169) as studentID, sar.AttendanceDate, sar.AttendanceTyID,
sar.RollupCount, sar.termID
from SchoolAttendanceTy sat
left outer join SchoolAttendanceRU sar
on sat.companyID=sar.companyID and
sat.AttendanceTyID = sar.attendanceTyID and
sar.companyID= 1370 and sar.studentID = 169 and sar.termID is null
WHERE sat.CompanyID = 1370
and running this gave me exact what I want:
AttendanceTyID Description AttendanceRuID studentID AttendanceDate AttendanceTyID RollupCount termID
-------------- -------------------------------------------------- -------------- ----------- ------------------------------------------------------ -------------- ----------- -----------
1 Present 15172 169 2006-04-03 00:00:00.000 1 1 NULL
1 Present 15173 169 2006-04-05 00:00:00.000 1 1 NULL
1 Present 15174 169 2006-04-17 00:00:00.000 1 1 NULL
1 Present 15175 169 2006-04-19 00:00:00.000 1 1 NULL
1 Present 15176 169 2006-05-01 00:00:00.000 1 1 NULL
1 Present 15177 169 2006-05-03 00:00:00.000 1 1 NULL
1 Present 15178 169 2006-05-31 00:00:00.000 1 1 NULL
1 Present 16001 169 2006-02-01 00:00:00.000 1 1 NULL
1 Present 16014 169 2006-02-13 00:00:00.000 1 1 NULL
1 Present 16194 169 2006-02-15 00:00:00.000 1 1 NULL
1 Present 16243 169 2006-06-16 00:00:00.000 1 1 NULL
1 Present 16322 169 2006-02-27 00:00:00.000 1 1 NULL
2 Excused Absence NULL 169 NULL NULL NULL NULL
3 Unexcused Absence 16408 169 2006-07-18 00:00:00.000 3 1 NULL
4 Tardy 15097 169 2006-05-15 00:00:00.000 4 1 NULL
4 Tardy 15098 169 2006-05-17 00:00:00.000 4 1 NULL
(16 row(s) affected)
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply