Select Sum on two fields using 2 key fields

[KaliKraft]

I have a tblWork that has a dozen fields in it.
It tracks hours worked for 2 employees at a time.
- fldDriverID
- fldDriverHours
- fldHelperID
- fldHelperHours

It also has a date field (fldDate) and a key to the ticket the work was performed on (fldTicketID).

So I assign the driver and the hours worked (1,15h) same for helper (1,13h)

What i want to do is do a payroll select that will get me the DriverHours and HelperHours for each employee. that is easy to do but the hard part is that the driver can be the helper and viseversa.
*
SampleData:

  fldTicketID        1         2         3         4         5
fldDate        2007-2-1  2007-2-1  2007-2-1  2007-2-2  2007-2-3
fldDriverID        10       11        10        11        13
fldDriverHours     8        8         8        7.5         8
fldHelperID        11       10        13        13        10
fldHelperHours     8        7.5        8         8          8    

Expected Results

DateRange = 2007-2-1 -> 2007-2-3

EmployeeID           10            11          13
DrivenHours           16           15.5         8
HelperHours          15.5           8           16
 

Any Ideas?

What I tried, got close

select
  (select sum(fldDriverHours)
    from tblWork
    where tblWork.fldTicketID = t.fldTicketID and fldDriverID = ?EmployeeID) as DrivenHours,
  (select sum(fldHelperHours)
    from tblWork
    where tblWork.fldTicketID = t.fldTicketID and fldHelperID = ?EmployeeID) as SwampHours
from tblWork w inner join tblTicket t on t.fldTicketID = w.fldTicketID
where t.fldDate >= '2007-10-10' and t.fldDate <= '2007-10-13'

[mwasif]

Wlecome to TSDN!

What you have made so far?

[KaliKraft]

Wlecome to TSDN!

What you have made so far?

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

[mwasif]

What I tried, got close

Your query giving the required results, right?

To get multiple results, replace the following condition with a JOIN to employee table and in WHERE clause provide the employee ids.

fldDriverID = ?EmployeeID

[KaliKraft]

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

Your query giving the required results, right?

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

To get multiple results, replace the following condition with a JOIN to employee table and in WHERE clause provide the employee ids.

fldDriverID = ?EmployeeID

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
- fldName

tblTicket
- fldTicketID
- fldDate

tblWork
- fldWorkID
- fldTicketID
- fldDriverID (fldEmployeeID)
- fldHelperID (fldEmployeeID)
- fldDriverHours
- fldHelperHours

Thanks,
Jordon

[KaliKraft]

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

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:

15.00, 4, 15.00, 5, '2007-10-10'
7.00,  4, 8.50,  5, '2007-10-10'
6.35,  6, 7.42,  6, '2007-10-12'
7.00,  4, 6.00,  6, '2007-10-12'
 

How do i group this in a way so i know total hours for each employee for what task they did? ( I wish i could change the database set up but i can't)

Expected Results:

4,   29,     NULL
5,   NULL,   23.5
6,   6.35,   13.42
 

Thanks,
Jordon

[pradeep kaltari]

Expected Results:

4,   29,     NULL
5,   NULL,   23.5
6,   6.35,   13.42
 

Thanks,
Jordon

Hi Jordon,
You can try something like the following:

select a.empid employee, driving, helping
from (select empid, sum(driver_hrs) driving from emp left outer join work on empid=driver group by empid) a join 
     (select empid, sum(helper_hrs) helping from emp left outer join work on empid=helper group by empid) b on a.empid=b.empid
 

plz change the code to match your tables
Hope this helps.

-Pradeep

[KaliKraft]

Hi Jordon,
You can try something like the following:

select a.empid employee, driving, helping
from (select empid, sum(driver_hrs) driving from emp left outer join work on empid=driver group by empid) a join 
     (select empid, sum(helper_hrs) helping from emp left outer join work on empid=helper group by empid) b on a.empid=b.empid
 

plz change the code to match your tables
Hope this helps.

-Pradeep

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]

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

This is what I came up with:

      SELECT a.empid employee, driving, helping
      FROM (SELECT empid,fldTicketID, sum(driver_hrs) driving FROM emp LEFT OUTER   JOIN work on empid=driver GROUP BY empid) a JOIN
           (SELECT empid,fldTicketID, 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 = t.fldticketID or b.fldTicketID = t.fldticketID
where t.fldDate =>= '2007-10-10' and t.fldDate <= '2007-10-12'
 

What do you think?

[pradeep kaltari]

This is what I came up with:

      SELECT a.empid employee, driving, helping
      FROM (SELECT empid,fldTicketID, sum(driver_hrs) driving FROM emp LEFT OUTER   JOIN work on empid=driver GROUP BY empid) a JOIN
           (SELECT empid,fldTicketID, 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 = t.fldticketID or b.fldTicketID = t.fldticketID
where t.fldDate =>= '2007-10-10' and t.fldDate <= '2007-10-12'
 

What do you think?

Hi KaliKraft,
I guess your query should work.

You can also use:

      SELECT a.empid employee, driving, helping
      FROM (SELECT empid,fldTicketID, sum(driver_hrs) driving FROM emp LEFT OUTER   JOIN work on empid=driver GROUP BY empid) a JOIN
           (SELECT empid,fldTicketID, 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 = t.fldticketID and t.fldDate >= '2007-10-10' and t.fldDate <= '2007-10-12'
 

- Pradeep

[KaliKraft]

WRENCH #3

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

Sample Data:

15.00, 4, 15.00, 5, '2007-10-10'
7.00,  4, 8.50,  5, '2007-10-10'
6.35,  6, 7.42,  6, '2007-10-12'
7.00,  4, 6.00,  6, '2007-10-12'
 

Results:

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:

select fldDriverHours,fldDriverID,   fldHelperHours,fldHelperId, fldDate
from tblWork inner join tblTicket on tblWork.fldTicketID = tblTicket.fldTicketID
order by fldDate
 

Thanks,
Jordon