counting and grouping in sql based on a values

Hi experts need to amend the code I have to cater for one more thing and I am really stuck as to how to proceed - so im hoping someone can help me

I have attached a spreadsheet which shows the classes that one student attended for one day and also the marks made against that student for the one day.

In the code below I am calculating the time_absent where attendance_type = 'AA' or 'AB' but I need to change this to calculate the time_absent based on each different descriptions in the description field (shown in spreadsheet).  Using the example from the spreadsheet I need two different descriptions being authorised absence and class cancelled and I need a seperate line for each description.  

Help Appreciated example-output.xls
SELECT acad_period, student_id, student_name, student_surname, stage_code,
	course_code, course_period, course_desc, dept_code, dept_desc,
	campus_id, campus_desc,
	date, week_no, [start_time], [end_time], 
	convert(varchar, convert(int,floor([Total Hours])))+':'+
	replicate('0',(2 - len(convert (varchar, convert(int,([Total Hours] - floor([Total Hours])) * 60.0)))))
	+convert (varchar, convert(int,([Total Hours] - floor([Total Hours])) * 60.0)) as total_hours,
	(CASE WHEN [total breaks]  < 0 THEN '-' ELSE '' END) + convert(varchar, convert(int,ABS([total breaks] -([total breaks] %1))))+':'+ 
	RIGHT(('0'+convert(varchar,  convert(int,60*ABS([total breaks] %1)))),2) as total_breaks,
	CONVERT(VARCHAR(5), [Late], 108) AS late,
	CONVERT(VARCHAR(5), [Absent], 108) AS time_absent,
	CONVERT(VARCHAR(5), [Unmarked], 108) AS not_marked,
	CONVERT(VARCHAR(5), [Present], 108) AS time_present

FROM
(
	SELECT  acad_period, student_id, student_name, student_surname, stage_code,
		course_code, course_period, course_desc, dept_code, dept_desc,
		campus_id, campus_desc, 
		Date,week_no, 
		MIN(CONVERT(datetime, start_time)) AS 'start_time', 
		MAX(CONVERT(datetime, End_Time)) AS 'end_time',
		ROUND(cast((datediff(mi, MIN(start_time), MAX(end_time))  / 60.0) as FLOAT),2) AS 'Total Hours',

		(DATEDIFF(minute, MIN(start_time), MAX(end_time)) - SUM(DATEDIFF(minute, start_time, end_time))) / 60.0 AS 'Total Breaks',
		
		DateAdd(n, SUM(CASE WHEN attendance_type = 'LA' THEN late_minutes ELSE 0 END), 0) AS 'Late',
		DateAdd(n, SUM(CASE WHEN attendance_type IN ('AA', 'AB') THEN DATEDIFF(minute, start_time,  end_time) ELSE 0 END), 0) AS 'Absent',
		DateAdd(n, SUM(CASE WHEN attendance_type IS NULL THEN DATEDIFF(minute, start_time,  end_time) ELSE 0 END), 0) AS 'Unmarked',
		DateAdd(n, SUM(CASE WHEN attendance_type IN ('PR', 'LA') THEN DATEDIFF(minute, start_time,  end_time) ELSE 0 END) 
			- SUM(CASE WHEN attendance_type = 'LA' THEN late_minutes ELSE 0 END), 0) AS 'Present'
	

	FROM stud_table
	GROUP BY acad_period, student_id, student_name, student_surname, stage_code,
	course_code, course_period, course_desc, dept_code, dept_desc,
	campus_id, campus_desc, Date, week_no
) X

Open in new window

LVL 3
lisa_mcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vdr1620Commented:
I guess use a CTE to get the Distinct Course_period, AbsenceCode , Description and then make an Inner join to the existing SQL by including the Description from this CTE to the Grouping


; With CTE
AS
(
SELECT distinct Course_period,absence_code, description From stud_table
)

Your Existing SQL

and then last make an Inner join


Refer to this link for example

http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/
0
lisa_mcAuthor Commented:
hi vdr1620

Thanks very much for the reply
Sadly our PC's are down so I cant test it till hopefully this afternoon but will reply back asap
0
lisa_mcAuthor Commented:
hi again

ok everything is coming out ok but I have one problem
- as in spreadsheet the time absent is 6:45 hrs but adding in the new descriptions (using the cte) I am getting two lines out one for class cancelled and one for authorised absence but in both lines the time absent = 6hrs 45 mins how do i get it to split the absences accordinly eg class cancelled = 45 mins
and authorised absence = 6 hours
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

vdr1620Commented:
Well, you can remove this statement from existing sql and use it as part of CTE ..so that it calculates the field correctly

DateAdd(n, SUM(CASE WHEN attendance_type IN ('AA', 'AB') THEN DATEDIFF(minute, start_time,  end_time) ELSE 0 END), 0) AS 'Absent',

So, it would be like

; With CTE
AS
(
SELECT distinct Course_period,absence_code, description, DateAdd(n, SUM(CASE WHEN attendance_type IN ('AA', 'AB') THEN DATEDIFF(minute, start_time,  end_time) ELSE 0 END), 0) AS 'Absent' From stud_table
Group By Course_period,absence_code, description
)



You can also add other fields to the CTE,if you want to calculate the values by the description
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lisa_mcAuthor Commented:
hi again

Got that working but I have one thing I need sorted I am getting an extra line out for description and time_absent so for the example on the spreadsheet I am getting the output

time_absent      description
NULL              NULL
00:45      Class Cancelled
06:00:00      Authorised Absence

how do I get rid of the one line with the null values

Thanks
0
vdr1620Commented:
just use a where clause in your CTE to eliminate NULLS at the end

>>>> WHERE description IS NOT NULL
0
lisa_mcAuthor Commented:
hi vdr1620

Sorry I got sidetracked with something else yesterday so I am testing it now will reply back asap

Thanks again
0
lisa_mcAuthor Commented:
I have attached code below which shows me joining my data to the cte to get all the different absence types.

As I am going to be creating a stored procedure I insert the output into my table created by my sp.

What I would finally like to do is only inserted students who have been absent greater than an amount of time eg 3 hrs so although the absences are broken down into each absent type I need to amend the code below to say only insert into table where total time_absent >= 3 hours

Can you help me?  Thanks
insert into table created from sp

SELECT t.*, c.absent_desc, CONVERT(VARCHAR(5), c.absent, 108) AS time_absent 

FROM #train 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


order by t.student_id, t.week_no, t.date, t.start_time, t.end_time

Open in new window

0
lisa_mcAuthor Commented:
thanks for the solution very helpful and explained well
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

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.