February 4, 2006 at 5:12 am
I have an Attendence Table with the following columns.
Attendence_Date
User_ID
Class_ID
Lesson_ID
Status
Under my Status column - I have either of this values (Present,Absent) either one.
I am stuck at doing this complicated query.
I want to find which students whom have 5 Absents for a certain Lesson_ID.
It would be best if I could insert this details into another table. (User_ID,Lesson_ID,No.Of.Absent)
February 4, 2006 at 5:33 am
CREATE PROCEDURE STUDENT @Lesson_Name varchar(10) AS
DECLARE @Lesson_ID as int, @User_ID as varchar(12)
SELECT @Lesson_ID = Lesson_ID FROM Lesson WHERE Lesson_Name = @Lesson_Name
INSERT INTO TEMP
(User_ID,Lesson_ID,No_of_Absent)
SELECT User_ID ,Lesson_ID,0
FROM Attendence
UPDATE TEMP
SET No_of_Absent =
(SELECT COUNT (Status) AS "Absent" FROM Attendence WHERE Lesson_ID = @Lesson_ID AND Status = 'Abesent' AND User_ID = @User_ID)
I have done a stored procedure but i dont think this is right, but i have an idea what i am doing, the @User_ID i seem to be stuck...
February 4, 2006 at 5:51 am
something like this...
insert results_table(User_id,LessonId,Num_Abset)
select user_id,lesson_id,count(*) Num_absent
from SomeTable T
where t.status = 'Absent'
group by t.user_id,t.lesson_id
having count(*) = 5
Mathew J Kulangara
sqladventures.blogspot.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply