Query Req: student & attendance table

Posted by Waqar100 under Sql Server on 3/27/2011 | Points: 10 | Views : 4345 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

Latest Posts