User Profile

KaliKraft · Nov 29 '07, 05:06 PM

WRENCH #3

Than you for all your help so far but now i would like to get these types of results.

Sample Data:

Results:

Code:

4, 22.00, null,  '2007-10-10'
4, 7.00,  null,  '2007-10-12'
5, null,  23.50, '2007-10-10'
6, 6.35,  13.42, '2007-10-12'

I get the sample data by using this select:

[code=sql]
select fldDriverHours, fldDriverID,...

KaliKraft · Nov 20 '07, 04:55 PM

This is what I came up with:

[code=mysql]
SELECT a.empid employee, driving, helping
FROM (SELECT empid,fldTicket ID, sum(driver_hrs) driving FROM emp LEFT OUTER JOIN work on empid=driver GROUP BY empid) a JOIN
(SELECT empid,fldTicket ID, sum(helper_hrs) helping FROM emp LEFT OUTER JOIN work on empid=helper GROUP BY empid) b on a.empid=b.empid
inner join tblTicket t on a.fldTicketID...

KaliKraft · Nov 20 '07, 04:35 PM

Thank you it works great.

Now I need to add a wrench.

I have to filter it using a date range like my first post. On the "work" table there is a date that i need to filter by. Either for a date or a date range or for a year.

Hopefully i can edit it. I will reply if i have any issues.

Thank you,
Jordon...

KaliKraft · Nov 16 '07, 11:05 PM

I have tried a few more things and now I have a select that returns all the hours for all the employees.

Code:

select w.fldDriverHours, w.flddriverid, w.fldHelperHours, w.fldHelperid,t.flddate
From tblTicket t inner join tblWork w on w.fldTicketID = t.fldTicketID
where fldDate >= '2007-10-10' AND fldDate <= '2007-10-13'

I get these results:

Code:

15.00, 4, 15.00, 5, '2007-10-10'

...

KaliKraft · Nov 16 '07, 10:37 PM

**Sorry for late reply, i didn't get notified by email**

No My query is only giving the results of the employee of the ID that I send in as a parameter.

I can't do that. See a employeeId is linked to a DriverID or a HelperID or both.

The result i get from a join are way wrong.

I will include the table structure:

tblEmployee
- fldEmployeeID...

KaliKraft · Nov 9 '07, 04:56 PM

I edited my original post with what i have so far.

Currently I am doing the select shown over and over again for each employee (takes time, especially once they do it for 30+ employees), I would like to get it as one select with the results.

Thanks,
Jordon...

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Select Sum on two fields using 2 key fields