I have a situation where users are classified to a particular level of service, but our budget is for a set number, and I need to work out the difference.
So I have created a query to show me the numbers, and then I want the number of rooms showed to compare, and then I want to do a Variance between them. BUT, I can't get it to work.
Here is the query:
--Count how many are in each level select [Rescall Stockcode] ,count(pd.NHI) as [No. clients] from (select pd.nhi_number as NHI ,pr.STOCKCODE as [Rescall Stockcode] from patient_rooms as pr join patient_details as pd on pd.PATIENT_ID=pr.PATIENT_ID where pr.STOCKCODE in ('MHL2PN','MHL3PN','MHL4PN') and (pr.END_DATE>=@StartDate or pr.OPEN_STAY='y') ) as [Count Residents] ,case when pr.stock_code='MHL2PN' then 22 when pr.stock_code='MHL3PN' then 16 else 18 end as [No. Rooms] ,[Count Residents]-[No. Rooms] as [Variance] from patient_rooms as pr join patient_details as pd on pd.PATIENT_ID=pr.PATIENT_ID group by [Count Residents].[Rescall Stockcode]
I have red error lines appearing under the second line's pd.NHI and also the first use of CASE and then the AS in the Case statement.
The error is:
Msg 156, Level 15, State 1, Line 60 Incorrect syntax near the keyword 'case'.
I am not sure what I am doing wrong. Hopefully someone can help.