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...

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...

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...

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...

I'm sorry, but I don't see how your code solves the problem.  You still have a condition in the where clause checking for StudentID = 169.  The table SchoolAttendanceTy does...

stax68,

Everything I used is in my big post.  Add where sa.StudentID = 169 to the where clause and you will reduce the result set displayed in my post by 1...

I just figured out the problem.  Your where clause contains sar.studentID = 169.

The line with AttendanceTyID = 2 is null in the column, and fails the where condition.

Change it to...

I took what you posted (I don't see CompanyID anywhere, so I left it off) and did the following:

set nocount on

create table dbo.SchoolAttendance(

    AttendanceRuID int,

    StudentID int,

    AttendanceDate datetime,

    AttendanceTyID...

Please try dropping the termId is null from the where clause using the query stax68 gave you.  What does that return?

Lynn

Colin,

Try the following, you will see that the table variable still has data after the rollback.

This can only mean that the insert to the table var inside the begin transaction...

Looks like this job is part of a maintenacne plan.  Do delete it, you will probably need to modify the plan.

Not sure, but if you deny the SA or Sys Admin from view database objects (databases, tables, etc), doesn't that mean you don't need a Sys Admin or SA?  A...

First problem, SchoolAttendanceType is on the right, not left.  Try a right outer join.  You may also want to drop the termId is null in the where clause.

hth,

Lynn

table vars are also faster as inserts/deletes/updates to table vars are not logged.

Lynn

It looks like you wanted a Windows collation during installation, but selected a SQL collation instead.  I can not find any windows collations that look like this: latin1_general_CP1_CI_AS.  The CP...