Sum straight and overtime in single Access query

I have an Access table containing timecard records for jobs:

JobID  HoursWorked  Rate
101    1.25
101    0.75         OT
102    0.33         DT
101    0.50
103    2.00

I want a query that returns a single record for each JobID that has summed the HoursWorked field. If [Rate] = "OT" then HoursWorked should be multiplied by 1.5, or in the case of DT, multiplied by 2.

The result would look like this:

JobID  TotalHoursWorked
101    2.875
102    0.66
103    2.00

I came up with this query which successfully SUMS the different types of Rate :

SELECT JobID, Sum(HoursWorked) AS TotalHoursWorked
FROM Timecards
GROUP BY JobID, Rate;

But I'm stuck at how to multiply the different rates and return a single total by JobID.

---

Use a Switch expression to compute the multiplier for each Rate. Multiply HoursWorked by those values and Sum() it all up in a GROUP BY

SELECT
    t.JobID,
    Sum(t.HoursWorked * Switch(t.Rate='OT',1.5,t.Rate='DT',2,True,1)) AS TotalHoursWorked
FROM Timecards AS t
GROUP BY t.JobID;

That query returned your expected result when tested with your data in Access 2010.

Here is the Switch expression broken out and formatted. It returns the value from the first expression/value pair where the expression evaluates as True, and ignores any remaining pairs. The final expression/value pair ( True, 1 ) returns 1 for any Rate which does not match 'OT' or 'DT':

Switch(
        t.Rate='OT', 1.5,
        t.Rate='DT', 2,
        True, 1
    )

---

You can use the IIF function to conditionally determine what to aggregate:

SELECT 
  JobID, 
  SUM(IIF(rate = 'OT', HoursWorked * 1.5, IIF(rate = 'DT' ,HoursWorked * 2.0, HoursWorked))) AS TotalHoursWorked
FROM Timecards
GROUP BY JobID;

The logic is iif(boolean test, value if true, value if false)

Since you have two conditions to test you have to nest the iif-expressions so if rate = 'OT' then multiply by 1,5, else check if rate = 'DT' and if so multiply with 2, or use the value as is if rate is something else.

Also, when you use aggregate functions you should not group by the column(s) you aggregate. If you do you'll most likely not get the result you want.

---

试试这个：

SELECT 
    JobID, 
    Sum(HoursWorked * CASE 
                        WHEN rate = 'OT' THEN 1.5 
                        WHEN rate = 'DT' THEN 2
                        ELSE 1
                    END
        ) AS TotalHoursWorked
FROM Timecards
GROUP BY JobID

链接地址: http://www.djcxy.com/p/62070.html

上一篇: 插入缺少的行（s）

下一篇: 在单个Access查询中直接和加班