Click here to Skip to main content
15,903,725 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 10-Dec-17 7:52am
v2
Comments
Wendelius 27-Mar-11 5:56am    
The attendance table description seems to be missing. Also have you already done something. If so, include the SQL into the post.

Since this seems like a homework I won't be including the full statement to the answer. However to get you started:

First you need all dates. If you can rely on a fact that at least one student has attended on each day then you could use the attendance table to create the dates. For example
SELECT DISTINCT Date FROM Attendance

This will give you a result set containing all the necessary dates which you can use as an inline view in the statement. Since you're having the time in the field it may be necessary to eliminate it. For that you can CAST[^] the date column to date (instead of datetime).

Now you can use that previous result set and using for example correlated NOT EXISTS[^] to the attendance table you can include only those days when the given student wasn't present.
 
Share this answer
 
ok,I m being motivated from your reply ,I did nt read completely the paragraph ,now again i am trying to break the wall against me,
thanks
no further answer to welcome,,
 
Share this answer
 
Comments
Wendelius 27-Mar-11 6:28am    
I'm glad if this motivated you. If you encounter problems/questions, drop a new question and include the statement you've done so far. I'm certain that you will get good answers from this forum especially if you show the effort you've already done :)

Just a side note: You can post a comment to the original answer. This way the person who delivered the answer will be automatically notified via email.

Good luck!
I think you are looking for this.

select std.id, attd.date
from Student std
left join Attendance attd
on std.id = attd.studentId
where attd.studentId is null
 
Share this answer
 
Comments
Deepu S Nair 11-Dec-17 2:27am    
You are answering a question which is nearly 8 years old .It may attract downvoting your answer.Please try to answer new questions

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900