Count between two days

  • I have student attendance data. I want to calculate total number of school days between startdate and End date .I could write sql for startdate and End Date but eventhough i couldnot get it in the sameline. Startdate takes one line and End Date takes another line. I want to have all in one line

    Like

    studentno Startdate EndDate numberofdaysattended

    942531

  • this Jeff Moden's function for calculating work days should help:

    http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/

  • How do you know what row is a start date, and what row is an end date? Is there a field, or are you making assumptions based on which date is first?

  • if Attendance column stores value 'S' then it is start date for that student and attendance stores value as 'E' then it is end date for the student of the year.

    In the Excel sheet third column as attendance. That stores startdate date value as S and End Date value as E .

  • I think my questions didnot add all the stuff. I dont know why it loaded half only.

  • You can join the table with itself like so:

    select t1.student_id, t1.date_time startdate, t2.date_time enddate, datediff(dd,t1.date_time,t2.datetime) attendance_days

    from attendance t1 join attendance t2 on t1.student_id = t2.student_id

    and t1.type = 'S' and t2.type = 'E'

    This is assuming that there are only 2 dates per student

  • have student attendance data. I want to calculate total number of school days between startdate and End date .I could write sql for startdate and End Date but eventhough i couldnot get it in the sameline. Startdate takes one line and End Date takes another line. I want to have all in one line

    So i am blank to calculate for count between startdate and end date

    I want Like

    studentno Startdate EndDate numberofdaysattended

    942531 9/10/2008 6/24/2008 178

    start date indicate as S

    End Date indicate as E

    I attached excel sheet for your review

    Thanks

    Ram

  • It works fine but it provides 288 days between 2 days. I am trying to calculate just student working dates between S and E . It is actually 178 days.

    I will email you if i have questions thanks for quick answer.

    Actually i got excel output from 3 tables . After i saw your sql i created new view and applying your workaround.

    Thanks

    Ram

  • Hi,

    I have an issue, i just want to find school days between S and E for the students. I added school days column but it didnot work. in my case statement '-' means holiday. so i want to find difference between S and E without '-'. it gives an error. could you please help me out . the Sql is

    select t1.dim_dtu_student_no,t1.date startdate,t2. date enddate, datediff(dd,t1.date,t2.date) attended_Days, datediff(dd,case when attendance not in('-') then t1.date else null end, case when attendance not in('-') then t2.date else null) school_days

    from studentattendanceionfo t1 join studentattendanceionfo t2 on t1.Dim_stu_Student_no=t2.Dim_stu_student_no and t1.Attendance='S' and t2.Attendance='E'

    Thanks

    Ram

  • ramaanujamr (9/23/2008)


    Hi,

    I have an issue, i just want to find school days between S and E for the students. I added school days column but it didnot work. in my case statement '-' means holiday. so i want to find difference between S and E without '-'. it gives an error. could you please help me out . the Sql is

    select t1.dim_dtu_student_no,t1.date startdate,t2. date enddate, datediff(dd,t1.date,t2.date) attended_Days, datediff(dd,case when attendance not in('-') then t1.date else null end, case when attendance not in('-') then t2.date else null) school_days

    from studentattendanceionfo t1 join studentattendanceionfo t2 on t1.Dim_stu_Student_no=t2.Dim_stu_student_no and t1.Attendance='S' and t2.Attendance='E'

    Thanks

    Ram

    I'm sorry, I just now looked at the excel file you attached. I don't think this is the best strategy, you will need to use some type of aggregate functionality.

    here is something I have in mind:

    select tab1.dim_dtu_student_no,

    Sum(case when tab1.attendance not in '-' then 1 else 0 end) no_of_days_no_holiday,

    Count(*) total_days

    from studentattendanceionfo tab1

    join

    (select t1.dim_dtu_student_no

    ,t1.date startdate

    ,t2. date enddate

    from studentattendanceionfo t1

    join studentattendanceionfo t2

    on t1.Dim_stu_Student_no=t2.Dim_stu_student_no

    and t1.Attendance='S' and t2.Attendance='E') tab2

    on tab1.dim_dtu_student_no = tab2.dim_dtu_student_no

    and tab1.date >= tab2.startdate and tab1.date <= tab2.enddate

    group by tab1.dim_dtu_student_no

    Since i don't have the schema, I can't verify any syntax errors, but that should get you to where you're trying to go...

  • Hi,

    Thank you very much . You did what i want. It works fine. I will email you if i have an issue again

    Thanks

    Ram

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply