asked on

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

vdr1620

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/

lisa_mc

ASKER

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

lisa_mc

ASKER

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

ASKER CERTIFIED SOLUTION

vdr1620

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

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

vdr1620

just use a where clause in your CTE to eliminate NULLS at the end

>>>> WHERE description IS NOT NULL

lisa_mc

ASKER

hi vdr1620

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

Thanks again

lisa_mc

ASKER

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

lisa_mc

ASKER

thanks for the solution very helpful and explained well