asked on

Counting Time and Selecting Records over a certain length of time

Hi Everyone

Quick background working on a script to show all students absent more than 4 hrs per day. Some students can be absent for several reasons per day including class cancelled or sick if this is the case then I want to show each different absent type and the time each student was absent for each absent type (I use my cte to join to my table to do this)

I am at the stage now where I can get each absent type for each student eg from spreadsheet we can see that this student is authorised absent for 6 hours and class cancelled for 45 minutes giving a total time absent per day of 6hrs 45 mins. What I would like to do is
1) Add another column to my output which calculates the total time absent (see spreadsheet)
2) Then only display the students where the total time absent >= 4 hours

I have attached my code which shows how I join my cte and table
Hope someone can help

Thanks in advance example-output.xls

SELECT t.*, c.absent_desc, CONVERT(VARCHAR(5), c.absent, 108) AS time_absent
FROM #students AS t
INNER JOIN cte1a AS c ON 
t.student_id = c.student_id
and t.course_code = c.course_code
and t.course_period = c.course_period
and t.week_no = c.week_no
and t.date = c.date

Open in new window

Chris McGuigan

Try the following code but it does mean you can't include all the fields from the CTE table.

"Group By" allows you to aggregate data easily and you use "Having" instead of "Where" to filter using the aggregates themselves.

Hope that helps?

SELECT C.student_id, c.absent_desc, CONVERT(VARCHAR(5), Sum(c.absent), 108) AS time_absent
    FROM #students AS t
        INNER JOIN cte1a AS c ON 
            t.student_id = c.student_id
            and t.course_code = c.course_code
            and t.course_period = c.course_period
            and t.week_no = c.week_no
            and t.date = c.date
    Group By C.Student_Id, C.Absent_Desc
    Having Sum(C.Absent) >= 4

Open in new window

lisa_mc

ASKER

hi Chrismc

Sorry should have gave you code in Cte

because now it is giving an error that datetime is invalid in the sum operator

; With cte1a 
AS
(
SELECT distinct student_id, course_code, course_period, week_no, absence_code, absent_desc,
date, 
DateAdd(n, SUM(CASE WHEN absent_desc = 'Authorised Absence' THEN DATEDIFF(minute, start_time,  end_time)  
WHEN absent_desc = 'Class Cancelled' THEN DATEDIFF(minute, start_time,  end_time) 
WHEN absent_desc = 'Unauthorised Absence' THEN DATEDIFF(minute, start_time,  end_time) 
WHEN absent_desc = 'Late' THEN late_minutes
ELSE 0
END), 0) AS 'Absent'
From #code

WHERE absent_desc IS NOT NULL

group by student_id, course_code, course_period, week_no, absence_code, absent_desc, date
)

Open in new window

Chris McGuigan

It's an amount of time so just remove the convert, it's not necessary.

Cheers
Chris

lisa_mc

ASKER

do you mean like this?

SELECT t.*, c.absent_desc, c.absent AS time_absent,
Sum(c.absent) AS total_absent

If so I am still getting the error
Msg 8117, Level 16, State 1, Line 73
Operand data type datetime is invalid for sum operator.
which takes me back to the cte1a

lisa_mc

ASKER

also this is how I work out start and end time - maybe this is causing a problem?

MIN(CONVERT(datetime, start_time)) AS 'start_time',
MAX(CONVERT(datetime, End_Time)) AS 'end_time',

Im so confused :-(

OnALearningCurve

Hi Lisa,

Are you still working on an answer for this? If so, I might have an option.

I think the issue is because you are trying to handle the total Absent value you have calculated as a datetime value rather than just a number of minutes. Take a look at the attached code I have added where I have altered your query slightly to make your 'Absent' value just display a total number of minutes (I hope!).

You can then select only the students where this value breaks the four hour mark by using

WHERE 'Absent' > 240

I hope this make sense (and works).

If not, let me know.

Mark.

; With cte1a 
AS
(
SELECT distinct student_id, course_code, course_period, week_no, absence_code, absent_desc,
date, 
SUM(CASE WHEN absent_desc IN ('Authorised Absence','Class Cancelled','Unauthorised Absence') THEN DATEDIFF(minute, start_time,  end_time) 
WHEN absent_desc = 'Late' THEN late_minutes
ELSE 0
END) AS 'Absent'
From #code

WHERE absent_desc IS NOT NULL

group by student_id, course_code, course_period, week_no, absence_code, absent_desc, date
)

Open in new window

ASKER CERTIFIED SOLUTION

lisa_mc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lisa_mc

ASKER

sorry menat to say I have come up with a solution

lisa_mc

ASKER

have accepted my own answer and have explained my solution above

OnALearningCurve

No problem.

Glad you got it sorted.