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.
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]