Solved

# counting and grouping in sql based on a values

Posted on 2011-10-04
195 Views
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
``````
0
Question by:lisa_mc

LVL 16

Expert Comment

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
)

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

LVL 3

Author Comment

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

LVL 3

Author Comment

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

LVL 16

Accepted Solution

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

LVL 3

Author Comment

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

LVL 16

Expert Comment

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

>>>> WHERE description IS NOT NULL
0

LVL 3

Author Comment

hi vdr1620

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

Thanks again
0

LVL 3

Author Comment

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

LVL 3

Author Closing Comment

thanks for the solution very helpful and explained well
0

## Featured Post

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…