CODEWITHSUNDEEP
sqlsql-servert-sql
user2919277
user2919277

Reputation: 246

Count on result of Case statements in tsql

Hi i am getting a different value when i interchange the case statement line above and below. Which is not good. Can some body help me on this

SELECT
    CASE 
     WHEN dDate > '20130509' ANd dDate < '20130909' THEN 1
     WHEN dDate > '20130609' ANd dDate < '20131109' THEN 2
     WHEN dDate > '20130709' ANd dDate < '20131009' THEN 3 as DateVal,
CASE 
     WHEN dDate > '20130509' ANd dDate < '20130909' THEN COUNT(Encounter)
     WHEN dDate > '20130609' ANd dDate < '20131109' THEN COUNT(Encounter)
     WHEN dDate > '20130709' ANd dDate < '20131009' THEN COUNT(Encounter)) as Value
FROM Table 1

Result of the above query is 

 EncounterCount     Date
 230                 1
 45                  2
 20                  3

When the change the above query slightly 

SELECT
        CASE 
         WHEN dDate > '20130609' ANd dDate < '20131109' THEN 2
         **WHEN dDate > '20130509' ANd dDate < '20130909' THEN 1**
         WHEN dDate > '20130709' ANd dDate < '20131009' THEN 3 as DateVal,
    CASE 
         WHEN dDate > '20130609' ANd dDate < '20131109' THEN COUNT(Encounter)
         **WHEN dDate > '20130509' ANd dDate < '20130909' THEN COUNT(Encounter)**
         WHEN dDate > '20130709' ANd dDate < '20131009' THEN COUNT(Encounter)) as Value
    FROM Table 1

The result is like : Result of the above query is 

 EncounterCount     Date
 45                 1
 150                2
 20                 3

This is very strange to me. Can anybody help.

Upvotes: 1

Views: 100

Answers (1)

Moha Dehghan
Moha Dehghan

Reputation: 18443

That's because the date intervals you specified in WHEN clauses overlap. When the CASE statement finds a matching WHEN clause, it gets the result form that clause and skips the other ones.

Some of your records can match in more than one WHEN clause. When you reorder the clauses, you change the chance of rows to match against another WHEN clause.

Upvotes: 4

Related Questions