I have the following query result using group by with rollup:
+---------+-----------------+-------------+----------------+-------------+-------+
| Divison | Department | Section |Employee Name | Employee ID | Hours |
+---------+-----------------+-------------+----------------+-------------+-------+
| Assets | Asset Strategy | Not Defined | Monty Mouse | 480193 | 64.00 |
| Assets | Asset Strategy | Not Defined | Frank Flint | 480165 | 67.50 |
| Assets | Asset Strategy | Not Defined | | 480165 | 131.50|
| Assets | Asset Strategy | | | 480165 | 131.50|
| Assets | Event Centre | Not Defined | Sally Spoons | 800192 | 72.00 |
| Assets | Event Centre | Not Defined | Randolph Smith | 800199 | 37.50 |
| Assets | Event Centre | Not Defined | Petra Peters | 800195 | 64.00 |
| Assets | Event Centre | Not Defined | | 800195 | 173.50|
| Assets | Event Centre | | | 800195 | 173.50|
+---------+-----------------+-------------+----------------+-------------+-------+
What I want to be able to do is to stop the employee id from replicating in the rollup lines:
+---------+-----------------+-------------+----------------+-------------+-------+
| Divison | Department | Section |Employee Name | Employee ID | Hours |
+---------+-----------------+-------------+----------------+-------------+-------+
| Assets | Asset Strategy | Not Defined | Monty Mouse | 480193 | 64.00 |
| Assets | Asset Strategy | Not Defined | Frank Flint | 480165 | 67.50 |
| Assets | Asset Strategy | Not Defined | | | 131.50|
| Assets | Asset Strategy | | | | 131.50|
| Assets | Event Centre | Not Defined | Sally Spoons | 800192 | 72.00 |
| Assets | Event Centre | Not Defined | Randolph Smith | 800199 | 37.50 |
| Assets | Event Centre | Not Defined | Petra Peters | 800195 | 64.00 |
| Assets | Event Centre | Not Defined | | | 173.50|
| Assets | Event Centre | | | | 173.50|
+---------+-----------------+-------------+----------------+-------------+-------+
I've read other posts about using union to try to just match up the employee if from a non-rollup query, but this hasn't worked for me.
I've also read about using sub-select (wrapping) to get the employee id, but this has just lead to the same result.
My rollup statement groups by division, department, section and employee name. If I try to add employee id to this clause I get a rollup on every change of employee. I've also changed the order of the name and if fields and tried grouping by the id rather than the name, but this has just replicated the name in the same way the id is above.
Am I chasing the impossible dream here? Is it not actually possible to display the data in this way? Any suggestions would be greatly appreciated.
For those that would like the full query code, here it is:
select distinct
hr_func_desc('CD_DEPT_', p.department) as 'Department',
hr_func_desc('CD_DIVN_', p.division) as 'Division',
hr_func_desc('CD_SECT_', p.section) as 'Section',
pe.payroll_name as 'Employee Name',
pe.employee_id as 'Employee ID',
sum(ph.ordinary_hours) as 'Ordinary Hours'
from swpayroll.py_employees pe
left outer join swhr_rails.hr_employees e on e.id = pe.employee_id
left outer join swhr_rails.hr_employee_positions ep on pe.employee_id = ep.employee_id
left outer join swhr_rails.hr_positions p on ep.position_id = p.id
left outer join swpayroll.py_rep_hist_hours ph on pe.employee_id = ph.employee_id
left outer join swpayroll.py_hist_dedns_allowances al
on al.employee_id = pe.employee_id
and al.pay_date = ph.pay_date
and al.da_id in (33, 66, 67)
left outer join swpayroll.py_hist_totals pt on pt.employee_id = pe.employee_id and pt.pay_date = ph.pay_date
where ep.position_id = (select min(x.position_id) from swhr_rails.hr_employee_positions as x
where x.employee_id = pe.employee_id and x.position_end is null)
and ph.pay_date between '2012-10-21' and '2012-11-04'
group by
hr_func_desc('CD_DEPT_', p.department),
hr_func_desc('CD_DIVN_', p.division),
hr_func_desc('CD_SECT_', p.section),
pe.payroll_name with rollup;
Edited 1 time(s). Last edit at 01/22/2013 03:53PM by Brendon Sandrey.