a problem with case

[nedrob]

I am relatively new to SQL programming and have been working on the following script, I would like to insert the following case statement into latter script.

'Temp YTD GM%' = ROUND(CASE WHEN SUM([Temp Billing MTD])
= 0 THEN '0' ELSE SUM([Temp GM$ MTD] / [Temp Sales MTD])
END, 2)

I am using the alias (from the script below)to acomplish the above script. How can I do this without creating seperate query? Any help would be greatly appriciated.

SELECT DISTINCT
Specialty, Branch,
'Temp Hrs WTD Last WK' = SUM(CASE WHEN DATEPART(WW,
CURRENT_TIMESTAMP) = DATEPART(WW, Pay_Ending_Date)
THEN [Bill Hours] ELSE 0 END),
'Temp Sales WTD' = SUM(CASE WHEN DATEPART(WW,
CURRENT_TIMESTAMP) = DATEPART(WW, Pay_Ending_Date)
THEN [Bill Amount] ELSE 0 END),
[Temp Sales MTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
THEN [Bill Amount] ELSE 0 END),
[Temp Sales YTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date)
THEN [Bill Amount] ELSE 0 END),
'Temp GM$ Last WK' = SUM(CASE WHEN DATEPART(WW,
CURRENT_TIMESTAMP) = DATEPART(WW, Pay_Ending_Date)
THEN [Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden]) ELSE 0 END),
[Temp GM$ MTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
THEN [Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden]) ELSE 0 END),
[Temp GM$ YTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date)
THEN [Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden]) ELSE 0 END),
'Perm Sales WTD' = SUM(CASE WHEN DATEPART(WW,
CURRENT_TIMESTAMP) = DATEPART(WW, Pay_Ending_Date)
THEN [Perm_GM] ELSE 0 END),
[Perm Sales MTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
THEN [Perm_GM] ELSE 0 END),
[Perm Sales YTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) THEN [Perm_GM] ELSE 0 END),
'GMP MTD' = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
THEN ([Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden])) + Perm_GM ELSE 0 END),
'GMP YTD' = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date)
THEN ([Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt]
+ [Burden])) + Perm_GM ELSE 0 END),
[Perm Falloffs MTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
AND [Type] = 'C' THEN [Perm_GM] ELSE 0 END),
'Perm Falloffs YTD' = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
[Type] = 'C' THEN [Perm_Gm] ELSE 0 END)
FROM Master_Business
GROUP BY Specialty, Branch
ORDER BY Specialty DESC

 

[tlbroadbent]

If you use the second script as a sub-query, you can reference the aliases in the outer query. I have some additional suggestions. Use double quotes (&quot or brackets ([]) to delimite column or alias names. Use getdate() instead of CURRENT_TIMESTAMP to obtain the current system datetime. The DISTINCT keyword is not required when using the group by clause and every item in the select list is part of an aggregate.

I came up with the following query based on the info you provided.

NOTE: I couldn't find an alias by the name of [Temp Billing MTD] in the big script. I have assumed you meant [Temp Sales MTD] and built the query accordingly. If my assumption is wrong then you'll need to make the appropriate change.

SELECT *, [Temp YTD GM%] = ROUND(CASE WHEN [Temp Sales MTD]=0 THEN 0 ELSE [Temp GM$ MTD]/[Temp Sales MTD] END, 2) FROM
(
/* sub-query begins with next line */
SELECT
Specialty,Branch,
[Temp Hrs WTD Last WK]=SUM(CASE
WHEN DATEPART(WW,getdate())=DATEPART(WW,Pay_Ending_Date) THEN [Bill Hours]
ELSE 0 END),
[Temp Sales WTD]=SUM(CASE
WHEN DATEPART(WW,getdate())=DATEPART(WW,Pay_Ending_Date) THEN [Bill Amount]
ELSE 0 END),
[Temp Sales MTD]=SUM(CASE
WHEN YEAR(getdate())=YEAR(Pay_Ending_Date)
AND MONTH(getdate())=MONTH(Pay_Ending_Date) THEN [Bill Amount]
ELSE 0 END),
[Temp Sales YTD]=SUM(CASE
WHEN YEAR(getdate())=YEAR(Pay_Ending_Date) THEN [Bill Amount]
ELSE 0 END),
[Temp GM$ Last WK]=SUM(CASE
WHEN DATEPART(WW,getdate())=DATEPART(WW,Pay_Ending_Date)
THEN [Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt] + [Burden])
ELSE 0 END),
[Temp GM$ MTD]=SUM(CASE
WHEN YEAR(getdate())=YEAR(Pay_Ending_Date)
AND MONTH(getdate())=MONTH(Pay_Ending_Date)
THEN [Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt] + [Burden])
ELSE 0 END),
[Temp GM$ YTD]=SUM(CASE
WHEN YEAR(getdate())=YEAR(Pay_Ending_Date)
THEN [Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt] + [Burden])
ELSE 0 END),
[Perm Sales WTD]=SUM(CASE
WHEN DATEPART(WW,getdate())=DATEPART(WW,Pay_Ending_Date) THEN [Perm_GM]
ELSE 0 END),
[Perm Sales MTD]=SUM(CASE
WHEN YEAR(getdate())=YEAR(Pay_Ending_Date)
AND MONTH(getdate())=MONTH(Pay_Ending_Date) THEN [Perm_GM]
ELSE 0 END),
[Perm Sales YTD]=SUM(CASE
WHEN YEAR(getdate())=YEAR(Pay_Ending_Date) THEN [Perm_GM]
ELSE 0 END),
[GMP MTD]=SUM(CASE
WHEN YEAR(getdate())=YEAR(Pay_Ending_Date)
AND MONTH(getdate())=MONTH(Pay_Ending_Date)
THEN [Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt] + [Burden]) + Perm_GM
ELSE 0 END),
[GMP YTD]=SUM(CASE
WHEN YEAR(getdate())=YEAR(Pay_Ending_Date)
THEN [Bill Amount] - ([Gross Pay] + [Federal] + [State] + [WorkCompAmt] + [Burden]) + Perm_GM ELSE 0 END),
[Perm Falloffs MTD]=SUM(CASE
WHEN YEAR(getdate())=YEAR(Pay_Ending_Date)
AND MONTH(getdate())=MONTH(Pay_Ending_Date)
AND [Type]='C' THEN [Perm_GM]
ELSE 0 END),
[Perm Falloffs YTD]=SUM(CASE
WHEN YEAR(getdate())=YEAR(Pay_Ending_Date) AND [Type]='C' THEN [Perm_Gm]
ELSE 0 END)
FROM Master_Business
GROUP BY Specialty,Branch
) As a
/* sub-query ended with line above */
ORDER BY Specialty DESC

Please let me know how this works and if you have any questions. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[belkin]

Terry,
this was a big help thanks!