July 18, 2018 at 11:47 am
I am joining several tables together to do get pricing (a column) based on a code value (a column) and a modifier (a column). With the final left join, what I'm trying to do with the case statement is to pass the null value or '26' that is found in the modifier column so I get the correct price. Why would this case statement not work? I keep getting the '26' price value, but none of the price values for the null modifier and price in that row record.
If I run the case statement as its own select statement in a new query it does return '26' or null as expected.
Thank you in advance!
select
Proc_CD_Urology.urology as [Procedure Code], --List the codes for Urology Procedures
INS1.[rate] as [Insurance1], --show their cost for the code
INS2.[professionalfee] as [Insurance2], --show their cost for the code
INS3.[Non-Facility Fee] as [Insurance3], --show their cost for the code
INS4.[OfficeFee] as [Insurance4] --show their cost for the code
from Proc_CD_Urology
left join --Show the code from the urology code list and match that code to code in the INS1 table and th
INS1 on Proc_CD_Urology.urology = INS1.Code
and INS1.Modifier = '26'
left join
INS2 on Proc_CD_Urology.urology = INS2.Code
and INS2.Modifier = '26'
left join
INS3 on Proc_CD_Urology.urology = INS3.Code
and INS3.Modifier = '26'
left join
INS4 on Proc_CD_Urology.urology = INS4.Code
and INS4.Modifier =
(case
when INS4.Modifier = '26' then INS4.modifier
else null
end)
order by [Procedure Code] asc
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
July 18, 2018 at 11:55 am
Please elaborate a little: in the case where INS4.Modifier <> '26', what do you want to happen?
Perhaps you are looking for this?left join
INS4 on Proc_CD_Urology.urology = INS4.Code
WHERE
INS4.Modifier = '26' or INS4.Modifier IS NULL
order by [Procedure Code] asc
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 18, 2018 at 12:22 pm
I think that the problem is that you are expecting NULL = NULL to be true when it's actually UNKNOWN. I think what you want for the final join is left join
INS4 on Proc_CD_Urology.urology = INS4.Code
AND ( INS4.Modifier = '26' or INS4.Modifier IS NULL )
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 18, 2018 at 1:25 pm
Phil Parkin - Wednesday, July 18, 2018 11:55 AMPlease elaborate a little: in the case where INS4.Modifier <> '26', what do you want to happen?
Perhaps you are looking for this?left join
INS4 on Proc_CD_Urology.urology = INS4.Code
WHERE
INS4.Modifier = '26' or INS4.Modifier IS NULL
order by [Procedure Code] asc
Yes, definitely, to elaborate in the case that
INS4.Modifier <> '26'
in the record I would like the query to use that record and ignore the rest of the modifiers. And there are only 10 or so codes that have a modifer of 26, 27, and NULL. All other records with a code and modifiers are NULL. Otherwise where the
INS4.Modifier IS NULL
in the record I would like the query to use that record.
The raw data that was imported for INS4 has three distinct "options" of data in the modifer column - 26, 27 or NULL.
Right now the possible solutions presented with INS4.Modifier = '26' or INS4.Modifier IS NULL is returning both as if the OR operater is acting as an AND.
I'm wondering if I need to replace the modifiers that are NULL with a value of 0 or something instead of NULL from the raw data? I won't need the records where the 27 is the modifier, but in the future I will. so I'm tryin to figure this out now instead of just removing the records I don't need, because I feel that's the easy way out. 🙂
I will make a sample set of data if needed tomorrow.
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
July 18, 2018 at 1:51 pm
usererror - Wednesday, July 18, 2018 1:25 PMPhil Parkin - Wednesday, July 18, 2018 11:55 AMPlease elaborate a little: in the case where INS4.Modifier <> '26', what do you want to happen?
Perhaps you are looking for this?left join
INS4 on Proc_CD_Urology.urology = INS4.Code
WHERE
INS4.Modifier = '26' or INS4.Modifier IS NULL
order by [Procedure Code] ascYes, definitely, to elaborate in the case that
INS4.Modifier <> '26'in the record I would like the query to use that record and ignore the rest of the modifiers. And there are only 10 or so codes that have a modifer of 26, 27, and NULL. All other records with a code and modifiers are NULL. Otherwise where the
INS4.Modifier IS NULLin the record I would like the query to use that record.
The raw data that was imported for INS4 has three distinct "options" of data in the modifer column - 26, 27 or NULL.
Right now the possible solutions presented withINS4.Modifier = '26' or INS4.Modifier IS NULLis returning both as if the OR operater is acting as an AND.I'm wondering if I need to replace the modifiers that are NULL with a value of 0 or something instead of NULL from the raw data? I won't need the records where the 27 is the modifier, but in the future I will. so I'm tryin to figure this out now instead of just removing the records I don't need, because I feel that's the easy way out. 🙂
I will make a sample set of data if needed tomorrow.
First off, the value of INS4.Modifier cannot be both 26 and NULL in any single row of data, so your statement that the OR is acting like an AND is false.
What you are seeing tells me that your join criteria may have a problem as it is returning multiple rows of data that match the criteria INS4.Modifier = '26' or INS4.Modifier IS NULL.
July 18, 2018 at 2:01 pm
Lynn Pettis - Wednesday, July 18, 2018 1:51 PMWhat you are seeing tells me that your join criteria may have a problem as it is returning multiple rows of data that match the criteria INS4.Modifier = '26' or INS4.Modifier IS NULL.
I agree with this assessment.
If your INS4 table has multiple rows, including one with NULL modifier and one with 26 modifier, which of the two INS4 rows do you want to select?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 18, 2018 at 2:50 pm
Here is my suggestion. Start looking at your data. No filters (meaning no WHERE clause) and make sure the result set is what you are expecting. You may also want to look at it in pieces making sure that as you add each table the result set is what you expect prior to filtering. Once you know that you are correctly pulling all the data, then start working on the filtering to ensure you get the correct results.
July 19, 2018 at 5:35 am
Lynn Pettis - Wednesday, July 18, 2018 1:51 PMFirst off, the value of INS4.Modifier cannot be both 26 and NULL in any single row of data, so your statement that the OR is acting like an AND is false.
What you are seeing tells me that your join criteria may have a problem as it is returning multiple rows of data that match the criteria INS4.Modifier = '26' or INS4.Modifier IS NULL.
Yes, my description was not clear. The INS4.Modifier is not both 26 and NULL in any single row that is impossible. I'll make up a test set of data today and post it. I should have just done it the first time around. :pinch:
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
July 19, 2018 at 10:28 am
usererror - Wednesday, July 18, 2018 11:47 AMI am joining several tables together to do get pricing (a column) based on a code value (a column) and a modifier (a column). With the final left join, what I'm trying to do with the case statement is to pass the null value or '26' that is found in the modifier column so I get the correct price. Why would this case statement not work? I keep getting the '26' price value, but none of the price values for the null modifier and price in that row record.If I run the case statement as its own select statement in a new query it does return '26' or null as expected.
Thank you in advance!
select
Proc_CD_Urology.urology as [Procedure Code], --List the codes for Urology Procedures
INS1.[rate] as [Insurance1], --show their cost for the code
INS2.[professionalfee] as [Insurance2], --show their cost for the code
INS3.[Non-Facility Fee] as [Insurance3], --show their cost for the code
INS4.[OfficeFee] as [Insurance4] --show their cost for the code
from Proc_CD_Urologyleft join --Show the code from the urology code list and match that code to code in the INS1 table and th
INS1 on Proc_CD_Urology.urology = INS1.Code
and INS1.Modifier = '26'
left join
INS2 on Proc_CD_Urology.urology = INS2.Code
and INS2.Modifier = '26'
left join
INS3 on Proc_CD_Urology.urology = INS3.Code
and INS3.Modifier = '26'
left join
INS4 on Proc_CD_Urology.urology = INS4.Code
and INS4.Modifier =
(case
when INS4.Modifier = '26' then INS4.modifier
else null
end)order by [Procedure Code] asc
If I understand you correctly, you later stated that your JOIN condition brings back both rows, but what you want is to prioritize the 26 over the NULL. If that's correct, the following code should help:WITH INS4_LIMITED AS (
SELECT *,
ROW_NUMBER()
OVER(PARTITION BY INS4.Code
ORDER BY
CASE
WHEN INS4.Modifier IS NULL THEN 2
WHEN INS4.Modifier = '26' THEN 1
ELSE 999
END) AS RowNum
FROM INS4
)
SELECT
PCU.urology AS [Procedure Code], --List the codes for Urology Procedures
INS1.rate AS Insurance1, --show their cost for the code
INS2.professionalfee AS Insurance2, --show their cost for the code
INS3.[Non-Facility Fee] AS Insurance3, --show their cost for the code
INS4.OfficeFee AS Insurance4 --show their cost for the code
FROM Proc_CD_Urology AS PCU
LEFT OUTER JOIN INS1 --Show the code from the urology code list and match that code to code in the INS1 table AND the
ON Proc_CD_Urology.urology = INS1.Code
AND INS1.Modifier = '26'
LEFT OUTER JOIN INS2
ON Proc_CD_Urology.urology = INS2.Code
AND INS2.Modifier = '26'
LEFT OUTER JOIN INS3
ON Proc_CD_Urology.urology = INS3.Code
AND INS3.Modifier = '26'
LEFT OUTER JOIN INS4_LIMITED AS I4L
ON Proc_CD_Urology.urology = I4L.Code
AND I4L.RowNum = 1
ORDER BY PCU.urology ASC;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 19, 2018 at 12:01 pm
sgmunson - Thank you, that did exactly what I was looking for. I would have never gotten that on my own. I am going to see if I can expand on that WITH statement.
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
July 19, 2018 at 12:28 pm
usererror - Thursday, July 19, 2018 12:01 PMsgmunson - Thank you, that did exactly what I was looking for. I would have never gotten that on my own. I am going to see if I can expand on that WITH statement.
Thanks for the feedback. The WITH set of stuff is referred to as a CTE, or Common Table Expression. You can have multiple such expressions, separated by commas, like this:WITH Query1 AS (
SELECT T.col1, T.col2, T.col3
FROM Table1 AS T
WHERE T.DateChanged >= '20180701'
),
Query2 AS (
SELECT T.col1, T.col2, T.col3
FROM Table2 AS T
WHERE T.DateChanged >= '20180601'
AND T.DateChanged < '20180701'
)
SELECT Q1.col1, Q1.col2, Q1.col3 + Q2.col3 AS col3
FROM Query1 AS Q1
INNER JOIN Query2 AS Q2
ON Q1.col1 = Q2.col1
AND Q1.col2 = Q2.col2
ORDER BY Q1.col1, Q1.col2;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 19, 2018 at 12:58 pm
Thanks, now I know what CTE stands for when I see it here. Also, CTE is apparently not covered in my beginners guide to SQL. I'm only 3/4 of the way through my book, so guess I know what to look for in my next book!
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
July 19, 2018 at 1:05 pm
usererror - Thursday, July 19, 2018 12:58 PMThanks, now I know what CTE stands for when I see it here. Also, CTE is apparently not covered in my beginners guide to SQL. I'm only 3/4 of the way through my book, so guess I know what to look for in my next book!
Keep posting here and reading others' questions & answers and you'll learn faster than from any book.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 19, 2018 at 1:19 pm
Phil Parkin - Thursday, July 19, 2018 1:05 PMusererror - Thursday, July 19, 2018 12:58 PMThanks, now I know what CTE stands for when I see it here. Also, CTE is apparently not covered in my beginners guide to SQL. I'm only 3/4 of the way through my book, so guess I know what to look for in my next book!Keep posting here and reading others' questions & answers and you'll learn faster than from any book.
And to add to what Phil says, even if you don't post an answer you should try answering questions as well. Compare what you develop to what others post in response. This will help your learning as well.
July 24, 2018 at 7:31 am
Steve,
This may be a silly question, but what is the significance of '_LIMITED' that you appended to the original table name "INS4" so it was 'INS4_LIMITED' on the subquery you made containing the WITH clause?
Thanks,
Mark
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply