SQL Query rounding issue

I am using pyodbc to fetch total quantity of a product based on a certain criteria called “Strength”. The “Strength” column also has string value for some records, so it is a varchar column.

The user enters details like brand, product type, product line, date range, minimum quantity (in this case, 12), and strength range

This is my query:

SELECT SUM(CAST([Qty] AS decimal(10, 2))) AS Qty 
FROM (
    SELECT 
        [Brand], 
        [ProdType], 
        [Lot], 
        CAST([Strength] AS DECIMAL(10,4)) AS [Strength], 
        [ProductLine], 
        [Size], 
        [Stage], 
        [Customer], 
        [PackedOn], 
        [Qty], 
        [RefreshedBy], 
        [RefreshedOn] 
    FROM SalesData 
    WHERE 
        (isnumeric([Strength]) = 1) 
        AND [Stage]='WIP' 
        AND [PackedOn]>='2018-06-03' 
        AND [PackedOn]<='2020-06-03' 
        AND [Brand]='ABC' 
        AND [ProductLine]='DEF' 
        AND [Size]='15' 
        AND [Qty]>='12.0' 
        AND [Strength]>=0.2 
        AND [Strength]<=0.4 
        AND [ProdType] Is Null
) as outputdata

This is my table:

ID  Brand   ProdType    Lot   Strength  ProductLine   Size    Stage   Province  PackedOn    Qty  

1   ABC     NULL      XXXXXXX     0.16       DEF       15        WIP    NULL    2018-12-07  1200

This is the create statement

CREATE TABLE [dbo].[SalesData](
    [ID] [int] NOT NULL,
    [Brand] [varchar](max) NOT NULL,
    [ProdType] [varchar](max) NULL,
    [Lot] [varchar](max) NOT NULL,
    [Strength] [varchar](max) NOT NULL,
    [ProductLine] [varchar](max) NOT NULL,
    [Size] [varchar](max) NOT NULL,
    [Stage] [varchar](max) NOT NULL,
    [Province] [varchar](max) NULL,
    [PackedOn] [date] NOT NULL,
    [Qty] [float] NOT NULL,
    [RefreshedBy] [varchar](max) NULL,
    [RefreshedOn] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

My problem is that this query results in a Quantity of 1200, even though it is outside the strength range. I am using SQL Server Management Studio V 18.4. How do I solve this?

Answer

In your WHERE clause you should use.

TRY_CAST([Strength] AS DECIMAL(10,4))>=0.2 AND TRY_CAST([Strength] AS DECIMAL(10,4))<=0.4

Because sql queries start working from where clauses( and joins) then executes other parts. SELECT is the least important part and if you only use CAST in your select it will be only useful for printing data as your preferred format.

SELECT SUM(CAST([Qty] AS decimal(10, 2))) AS Qty FROM 
(SELECT [Brand], [ProdType], [Lot], CAST([Strength] AS DECIMAL(10,4)) AS [Strength], [ProductLine], [Size], [Stage], [Customer], [PackedOn], [Qty], [RefreshedBy], [RefreshedOn] 
FROM SalesData 
WHERE (isnumeric([Strength]) = 1) AND [Stage]='WIP' AND [PackedOn]>='2018-06-03' 
AND [PackedOn]<='2020-06-03' AND [Brand]='ABC' AND [ProductLine]='DEF' 
AND [Size]='15' AND [Qty]>='12.0' AND TRY_CAST([Strength] AS DECIMAL(10,4))>=0.2 AND TRY_CAST([Strength] AS DECIMAL(10,4))<=0.4 AND [ProdType] Is Null) as outputdata