Using CASE

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.

The provided query looks totally garbled. Can you please explain the fields you use, and provide sample data with expected output of your sample data.
How to post a question

The basic problem is the CASE cannot come after the JOIN, You want something like this:

SELECT [Rescall Stockcode]
     , count(pd.NHI) AS [No. clients]
     , 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 (
     SELECT pd.nhi_number AS NHI
          , pr.STOCKCODE AS [Rescall Stockcode]
     FROM patient_rooms AS pr
     INNER 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]

FROM patient_rooms AS pr
INNER JOIN patient_details AS pd
     ON pd.PATIENT_ID = pr.PATIENT_ID
GROUP BY [Count Residents].[Rescall Stockcode]

but then there are more problems. e.g. you have

[Count Residents] - [No. Rooms] AS [Variance]

but [Count Residents] is an alias for a derived table and you can't use [No. Rooms]
in the SELECT list since that alias is not visible until the ORDER BY clause.

OK, thanks for the feedback. I am not sure then how I can use derived tables for the calculation?

Short answer: you can use a column in a derived table in an outer query, e,g,

select foo - bar
from (select one as foo, two as bar from sometable) myalias

but you can't write

select one as foo, two as bar, foo-bar
from sometable

since SQL cannot "see" the aliases foo and bar until after the select clause is processed. Recall the processing order:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER

so the ORDER clause is the first time that SQL can see your aliases. We use subqueries (derived tables) to get around that. In your case, CROSS APPLY might do the trick:

SELECT [Rescall Stockcode]
     , count(pd.NHI) AS [No. clients]
     , [Count Residents].???? - [No. Rooms] AS [Variance]
FROM (
     SELECT pd.nhi_number AS NHI
          , pr.STOCKCODE AS [Rescall Stockcode]
     FROM patient_rooms AS pr
     INNER 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]
CROSS APPLY
(
SELECT CASE 
          WHEN pr.stock_code = 'MHL2PN'
               THEN 22
          WHEN pr.stock_code = 'MHL3PN'
               THEN 16
          ELSE 18
          END AS [No. Rooms]
)

INNER JOIN patient_details AS pd
     ON pd.PATIENT_ID = pr.PATIENT_ID
GROUP BY [Count Residents].[Rescall Stockcode]

Note that you still have to specify the column name in [Count Residents]

Thank you for explaining this. I have not learned about the APPLY operator up till now, it's very useful, isn't it?

I used your code and also specified the column name for [Count Residents]

--Count how many are in each level
--This doesn't work
    SELECT [Rescall Stockcode]
         , count([Count Residents].NHI) AS [No. clients]
         , [Count Residents].NHI - [No. Rooms] AS [Variance]
    FROM (
         SELECT pd.nhi_number AS NHI
              , pr.STOCKCODE AS [Rescall Stockcode]
         FROM patient_rooms AS pr
         INNER 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]
    CROSS APPLY
    (
    SELECT CASE 
              WHEN pr.stock_code = 'MHL2PN'
                   THEN 22
              WHEN pr.stock_code = 'MHL3PN'
                   THEN 16
              ELSE 18
              END AS [No. Rooms]
    )
    INNER JOIN patient_details AS pd
         ON pd.PATIENT_ID = pr.PATIENT_ID
    
    GROUP BY [Count Residents].[Rescall Stockcode]
    ;

But I get red squiggly lines as shown:

and get this error message;

Msg 156, Level 15, State 1, Line 70
Incorrect syntax near the keyword 'INNER'.

OK -- this should do. Hard to test since I don't have your CREATE TABLE statements:

SELECT [Rescall Stockcode]
     , count(pd.NHI) AS [No. clients]
     , [Count Residents].???? - [No. Rooms] AS [Variance]
FROM (
     SELECT pd.nhi_number AS NHI
          , pr.STOCKCODE AS [Rescall Stockcode]
     FROM patient_rooms AS pr
     INNER JOIN patient_details AS pd
          ON pd.PATIENT_ID = pr.PATIENT_ID
	CROSS APPLY
	(
	SELECT CASE 
			  WHEN pr.stock_code = 'MHL2PN'
				   THEN 22
			  WHEN pr.stock_code = 'MHL3PN'
				   THEN 16
			  ELSE 18
			  END AS [No. Rooms]
	) myalias
     WHERE pr.STOCKCODE IN ('MHL2PN', 'MHL3PN', 'MHL4PN')
          AND (
               pr.END_DATE >= @StartDate
               OR pr.OPEN_STAY = 'y'
               )
     ) AS [Count Residents]


INNER JOIN patient_details AS pd
     ON pd.PATIENT_ID = pr.PATIENT_ID
GROUP BY [Count Residents].[Rescall Stockcode]

Is this anywhere near, what you @Margo are trying to accomplish?

 select [Rescall Stockcode]
      ,[No. clients]
      ,[No. clients]
      -case [Rescall Stockcode]
          when 'MHL2PN' then 22
          when 'MHL3PN' then 16
          else               18
       end
       as [Variance]
  from (select pr.STOCKCODE as [Rescall Stockcode]
              ,count(pd.nhi_number) as [No. clients]
          from patient_rooms as pr
               inner 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'
               )
         group by pr.STOCKCODE
       ) as [Count Residents]