Need help to write query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help to write query

Author

Topic

yoshitha
Starting Member

4 Posts

Posted - 2006-02-14 : 00:39:47

Hi all
here i'm listing out the table structures for the query i need to write.

1. Faculty_type:

This table consists of the following fields.

FtypeID FtypeName facultytype

(P.Key) FAC00001 Us Teacher Teaching
FAC00002 LS Teacher Teaching
FAC00003 Office Staff Non Teaching
FAC00004 Teacher Teaching

2. Staff_Detail

Staff_details table consists of following fields (it consists of some other fields but it is un necessary here)

StaffId (p.k) FtypeId (F.k) Fname

STA00001 FAC00001 A
STA00002 FAC00002 B
STA00003 FAC00003 C
STA00004 FAC00004 D

3. Examination_TimeTable consists of the following fields (I list few fields only)

ClassId ExamDate SubId Invigilation

CLD00001 3/12/1996 SUB00001 Null
CLD00001 3/14/1996 SUB00002 Null
CLD00001 3/18/1996 SUB00003 Null

CLD00002 3/12/1996 SUB00004 Null
CLD00002 3/18/1996 SUB00005 Null
CLD00002 3/20/1996 SUB00006 Null

(classid is primary in classes table and foreign key in subjects table)
(subid is primary key in subjects table)

And for examination_timetable there is no primary key but it is having foreign keys (like classid,subid).

Now my requirement is to assign a teaching staff for invigilation of any subjects of any class.
And we can assign more that one staff for invigilation for a particular subject.

To assign a staff I’m displaying those staff in list box. ( so that for a particular subject we can select more that one staff).

So for the first class(cld00001) and sub00001 I set the following staff (data looks like this in examination_timetable)

ClassId Exam date SubId Invigilation
CLD00001 2/12/1996 SUB00001 STA00001,STA00001

And now if I’m trying to assign staff for second class (CLD00002) then it should not display in the list STA00001 and STA00001 since for the same day they have assigned for the first class, so other that these 2 people we have to display other un assigned teaching staff.

so what i need is , i wnat those staff whore are not assigned for any invigilation for a selected day ( i've to check for all the class that exists in the system).

I wrote query like this which wrong I know.

select staffid from staff_details,faculty_type where
facultytype='teaching'

and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
(select invigilation from Examination_timetable where examdate =
'3/12/2006' and subid='SUB00001' and classid='CLD00001')

but i need the modified version of the query and which satisfies my requirement.

hope u got what i said till now.

thanx
jyothi

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-14 : 01:21:51

Hi Jyothi,
You can first get all the staffs which are already been assigned the job on that particular day.

-- this will give you all the staff list engaged on that particular date i.e (if you are trying to assign a staff for classId 2 and subject 4 )

declare @staff_id varchar(100)
select @staff_id = coalesce( @staff_id +',' + b.invigilation, b.invigilation )
from Examination_TimeTable a , Examination_TimeTable b where
a.classid = 'CLD00002' and a.subid= 'SUB00004'
and b.invigilation is not null
and a.examdate = b.examdate

then you can use the CSv string in your in clause. for that you need to use the split function which will convert the csv to table

select staffid from staff_detail,faculty_type where
facultytype='teaching'
and staff_detail.ftypeid=faculty_type.ftypeid
and staffid not in
(select data from dbo.split(@staff_id,','))

for split function refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Hope this helps..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-14 : 01:31:06

quote:
so what i need is , i wnat those staff whore are not assigned for any invigilation for a selected day ( i've to check for all the class that exists in the system).

Is this what you want ? Else post the result that you need.

select 	StaffID 
from 	Staff_Detail d inner join Faculty_type f
	ON	d.FTypeID	= f.FtypeID
where 	f.FacultyType 		= 'Teaching'
and 	StaffID not in	(select Invigilation from Examination_TimeTable 
				where 	ExamDate = '1996-03-12'
				and	Invigilation	is not null)

----------------------------------
'KH'
everything that has a beginning has an end

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-14 : 01:45:55

quote:

And we can assign more that one staff for invigilation for a particular subject.
So for the first class(cld00001) and sub00001 I set the following staff (data looks like this in examination_timetable)

ClassId Exam date SubId Invigilation
CLD00001 2/12/1996 SUB00001 STA00001,STA00001

however if you know the exam date for which you are assigning staff then you can remove the join

and use
select @staff_id = coalesce( @staff_id +',' + b.invigilation, b.invigilation )
from Examination_TimeTable b where
b.examdate = '<examdate> '
and b.invigilation is not null

yoshitha
Starting Member

4 Posts

Posted - 2006-02-14 : 06:41:33

Hi Gupta
i'have used these statements to get the ans

declare @staff_id varchar(100)
select @staff_id = coalesce( @staff_id +',' + b.invigilation, b.invigilation)
from Examination_TimeTable b where
b.examdate = '3/12/2006'
and b.invigilation is not null

select staff_details.staffid from staff_details,faculty_type where
faculty_type.facultytype='teaching'
and staff_details.ftypeid=faculty_type.ftypeid
and staffid not in
(select data from dbo.split(@staff_id,','))

CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

and in the url, i copied the first spilt function and tried.

this is worked when the invigilation field contains data.

when i removed the data in invigilation field and run this query then i didnt get any data and it has to display 2 staff id's ( these 2staff id's only matches my requirememt)

when invigilation field contain data like this
STA00001,STA00002
then it return nothing which is correct.
when this field contains only one value STA00001 then when i execute the query it returned STA00002 and
when this field contans nothing it has to show me these 2 ids na instead it is not displayng anything.

Thanx & Regards
Jyothi

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-14 : 06:56:44

Hi,
This is because split func is returning a null row when null is passed.
you can add an if statement in DBo.split function so that it should not return a null row when null is passed.

CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

if @rowdata is not null
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

yoshitha
Starting Member

4 Posts

Posted - 2006-02-14 : 07:42:14

Hi KH

the solution u gave is not working when the invigilation field contains values like this STA00001,STA00002

yoshitha
Starting Member

4 Posts

Posted - 2006-02-14 : 08:10:48

Hi Guptha

thank you very much
now it is working exactly.

later if i have any doubts regarding this i'll be in touch with you.

once again thanks
Regards
Jyothi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources