I'm writing a SQL Command for Crystal Reports and I'm still new to SQL, so I'm having trouble with the following scenario. I've got a SUM function to add up the costs of a production job to get me the actual costs. I want to formulate the margin from the invoice amount and subtract the actual cost from this SUM function to populate another field in my report. When the related invoice has a return applied, it shows as a credit memo type rather than a typical invoice. I want to add a CASE function to the Margin SQL statement.
I'm getting this CASE statement to work to get my actual costs:
,(CASE WHEN t1.[Type] = 'CM' THEN '0.00' ELSE (SELECT SUM(Act_Labor+Act_Material+Act_Service+Act_Labor_Burden+Act_Machine_Burden+Act_GA_Burden) FROM Job WHERE Top_Lvl_Job = t0.Job) END)as Act_Cost
I want to incorporate this CASE statement into my formula to get my Margin as seen below:
,(SELECT ISNULL(t0.Amount, 0) - SUM(Act_Labor+Act_Material+Act_Service+Act_Labor_Burden+Act_Machine_Burden+Act_GA_Burden) FROM Job WHERE Top_Lvl_Job = t0.Job) as Margin
I keep getting errors if I try to use the CASE statement in my Margin formula. I'm not sure where I'm going wrong with the sequence or if my syntax has multiple errors.
I've tried the following syntax, but keep getting errors:
,(SELECT ISNULL(t0.Amount,0) - (CASE WHEN t1.[Type] = 'CM' THEN '0.00' ELSE (SUM(Act_Labor+Act_Material+Act_Service+Act_Labor_Burden+Act_Machine_Burden+Act_GA_Burden) FROM Job WHERE Top_Lvl_Job = t0.Job) END))as Margin