[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Counting Time and Selecting Records over a certain length of time

Posted on 2011-10-13
10
Medium Priority
?
248 Views
Last Modified: 2012-08-14
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

0
Comment
Question by:lisa_mc
  • 6
  • 2
  • 2
10 Comments
 
LVL 18

Expert Comment

by:chrismc
ID: 36962629
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
 
LVL 3

Author Comment

by:lisa_mc
ID: 36962740
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
 
LVL 18

Expert Comment

by:chrismc
ID: 36963041
It's an amount of time so just remove the convert, it's not necessary.

Cheers
Chris
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 3

Author Comment

by:lisa_mc
ID: 36963128
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

by:lisa_mc
ID: 36963135
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

by:OnALearningCurve
ID: 37005599
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
 
LVL 3

Accepted Solution

by:
lisa_mc earned 0 total points
ID: 37006277
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

by:lisa_mc
ID: 37006284
sorry menat to say I have come up with a solution
0
 
LVL 3

Author Closing Comment

by:lisa_mc
ID: 37035259
have accepted my own answer and have explained my solution above
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 37006427
No problem.

Glad you got it sorted.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question