CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Shmewnix
Shmewnix

Reputation: 1573

How to reference multiple columns in case statement

I have a query, where i'm attempting to use a case statement referencing two columns.

Data:

Users table:

ID_User       sUserName
1             Test
2             Test2
3             Test3

Customers table:

ID        last name       Redeemed        paid
1           Cust1         10/1/16         9/15/16
2           Cust2         9/16/16         9/14/16
3           Cust3         10/4/16         9/30/16
4           Cust4         10/5/16         10/1/16

Query:

SELECT     Users.sUserName, Customers.LastName, CASE WHEN customers.Redeemed < '9/28/16' AND 
                      customers.paid < '9/28/16' THEN '3' ELSE '4' END AS Amount
FROM         Customers LEFT OUTER JOIN
                      Users ON Customers.lGreeterId = Users.ID_User
WHERE     (Customers.Redeemed BETWEEN @startdate AND @enddate)

Output of the query:

susername  last name      amount

Test        Cust1           4
Test2       Cust2           4
Test3       Cust3           4
Test3       Cust4           4

Output should be:

susername  last name      amount

 Test        Cust1           3
 Test2       Cust2           3
 Test3       Cust3           4
 Test3       Cust4           4

Why am I getting the "else" result for all of them? I am looking to return a '3' if redeemed is less than the specified date OR paid is less than the specified date.

Paid and Redeemed are both Datatype datetime

Upvotes: 3

Views: 3776

Answers (1)

JohnHC
JohnHC

Reputation: 11205

Following comments, try using a different date format in the query

SELECT     Users.sUserName, Customers.LastName, 
           CASE 
             WHEN customers.Redeemed < '2016-09-28' THEN '3'
             WHEN customers.paid < '2016-09-28' THEN '3' 
             ELSE '4'
           END AS Amount
FROM         Customers 
LEFT JOIN Users 
    ON Customers.lGreeterId = Users.ID_User
WHERE     (Customers.Redeemed BETWEEN @startdate AND @enddate)

Upvotes: 3

Related Questions