Something like this maybe?
select employee.last_name, employee.first_name, department.name, sum(
timeentry.hours) as tot_hrs
from timeentry
join employee on timeentry.emp_id = employee.emp_id
join department on employee.department_id = department.department_id
where date( apply_date) > '2006-04-01'
group by department.name, employee.last_name, employee.first_name
order by employee.last_name, department.name
On 4/28/06, Matthew Jarvis <[EMAIL PROTECTED]> wrote:
My lack of SQL theory is showing.... <g>
This SQL statement:
select timeentry.emp_id, employee.last_name, employee.first_name,
timeentry.dept_id, department.name, sum( timeentry.hours) as tot_hrs
from timeentry, employee,department where date( apply_date) >
'2006-04-01' and timeentry.emp_id = employee.emp_id and
timeentry.dept_id = department.dept_id group by timeentry.emp_id,
timeentry.dept_id, department.name, employee.last_name,
employee.first_name order by employee.last_name, department.name;
Gives me:
emp_id | last_name | first_name | dept_id | name | tot_hrs
--------+-------------+------------+---------+-----------------+---------
74 | Casler | Chris | 1 | Production Line | 154
74 | Casler | Chris | 5 | Tooling | 0.75
99 | Cheek | Mike | 28 | 5S | 0.5
99 | Cheek | Mike | 21 | 75%Mfg/25%CSvc | 7.75
99 | Cheek | Mike | 3 | Customer Servic | 0.75
99 | Cheek | Mike | 27 | Improvements | 0.75
99 | Cheek | Mike | 1 | Production Line | 134.75
99 | Cheek | Mike | 26 | X-Training | 18.5
88 | Coon | Monty | 3 | Customer Servic | 2
88 | Coon | Monty | 27 | Improvements | 0.5
88 | Coon | Monty | 1 | Production Line | 156.75
Where I want to end up is more or less like:
last_name | Production Line | Tooling | (all the other depts)| Total |
----------+-----------------+---------+----------------------+--------+
Casler | 154 | 0.75 | | 154.75 |
Cheek | etc.
In other words, make each Dept name a column in the result, with the
hours as the column data....
I've seen this done before but can't remember how it works...
Oh, and I'd prefer to do it all in postgres w/o having to resort to
programming it....
Oh, and I'm in a hurry.... <g>
Any thoughts?
--
Matthew S. Jarvis
IT Manager
Bike Friday - "Performance that Packs."
www.bikefriday.com
541/687-0487 x140
[EMAIL PROTECTED]
_______________________________________________
EUGLUG mailing list
[email protected]
http://www.euglug.org/mailman/listinfo/euglug
_______________________________________________
EUGLUG mailing list
[email protected]
http://www.euglug.org/mailman/listinfo/euglug
