Select Row whose Column expression value is Valid

问题:

I have a Table whose Column has an Expression as its Value. I want to write a Single Select Query to check if the Expression in that Column holds TRUE.

Here is My Table:

CREATE TABLE #TEMP (FinYear VARCHAR(9), TaxType VARCHAR(50), Rate NUMERIC(10,4), SlabExpression VARCHAR(50))  
INSERT INTO #TEMP  
VALUES ('2014-2015', 'SURCHARGE', 0.00, '#<=10000000'),  
       ('2014-2015', 'SURCHARGE', 10.00, '#>10000000'),  
       ('2014-2015', 'Education Cess', 3.00, '#<=10000000'),  
       ('2014-2015', 'Education Cess', 3.00, '#>10000000')  

SELECT * FROM #TEMP  
DROP TABLE #TEMP

My Income value now is 1200000 which obviously falls under the slab - '#>10000000' on which I intend to apply Surcharge and Education Cess rates.

But, How do I find the Correct Slab with a Single Query.

I know I can do that using a Cursor. But, I do not want to use a Cursor here. I need a Single Query.

Note: This Table has Two SlabExpression Values in one Column
ie. '#<=10000000' AND '#>10000000'

I want to Write a Single SELECT Query to get the two Rows for which the SlabExpression Holds True.
ie. I want the Result as the two Rows with SlabExpression '#>10000000'

Basically, I want a Query something Like

SELECT * FROM #TEMP WHERE SlabExpression Holds True

回答1:

SELECT *, 
    CASE SUBSTRING(SlabExpression, PATINDEX('%[<,=,>]%', SlabExpression), PATINDEX('%[0-9]%', RIGHT(SlabExpression, LEN(SlabExpression) - PATINDEX('%[<,=,>]%', SlabExpression))))
        WHEN '<=' THEN 
            CASE WHEN CAST(LEFT(SlabExpression, PATINDEX('%[<,=,>]%', SlabExpression) -1) AS INT) <= CAST(RIGHT(SlabExpression, LEN(SlabExpression) - PATINDEX('%[<,=,>]%', SlabExpression) -1) AS INT) THEN 'True'
            ELSE 'False' END
        WHEN '=<' THEN 
            CASE WHEN CAST(LEFT(SlabExpression, PATINDEX('%[<,=,>]%', SlabExpression) -1) AS INT) <= CAST(RIGHT(SlabExpression, LEN(SlabExpression) - PATINDEX('%[<,=,>]%', SlabExpression) -1) AS INT) THEN 'True'
            ELSE 'False' END
        WHEN '>=' THEN 
            CASE WHEN CAST(LEFT(SlabExpression, PATINDEX('%[<,=,>]%', SlabExpression) -1) AS INT) >= CAST(RIGHT(SlabExpression, LEN(SlabExpression) - PATINDEX('%[<,=,>]%', SlabExpression) -1) AS INT) THEN 'True'
            ELSE 'False' END
        WHEN '=>' THEN 
            CASE WHEN CAST(LEFT(SlabExpression, PATINDEX('%[<,=,>]%', SlabExpression) -1) AS INT) >= CAST(RIGHT(SlabExpression, LEN(SlabExpression) - PATINDEX('%[<,=,>]%', SlabExpression) -1) AS INT) THEN 'True'
            ELSE 'False' END
        WHEN '>' THEN 
            CASE WHEN CAST(LEFT(SlabExpression, PATINDEX('%[<,=,>]%', SlabExpression) -1) AS INT) > CAST(RIGHT(SlabExpression, LEN(SlabExpression) - PATINDEX('%[<,=,>]%', SlabExpression) ) AS INT) THEN 'True'
            ELSE 'False' END
        WHEN '<' THEN 
            CASE WHEN CAST(LEFT(SlabExpression, PATINDEX('%[<,=,>]%', SlabExpression) -1) AS INT) < CAST(RIGHT(SlabExpression, LEN(SlabExpression) - PATINDEX('%[<,=,>]%', SlabExpression) ) AS INT) THEN 'True'
            ELSE 'False' END
        WHEN '=' THEN 
            CASE WHEN CAST(LEFT(SlabExpression, PATINDEX('%[<,=,>]%', SlabExpression) -1) AS INT) = CAST(RIGHT(SlabExpression, LEN(SlabExpression) - PATINDEX('%[<,=,>]%', SlabExpression) ) AS INT) THEN 'True'
            ELSE 'False' END
        ELSE null
        END AS Value
 FROM #TEMP  

回答2:

Here is one way to solve this.
Tested and verified on Sql server 2012, for some reason I can't seem to make it work on sql fiddle. However, to the best on my knowledge, I didn't use anything that is not supported on Sql server 2008.

DECLARE @Value int = 1200000

;WITH CTE AS (
    SELECT  FinYear, 
            TaxType, 
            Rate, 
            SlabExpression, 
            @Value As Value1,
            REPLACE(LEFT(SlabExpression, PATINDEX('%[0-9]%', SlabExpression)-1), '#', '') As Operator,
            CAST(RIGHT(SlabExpression, LEN(SlabExpression)-PATINDEX('%[0-9]%', SlabExpression)+1) As int) As Value2
    FROM #TEMP
), 
CTE2 AS (
    SELECT  FinYear, 
            TaxType, 
            Rate, 
            SlabExpression,
            CASE Operator 
                WHEN '<' THEN
                    CASE WHEN Value1 < Value2 THEN 1 END
                WHEN '<=' THEN
                    CASE WHEN Value1 <= Value2 THEN 1 END
                WHEN '>' THEN
                    CASE WHEN Value1 > Value2 THEN 1 END
                WHEN '>=' THEN
                    CASE WHEN Value1 >= Value2 THEN 1 END
            END As IsTrue
    FROM CTE
)

SELECT  FinYear, 
        TaxType, 
        Rate, 
        SlabExpression
FROM CTE2 
WHERE IsTrue = 1

Results:

FinYear     TaxType                Rate           SlabExpression
----------- ---------------------- -------------- --------------
2014-2015   SURCHARGE              0.0000         #<=10000000
2014-2015   Education Cess         3.0000         #<=10000000

Explanations: I've used the first cte to break down the SlabExpression to operator and value, and the second cte to use that operator and value against the variable value.

回答3:

I think these SQL useful to you.

SELECT * FROM #TEMP WHERE SlabExpression = '1200000>10000000'

Thank you.