Sum by both Job code and Pay type

[dhborchardt]

I am trying to add together all the hours for each job code for Two different pay types. Each employee has a job code and there can be more than one employee with the same job code. Each job code can have one or two pay types, for example 'regular hours - "Productive hours"', or 'Sick/Vacation - "Non Productive Hours"' I need to sum all the non productive hours for a job code and all the non productive hours for the same job codes.

The query I have so far is pulling from two tables hence the many codes to one employee.

The columns in the query are
JobCode PayType hours


For example

Employee A with job code 300
Productive 32
Non 8

Employee b with job code 300
productive 40
non 0

Employee c with job code 310
Productive 20
Non 20

Employee d with jog code 315
productive 10
non 30

TOTALS
job code 300
productive 72
non 8

Job code 310
Productive 20
Non 20

Job code 315
Productive 10
non 30

 

[PHV]

What the actual SQL code of your query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhborchardt]

'Primary Earning code' is the same as 'Pay Type'. There is a third table involved to resolve the 'Primary Earning Types' down to two Pay Types.


SELECT DISTINCTROW dbo_PpTimeCards.JobCode, dbo_PpTimeCardEarnings.Hours, tblEarningMnemonics.[Primary Earning Code]
FROM (dbo_PpTimeCards INNER JOIN dbo_PpTimeCardEarnings ON dbo_PpTimeCards.TimeCardID = dbo_PpTimeCardEarnings.TimeCardID) INNER JOIN tblEarningMnemonics ON dbo_PpTimeCardEarnings.EarningMnemonic = tblEarningMnemonics.[Earning Mnemonic]
WHERE (((dbo_PpTimeCards.PayDateTime)=#2/8/2008#))
ORDER BY dbo_PpTimeCards.JobCode;

 

[PHV]

A starting point:

SELECT T.JobCode, M.[Primary Earning Code] AS PayType, Sum(E.Hours) AS TotalHours
FROM (dbo_PpTimeCards AS T
INNER JOIN dbo_PpTimeCardEarnings AS E ON T.TimeCardID = E.TimeCardID)
INNER JOIN tblEarningMnemonics AS M ON E.EarningMnemonic = M.[Earning Mnemonic]
WHERE T.PayDateTime = #2008-02-08#
GROUP BY T.JobCode, M.[Primary Earning Code]
ORDER BY 1, 2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhborchardt]

Thanks
You nailed first try.

 

[dhborchardt]

ONe more question if you don't mind?
I looked at it and the only thing I don't understand is the last Order By line:
ORDER BY 1,2;
What does it do?

 

[PHV]

Sort by the 1st and 2nd column.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhborchardt]

Thanks