SQl Questions

  • 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

  • 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.

  • 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