Count between two days

Question

Post reply

Count between two days

ramaanujamr

Old Hand

Points: 366
More actions
September 23, 2008 at 8:43 am

#382783

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

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

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

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 1

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

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

Gabriel P SSCarpal Tunnel Points: 4289 More actions · Answer 2

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?

ramaanujamr Old Hand Points: 366 More actions · Answer 3

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 .

ramaanujamr Old Hand Points: 366 More actions · Answer 4

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

Gabriel P SSCarpal Tunnel Points: 4289 More actions · Answer 5

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

ramaanujamr Old Hand Points: 366 More actions · Answer 6

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

ramaanujamr Old Hand Points: 366 More actions · Answer 7

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

ramaanujamr Old Hand Points: 366 More actions · Answer 8

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

Gabriel P SSCarpal Tunnel Points: 4289 More actions · Answer 9

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

ramaanujamr Old Hand Points: 366 More actions · Answer 10

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