I have 3 nvarchar columns user_3, user_4 and description. I am setting yes and no flag. If the value in description column is equal or in between user_3 and User4 then set the flag to 'N' else set the flag to 'Y'.
Here is the SQL script I wrote so far. it works in some instances but not always. See the image with results. it worked on line #1 but didn't work on line # 6 for example. What am I doing wrong?
SELECT [B].USER_3,[B].USER_4,A.DESCRIPTION,
(case when Isnumeric(A.DESCRIPTION) <> 1 then 'Y'
else case when (CASE WHEN Isnumeric(A.DESCRIPTION) = 1 then
cast(A.DESCRIPTION AS decimal(10,5)) else 0 end)
between ( CASE WHEN Isnumeric([B].USER_4) = 1 then
cast([B].USER_4 AS decimal(10,5)) else 0 end) and
(CASE WHEN Isnumeric([B].USER_3) = 1 then cast([B].USER_3 AS decimal(10,5)) else 0 end)
then 'N' else 'Y' end end) as Flagset
from A , B
Here is the screenshot of the results
The issue is with your use of BETWEEN
as per the docs:
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.
Because you don't know whether USER_3
or USER_4
is the higher limit or the lower limit, you need to test both ways.
Note: For this sort of query I prefer to pre-calculate all the values (using CROSS APPLY
in this case) I need. It makes it much easier to follow and debug.
SELECT USER_3, USER_4, [DESCRIPTION]
, CASE WHEN ISNUMERIC([DESCRIPTION]) <> 1 THEN 'Y' ELSE
CASE WHEN CASE WHEN ISNUMERIC([DESCRIPTION]) = 1 THEN CAST([DESCRIPTION] AS decimal(10,5)) ELSE 0 END BETWEEN CASE WHEN ISNUMERIC(USER_4) = 1 THEN CAST(USER_4 AS decimal(10,5)) ELSE 0 END AND
CASE WHEN ISNUMERIC(USER_3) = 1 THEN CAST(USER_3 AS decimal(10,5)) ELSE 0 END
THEN 'N' ELSE 'Y' END END AS Flagset
, CASE WHEN DNUMERIC <> 1 THEN 'Y' ELSE CASE WHEN DESCRIPTIOND BETWEEN USER_4D AND USER_3D OR DESCRIPTIOND BETWEEN USER_3D AND USER_4D THEN 'N' ELSE 'Y' END END CorrectedFlagSet
FROM (VALUES
('1.395','1.385','1.390')
, ('22.025','41.425','22')
, ('22.025','41.425','23.025')
) AS X (USER_3, USER_4, [DESCRIPTION])
CROSS APPLY (VALUES (
CASE WHEN ISNUMERIC(USER_3) = 1 THEN CAST(USER_3 AS decimal(10,5)) ELSE 0 END
, CASE WHEN ISNUMERIC(USER_4) = 1 THEN CAST(USER_4 AS decimal(10,5)) ELSE 0 END
, CASE WHEN ISNUMERIC([DESCRIPTION]) = 1 THEN CAST([DESCRIPTION] AS decimal(10,5)) ELSE 0 END
, CASE WHEN ISNUMERIC([DESCRIPTION]) = 1 THEN 1 ELSE 0 END
)) AS Y (USER_3D, USER_4D, DESCRIPTIOND, DNUMERIC);
Returns:
USER_3 | USER_4 | DESCRIPTION | Flagset | CorrectedFlagSet |
---|---|---|---|---|
1.395 | 1.385 | 1.390 | N | N |
22.025 | 41.425 | 22 | Y | Y |
22.025 | 41.425 | 23.025 | Y | N |
I'm sure I don't have to mention that you should really be storing this data in numeric form in the first place as it will perform better and save you lots of future issues.
And well laid out queries with consistent casing also helping understand and debug them.
Finally providing a minimal reproducible example with sample data, your query and your desired result as shown here makes it much easier for people to assist.