sql - 如何在case语句中引用多列

Question

我有一个查询，我试图在其中使用引用两列的 case 语句。

数据:

用户表:

ID_User       sUserName
1             Test
2             Test2
3             Test3

客户表:

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

询问:

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)

查询的输出:

susername  last name      amount

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

输出应该是:

susername  last name      amount

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

为什么我得到所有这些的“其他”结果？如果兑换的日期小于指定日期，我希望返回“3” OR付款少于指定日期。

付费和赎回都是数据类型 datetime

Answer 1

根据评论，尝试在查询中使用不同的日期格式

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)