August 26, 2008 at 7:21 am
Hi,
I am working on attendance system. Students when starts school year we mark as 'S'. that is first day of the students school year. The next day his attendance will have like P(present),A(absent) like that for remainig days.
Generally school system set last day of the school year. Here it is Jun202007 was school last day of the year.
But some schools will send the students little earlier and attendance end with 'E'. remainign school will go until end of the school day.
I have table like
sch studentno date attendance
1010 234597 9/6/2007 S S- Start day
1010 235597 9/7/2007 P P- Present
1010 458796 9/9/2007 S
1010 458796 9/10/2007 P
1010 336789 9/9/2007 P The didnot enter as start day S just they mensioned as 'S'
1010 336789 9/10/2007 P
..........
..........
1010 234597 6/20/2008 P
1010 458796 6/12/2008 E
1010 336789 6/20/2008 P
I need 3 information
1. I want to find students present count between S and E
2. I want to find student present count between s and E if E is not there it looks for end of the school year day.
3. how to find the studentno if S Starting day is not there
For 3 rd questions
I tried
select * from table where attendance <>'S'
it didnot give exact studentno.
i have 5k+ have dont have S start day marked. I want to find the all 5k+ student no for reference
1 questions
select* from table where attendance between 'S' and 'E'
it populates nohing
2nd questions
no idea.
You guys have any idea.
Thanks
Ram
August 26, 2008 at 8:19 am
This sounds like homework or a test question, so it would be good for you to try some queries. We don't like to do work for you, but rather help you.
A few hints: 'S' and 'E' are characters. The only things between them are "f, g, h, i, etc." You need to check for dates, where you have a max(date) = 'E' and a min(date) = 'S'
Min will help you find the student with a WHERE clause that looks for a "P", but no "S"
COUNT(*) will help you with counting students. You'll need to group by student.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 26, 2008 at 8:38 am
You are right. it is simple statement
I think i want to explain little bit more
Student biginging date is 'S'
Student Ending date is 'E'
In the between student may have present(P), A( Absent),T, Tardy, X( excused Absent etc.)C (continues present) So user enter any one of that based on students attending.
I can use Min and Max for date side.
thanks for your input
Thaks
Ram
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply