How do you implement if/then logic in SQL

Question

I'm new to SQL and databases, so bear with me here.

I already know that this query isn't correct, but I think other developers here can understand what I'm trying to do. Any ideas on how to fix this query to get it to work?

         SELECT [LabTest], Count(*)
         FROM [UsersDB].[dbo].[vwUserLabTest]
         IF (LabTest='PTT') SET RangeMax=35
         IF (LabTest='CK') SET RangeMax=150
         IF (LabTest='Ca') SET RangeMax=10.1
         WHERE ResultValue>RangeMax
         GROUP By LabTest

I tried CASE WHEN ([LabTest]='PTT') THEN RangeMax=35 but that's throwing up errors too — Algorithmic Programmer
– Algorithmic Programmer, Commented Aug 20, 2017 at 1:24

Gordon Linoff · Accepted Answer · 2017-08-20 01:32:33Z

1

I think you want:

SELECT [LabTest], Count(*)
FROM [UsersDB].[dbo].[vwUserLabTest]
WHERE (LabTest = 'PTT' AND ResultValue > 35) OR
      (LabTest = 'CK' AND ResultValue > 150) OR
      (LabTest = 'CA' AND ResultValue > 10.1)
GROUP By LabTest;

There are other ways of expressing the logic. But neither IF nor CASE are needed -- just simple filtering conditions.

answered Aug 20, 2017 at 1:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jason A. Long · Accepted Answer · 2017-08-20 01:42:27Z

Either of the two following approaches should get you what you're after...

SELECT 
    ult.LabTest, 
    LT_Count = COUNT(*)
FROM 
    UsersDB.dbo.vwUserLabTest ult
    CROSS APPLY ( VALUES (
                        CASE ult.LabTest
                            WHEN 'PTT'  THEN 35.0
                            WHEN 'CK'   THEN 150.0
                            WHEN 'Ca'   THEN 10.1
                        END) 
                        ) rm (RangeMax)
WHERE 
    ult.ResultValue > rm.RangeMax
GROUP By 
    ult.LabTest;

-- OR

SELECT 
    ult.LabTest, 
    LT_Count = COUNT(*)
FROM 
    UsersDB.dbo.vwUserLabTest ult
WHERE 
    (ult.LabTest = 'PTT' AND ult.ResultValue > 35)
    OR
    (ult.LabTest = 'CK' AND ult.ResultValue > 150)
    OR 
    (ult.LabTest = 'Ca' AND ult.ResultValue > 10.1)
GROUP By 
    ult.LabTest;

HTH, Jason

Ardeshir Ayati · Accepted Answer · 2017-08-20 01:48:33Z

0

You can use select in select Make first select by case when syntax and make second select from first select. For example

Block quote

Select mylabtest, count(*) from( Select (case when LabTest = 'PTT' Then 35 else case when LabTest = 'CK' then 150 else LabTest = 'CA' Then 10.1 ) as mylabtest from .... ) group by mylabtest

Block quote

Reguards A.Ayati

answered Aug 20, 2017 at 1:48

Ardeshir Ayati

1211 silver badge6 bronze badges

Collectives™ on Stack Overflow

How do you implement if/then logic in SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related