I have a query with about 20 columns of employee data that shows information for all of my active employees. I now need to add 2 specific columns for goal amounts for certain deductions. Problem is, the goal amounts and deduction amounts are with in the same table structure. I.E. Normally I'd do a report for Deduction=HFSA1 and show the goal amount, and then do another report for Deduction=DFSA1 and show it's goal amount. I need one report to show all of it, in separate columns, and where an employee doesn't have the deduction, show $0.00.
Here's an example below of what I want it to look like when I'm done.
John Smith has a DFSA1 goal of $1,000 but no HFSA1 goal.
Betty Crocker has a DFSA1 goal of $2,000 and a HFSA1 goal of $5,0000.
Jane Doe doesn't have any goals.
Employee ID | Employee Name | Other Employee Info | HFSA1 Benefit Goal Amount | DFSA1 Benefit Goal Amount |
12345 | John Smith | Regular Employee | $0.00 | $1000.00 |
54321 | Betty Crocker | VIP Employee | $5,000.00 | $2,000.00 |
98765 | Jane Doe | Regular Employee | $0.00 | $0.00 |
Any help on this is greatly appreciated.
Can you try using a case statement, one for each goal amount...something like this for two columns
Total(Case [Deduction]='DFSA1' then [goal amount] end) [DFSA1 Benefit Goal Amount]
&
Total(Case [Deduction]='HFSA1' then [goal amount] end) [HFSA1 Benefit Goal Amount]
Regards
Raj
Can you try using a case statement, one for each goal amount...something like this for two columns
[DFSA1 Benefit Goal Amount]
-----------------------------------
Total(Case when [Deduction]='DFSA1' then [goal amount] end)
[HFSA1 Benefit Goal Amount]
-----------------------------------
Total(Case when [Deduction]='HFSA1' then [goal amount] end) [HFSA1 Benefit Goal Amount]
Regards
Raj