CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Kumar
Kumar

Reputation: 167

How can I simplify this Query? I need to compare a temp variable value with a column value of multiple rows

I need to compare a temp variable value with a column value of multiple rows and perform Operations based on that.

  | intSeqID    | Value |
 ----------------------------
     1          | 779.40
     2          | 357.38
     3          |  NULL
     4          |  NULL
     5          |  NULL
     6          |  NULL
     7          |  NULL
     8          |  NULL
     9          |  NULL
    10          |  NULL

    DECLARE @tmpRange NUMERIC(5,2)
    SELECT @tmpRange = 636

Here I need to compare the value @tmpRange with Value from TABLE and perform operations based on it.

      IF((@tmpRange < (select ISNULL(Value,0) from @tableA intSeqID=1)) AND 
      (@tmpRange< (select ISNULL(Value,0) from @tableA where intSeqID=2))) AND 
      (@tmpRange< (select ISNULL(Value,0) from @tableA where intSeqID=3))) AND 
      (@tmpRange< (select ISNULL(Value,0) from @tableA where intSeqID=9))) AND 
      (@tmpRange< (select ISNULL(Value,0) from @tableA where intSeqID=10)))
       BEGIN
       SELECT 'All'
       END 
       ELSE IF ((@tmpRange < (select ISNULL(Value,0) from @tableA intSeqID=1)) AND 
      (@tmpRange< (select ISNULL(Value,0) from @tableA where intSeqID=2))) AND 
      (@tmpRange< (select ISNULL(Value,0) from @tableA where intSeqID=3))) AND 
      (@tmpRange< (select ISNULL(Value,0) from @tableA where intSeqID=9))))
          BEGIN
          SELECT '10'
          END
       END

How can i simplify this query to compare values. Or is there any other way to pick the values of multiple rows and compare the same with temp variable.

Upvotes: 1

Views: 105

Answers (3)

KtX2SkD
KtX2SkD

Reputation: 752

You want to find the biggest record in Value, who is also smaller than your variable, correct?

--DECLARE @tableA TABLE (intSeqID tinyint, [Value] decimal(5,2))
--INSERT INTO @tableA SELECT 1, 400 UNION SELECT 2, 300 UNION SELECT 3, 200
--DECLARE @tmpRange decimal(5,2) = 250

SELECT TOP 1 *
FROM (
    SELECT TOP 1 CONCAT('', intSeqID) AS intSeqID -- Can't UNION int to varchar.
    FROM @tableA
    WHERE ISNULL([Value], 0) < @tmpRange
    ORDER BY intSeqID ASC
    UNION
    SELECT 'All' AS [?]
) AS T
ORDER BY intSeqID ASC

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

Here is one fairly simple way to do it:

Create and populate sample table (Please save us this step in your future questions)

DECLARE @tableA as table
(
    intSeqID int identity(1,1),
    Value numeric(5,2)
)

INSERT INTO @tableA VALUES
(779.40),
(357.38),
(256.32),
(NULL)

Declare and populate the variable:

DECLARE @tmpRange numeric(5, 2) = 636

The query:

;WITH CTE AS
(
    SELECT TOP 1 intSeqId
    FROM @TableA
    WHERE @tmpRange < ISNUll(Value, 0)
    ORDER BY Value 
) 

SELECT  CASE WHEN intSeqId = 
            (
                SELECT TOP 1 intSeqId
                FROM @TableA
                ORDER BY ISNUll(Value, 0) 
            ) THEN 'All'
       ELSE CAST(intSeqId as varchar(3))
       END 
FROM CTE

Result: 1.

See a live demo on rextester.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

We can try to refactor your query using aggregations. We almost get away with no subquery except for just one, which is needed to distinguish the two conditions.

SELECT
    CASE WHEN SUM(CASE WHEN @tmpRange < Value THEN 1 ELSE 0 END) = 4 AND
    @tmpRange < (SELECT Value FROM @tableA WHEREA intSeqID = 10)
         THEN 'All'
         WHEN SUM(CASE WHEN @tmpRange < Value THEN 1 ELSE 0 END) = 4
         THEN '10'
         ELSE 'NONE' END AS label
FROM @tableA
WHERE intSeqID IN (1, 2, 3, 9)

Upvotes: 0

Related Questions