Solved

# Counting Time and Selecting Records over a certain length of time

Posted on 2011-10-13
238 Views
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

``````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
``````
0
Question by:lisa_mc

LVL 18

Expert Comment

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
``````
0

LVL 3

Author Comment

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
)
``````
0

LVL 18

Expert Comment

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

Cheers
Chris
0

LVL 3

Author Comment

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

LVL 3

Author Comment

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

LVL 10

Expert Comment

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
)
``````
0

LVL 3

Accepted Solution

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

LVL 3

Author Comment

sorry menat to say I have come up with a solution
0

LVL 3

Author Closing Comment

have accepted my own answer and have explained my solution above
0

LVL 10

Expert Comment

No problem.

0

## Featured Post

### Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
how to add IIS SMTP to handle application/Scanner relays into office 365.