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
dBforums
>
Help with CASE
04-16-10, 16:51
Linear Mode
