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

LVL 3
lisa_mcAsked:
Who is Participating?
 
lisa_mcAuthor Commented:
Hi Mark

Im getting this error

(16 row(s) affected)

(3 row(s) affected)
Msg 245, Level 16, State 1, Line 106
Conversion failed when converting the varchar value 'Absent' to data type int.

I have actually gave up with a solution was just too busy the other day to post it.  

1) At the start I had the total time absent worked out  then as I wanted to give the time for each absent type I moved this code into the cte so to add the extra column total absent I also added this to the main piece of code
2) When I join my table to my cte (t being table and c representating cte)
I use the code below to check total time absent being over 4 hours

where datepart(hh,convert(datetime,t.tot_time_absent)) >= 4 and
datepart(mi,convert(datetime,t.tot_time_absent)) >= 00

this works perfect so I will accept my own answer as no other solution has been given

0
 
chrismcCommented:
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

0
 
lisa_mcAuthor Commented:
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

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

Cheers
Chris
0
 
lisa_mcAuthor Commented:
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

0
 
lisa_mcAuthor Commented:
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 :-(
0
 
OnALearningCurveCommented:
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

0
 
lisa_mcAuthor Commented:
sorry menat to say I have come up with a solution
0
 
lisa_mcAuthor Commented:
have accepted my own answer and have explained my solution above
0
 
OnALearningCurveCommented:
No problem.

Glad you got it sorted.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.