SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

craigbroadman (5/18/2011)

My question is, do I create generic tables, with alot of redundant columns for each type e.g.

...

Or, do I get specific and go a level down and create separate...

I agree. I don't...

Stefan, your solution lists the staff members for that particular periodID. It seems to me he wants the staff that are not matched.

Working DDL for the adventurers:

create table tTimetable (DayID int, PeriodID int, StaffID int);

insert into tTimetable

values

(1,1,2),

(1,1,7),

(1,1,8),

(1,1,9),

(1,2,6),

(1,2,7),

(1,2,8),

(1,2,9),

(1,3,1),

(1,3,2),

(1,3,3),

(1,3,4),

(1,3,5),

(1,3,6),

(1,3,7)

create table tStaffTimeTableInfo (StaffID int, StaffInitials varchar(10));

insert into tStaffTimeTableInfo

values

(1,'ABC'),

(2,'RWT'),

(3,'YUJ'),

(4,'OPP'),

(5,'QWE'),

(6,'TFV'),

(7,'ASA'),

(8,'MNB'),

(9,'PLM')

drop table tTimetable;

drop table tStaffTimeTableInfo;

EDIT: he...

This is not a sub-query, it's a derived table and is...

Here is something we can work with. Start with a basic table create and some sample data.

create table SQL_List (Stmt_ID int identity(1,1), SQL_Stmt varchar(8000), File_location varchar(8000));

insert into SQL_List( SQL_Stmt, File_location)

select...

In step 2, what type of sql statements are you storing in the table?

I know all about the horrors of inherited code, so I know what you're going through. :w00t:

It seems like the author was using dynamic sql to conditionally append that last...

The variables in the dynamic sql run in a different scope than the procedure. But it looks like you are not concatenating your variables into the sql string. When you...

Can you post some DDL that works? 😉

You can conditionally pick that value with a sub-select:

SELECT count(B.TRANS_TYPE) STARTS, B.PROD_CODE pcode, B.CODE CScode

FROM SUBS B

WHERE B.TRANS_DATE between '1/1/2011' AND '5/1/2011'

AND (B.TRANS_TYPE = '3'

OR

B.TRANS_TYPE =...

mtassin (5/11/2011)...

At that point, just call the bloody thing a primary key.

So I see INT IDENTITY(1,1) and these days I assume PRIMARY KEY is stuck on the end of it.

Then...

This will get the bad children for you:

select s.stud_code, s.given_name, x.Num_Absences

from Student s

inner join

(

select stud_code, count(*) Num_Absences

from Student_Absent

group by stud_code

having count(*) > 2

) x on s.stud_code =...

Your code is working perfectly fine for me, Greg. I created a folder in c:\temp called "todd's files" and as long as there is a file in that folder, the...