Shmewnix Shmewnix - 2 months ago 7
SQL Question

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

Answer

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)