Results 1 to 2 of 2

Thread: Help with CASE

  1. #1
    Join Date
    Mar 2010
    Posts
    16

    Unanswered: Help with CASE

    Hey all -

    Here is what I basically trying to do...

    Code:
    SELECT SUM ( CASE t2.PaymentTypesID WHEN 1 or 2 or 3 or 4 THEN t1.Total ELSE 0 END ) AS Income
         , SUM( CASE t2.PaymentTypesID WHEN 8 THEN t1.Total ELSE 0 END ) AS FedTax
         , SUM( CASE t2.PaymentTypesID WHEN 9 OR 10 THEN t1.Total ELSE 0 END ) AS SocSec
         , SUM( CASE t2.PaymentTypesID WHEN 11 OR 12 THEN t1.Total ELSE 0 END ) AS Medicare
      FROM table1 AS t1
    INNER 
      JOIN table2 AS t2
        ON t2.PayCategoryID = t1.PayCategoryID
    But its not working right. It's not erroring, I get values - but Income/Soc/Med have the same value - and that's not right according to the table.

    I'm assuming there might be an issue with the OR part in the CASE statements.

    Any ideas?

    Here is a sample of Table 1
    Code:
    +-----------------+------------+---------------+----------+---------+-----------+
    | PaycheckItemsID | PaycheckID | PayCategoryID | Quantity | Total   | Calculate |
    +-----------------+------------+---------------+----------+---------+-----------+
    |            1577 |         63 |            16 |     2.82 | 3846.15 |         1 |
    |            1578 |         63 |            17 |     0.00 |    0.00 |         1 |
    |            1579 |         63 |            19 |     0.00 |    0.00 |         0 |
    |            1580 |         63 |            18 |     0.00 |    0.00 |         0 |
    |            1581 |         63 |            24 |     0.00 |    0.00 |         0 |
    |            1582 |         63 |            26 |     0.00 |    0.00 |         1 |
    |            1583 |         63 |            11 |     0.00 |    0.00 |         1 |
    |            1584 |         63 |             1 |     0.00 |    0.00 |         1 |
    |            1585 |         63 |             6 |     0.00 |  238.46 |         1 |
    |            1586 |         63 |             7 |     0.00 |  238.46 |         1 |
    |            1587 |         63 |             4 |     0.00 |   55.77 |         1 |
    |            1588 |         63 |             5 |     0.00 |   55.77 |         1 |
    |            1589 |         63 |             2 |     0.00 |  238.46 |         1 |
    |            1590 |         63 |             3 |     0.00 |  607.94 |         1 |
    |            1601 |         65 |            22 |     9.00 |  135.00 |         1 |
    and table 2
    Code:
    +---------------+--------------------------------+----------------+
    | PayCategoryID | Category                       | PaymentTypesID |
    +---------------+--------------------------------+----------------+
    |             1 | Advanced Earned Income Credit  |             19 |
    |             2 | Federal Unemployment           |              7 |
    |             3 | Federal Withholding            |              8 |
    |             4 | Medicare Company               |             12 |
    |             5 | Medicare Employee              |             11 |
    |             6 | Social Security Company        |             10 |
    |             7 | Social Security Employee       |              9 | 
    |           104 | Mike's Salary                  |              1 |     
    |           112 | Mike Test2                     |              1 |     
    |           113 | Hourly                         |              2 |       
    |           121 | Ohio State Withholding Tax     |             14 |
    |           122 | Ohio State Unemployment Tax    |             13 |
    |           123 | Salary                         |              1 |       
    |           124 | Child Support                  |              5 |     
    |           125 | Simple IRA                     |              6 |      
    |           126 | Simple IRA                     |              5 |      
    |           127 | Hourly - Overtime              |              2 |   
    |           128 | Hourly - Vacation              |              2 |   
    |           129 | Hourly - Holiday               |              2 |    
    |           130 | Georgia State Withholding Tax  |             14 |
    |           131 | Georgia State Unemployment Tax |             13 |
    |           132 | Snow Plowing                   |              2 |     
    |           133 | Bonus                          |              4 |        
    |           134 | Commission                     |              3 |      
    +---------------+--------------------------------+----------------+
    TIA
    Chris
    Last edited by dockraz; 04-16-10 at 17:58.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT SUM(CASE WHEN t2.PaymentTypesID IN (1,2,3,4)
                    THEN t1.Total ELSE 0 END ) AS Income
         , SUM(CASE WHEN t2.PaymentTypesID = 8 
                    THEN t1.Total ELSE 0 END ) AS FedTax
         , SUM(CASE WHEN t2.PaymentTypesID IN (9,10)
                    THEN t1.Total ELSE 0 END ) AS SocSec
         , SUM(CASE WHEN t2.PaymentTypesID IN (11,12)
                    THEN t1.Total ELSE 0 END ) AS Medicare
      FROM ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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