Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012

    Unanswered: SUM Multiply based on column entry

    SELECT EmployeeId, PiecemealType,SUM(PiecemealQty) /100 AS TotalTrays, ActivityId
    FROM SR.dbo.PayTrays
    WHERE WorkDate > '2012-01-01'
    AND WorkDate < '2012-12-31'
    GROUP BY EmployeeId, PiecemealType, ActivityId
    ORDER BY EmployeeId, PiecemealType

    PiecemealType is the type of tray
    1= 15 Items per tray
    2= 12 Items per tray
    3= 8 Items per tray
    4= 6 Items per tray

    I'm trying to work out a bonus for 1 cent per item worked. The divide by 100 moves the total from cent to dollar and the SUM is adding all weekly values together.

    I just need to work out how to multiply the the SUM value for trays by the 4 different PiecemealType values. If it was static i could just add *15 to my SUM.

  2. #2
    Join Date
    Apr 2012
    I do not know if I understand your question correctly, but try:

    SUM(PiecemealQty) / 100 * 
    CASE PiecemealType
        WHEN 1 THEN 15
        WHEN 2 THEN 12
        WHEN 3 THEN 8
        ELSE 6
    END AS TotalTrays
    Hope this helps.

  3. #3
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    To make it easier, have a table of piecemeal type values and units per tray. Then you can include this table in your query and reference the units per tray column in the calculation.

    This allows you to update the units per tray values and apply new piecemeal types without having to amend the query again.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts