Query Req: student & attendance table

Posted by Waqar100 under Sql Server on 3/27/2011 | Points: 10 | Views : 3107 | Status : [Member] | Replies : 2
Iam a fresh here
I have a table Attendance & student ..

I want to fetch the records at the end of month that date with studentid which are not present on particualar days ,
student table:

id name
1 waqar
2 atif
3 suresh
4 dhoni
5 afridi
attendance table: here studentID is a Foreign key
id Date studentID
1 3/27/2011 10:46:04 AM 1
2 3/27/2011 10:46:04 AM 2
3 3/27/2011 10:46:04 AM 4
4 3/27/2011 10:46:04 AM 5
5 3/26/2011 10:46:04 AM 2
6 3/26/2011 10:46:04 AM 3
7 3/26/2011 10:46:04 AM 4
8 3/25/2011 10:46:04 AM 1
9 3/25/2011 10:46:04 AM 5
10 3/24/2011 10:46:04 AM 3
11 3/24/2011 10:46:04 AM 1
12 3/24/2011 10:46:04 AM 4
13 3/23/2011 10:46:04 AM 2
14 3/22/2011 10:46:04 AM 4
15 3/22/2011 10:46:04 AM 3
16 3/22/2011 10:46:04 AM 5


I could not done this




Responses

Posted by: Amitmv76 on: 3/28/2011 [Member] Starter | Points: 25

Up
0
Down
Hi'
Try this query:



select studentID, studentNAME,attendanceID, attendanceDATE,attendanceSTATUS from student,attendance where studentID=attendanceID and attendanceSTATUS='A';

Note: You should have one column name as attendanceSTATUS in attendance table;
or u sent the both table columns name...

Waqar100, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 3/28/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi,

Nice Question.
Here i am using 'Procedure' to achieve your output.
I think its useful to you.
Create Proc stp_GetAbsentList

AS
Declare @tbDisDate Table
(
sNo int identity(1,1),
dtDate datetime
)
Insert into @tbDisDate (dtDate) Select AttDate from tbl_Attendance group by AttDate
Declare @sResult Table
(
StudId int,
StudName varchar(100),
AttDate datetime
)
Declare @nStart int
Declare @nTotal int
Declare @nSelDate datetime
Set @nStart = 1
Set @nTotal = (Select count(*) from @tbDisDate)
While(@nStart <= @nTotal)
Begin
Set @nSelDate =(Select dtDate from @tbDisDate where sNo = @nStart)
Insert into @sResult (StudId,StudName,AttDate)
Select distinct StudId,StudName, @nSelDate from tbl_Student where StudId not in
(Select StudId from tbl_Attendance where AttDate = @nSelDate)
Set @nStart = @nStart + 1
End
Select * from @sResult


Refer this code...then try it your self...




Thanks,
T.Saravanan

Waqar100, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response