用Case把条件嵌套起来，结果出错，错在哪里？？？
ContractTable（合同表）有这样几个字段，LeaseDateFrom（合同起始日），LeaseDateTo（合同终止日），EscapeLeaseDateTo（免租期截止日），monthRent（第一年的租金），monthRent1（第二年的租金），monthRent2（第三年的租金），TypeofPayment（交租方式）。
----
当TypeofPayment（交租方式）为“免租，递增，季度末7日前预交2、5、8、11”，表示这份合同在最开始的时候有一段时间的免租期，也就是指LeaseDateFrom（合同起始日）到EscapeLeaseDateTo（免租期截止日）这一段时间，而EscapeLeaseDateTo后的一天就要开始收租金了。
递增是表示这份合同每年的租金都不同，monthRent（第一年的租金），monthRent1（第二年的租金），monthRent2（第三年的租金），我做了一段代码，可以自动的从免租期结束后开始算租金（monthRent），而且第二年租金就是（monthRent1），第三年租金就是（monthRent）。
----
这一段代码运行无误。我继续用Case把条件嵌套，给“免租，递增”条件前在加上“季度末7日前预交2,5,8,11”,本来有的结果都变成NULL了。（第二幅代码）
----
我考虑了很久，把代码调整了一下，在我看来只是结构稍微调整了一下，但这次能显示正确结果了！（第三幅代码）
我真的不明白我用case把各个条件嵌套起来（这个结果是NULL）和我用and把各个条件直接写出来（这个结果正确）有什么区别，我觉得结果应该都是一样的啊！
----
只能说明一个问题，那就是我的case有误！到底哪里做错了！求解！！！谢谢
---------------------------------只考虑免租，递增条件下运行无误的代码----------------------------
- SQL code
(case when DATEDIFF (day, a.LeaseDateFrom,@time2) between 0 and DATEDIFF(day, LeaseDateFrom,DATEADD(day,-1, DATEADD(year,1,leasedatefrom))) then MonthRent*3 when DATEDIFF (day, a.LeaseDateFrom,@time2) between DATEDIFF(day, LeaseDateFrom,DATEADD(day,-1, DATEADD(year,1,leasedatefrom)))+1 and DATEDIFF(day, LeaseDateFrom,DATEADD(day,-1, DATEADD(year,2,leasedatefrom))) then MonthRent1*3 when DATEDIFF (day, a.LeaseDateFrom,@time2) between DATEDIFF(day, LeaseDateFrom,DATEADD(day,-1, DATEADD(year,2,leasedatefrom)))+1 and DATEDIFF(day, LeaseDateFrom,DATEADD(day,-1, DATEADD(year,3,leasedatefrom))) then MonthRent2*3 else null end)
------------------------------加上“季度末7日前预交2,5,8,11”条件后本来正确的结果都变成NULL了
- SQL code
declare @time2 dateselect @time2='2012.11.22'select (casewhen a.TypeOfPayment ='免租，递增，季度末7日前预交2、5、8、11' and DATEPART(month, @time2) in (2,5,8,11) then (case when DATEDIFF (day, a.LeaseDateFrom,@time2) between 0 and DATEDIFF(day, LeaseDateFrom,DATEADD(day,-1, DATEADD(year,1,leasedatefrom))) then MonthRent*3 when DATEDIFF (day, a.LeaseDateFrom,@time2) between DATEDIFF(day, LeaseDateFrom,DATEADD(day,-1, DATEADD(year,1,leasedatefrom)))+1 and DATEDIFF(day, LeaseDateFrom,DATEADD(day,-1, DATEADD(year,2,leasedatefrom))) then MonthRent1*3 when DATEDIFF (day, a.LeaseDateFrom,@time2) between DATEDIFF(day, LeaseDateFrom,DATEADD(day,-1, DATEADD(year,2,leasedatefrom)))+1 and DATEDIFF(day, LeaseDateFrom,DATEADD(day,-1, DATEADD(year,3,leasedatefrom))) then MonthRent2*3 else null end) else null end) from ContractTable a join PropertyTable b on a.City +a.Road +a.[Address] =b.City +b.Road +b.[Address] where and ContractRemarks like '%免租%' or ContractRemarks like '%递增%'order by b.City +b.Road +b.[Address]
--------------------------第三段正确的代码，这样写为什么就对了，它和上面的第二段代码有区别么？
- SQL code
declare @time2 dateselect @time2='2012.11.22'select (casewhen a.TypeOfPayment ='免租，递增，季度末7日前预交2、5、8、11' and DATEPART(month, @time2) in (2,5,8,11)and DATEDIFF (day, a.LeaseDateFrom,@time2)between 0 and DATEDIFF(day, LeaseDateFrom,DATEADD(day,-1, DATEADD(year,1,leasedatefrom))) then MonthRent*3when a.TypeOfPayment ='免租，递增，季度末7日前预交2、5、8、11' and DATEPART(month, @time2) in (2,5,8,11) and DATEDIFF (day, a.LeaseDateFrom,@time2)between DATEDIFF(day, LeaseDateFrom,DATEADD(day,-1, DATEADD(year,1,leasedatefrom)))+1 and DATEDIFF(day, LeaseDateFrom,DATEADD(day,-1, DATEADD(year,2,leasedatefrom))) then MonthRent1*3when a.TypeOfPayment ='免租，递增，季度末7日前预交2、5、8、11' and DATEPART(month, @time2) in (2,5,8,11)and DATEDIFF (day, a.LeaseDateFrom,@time2) between DATEDIFF(day, LeaseDateFrom,DATEADD(day,-1, DATEADD(year,2,leasedatefrom)))+1 and DATEDIFF(day, LeaseDateFrom,DATEADD(day,-1, DATEADD(year,3,leasedatefrom))) then MonthRent2*3 else null end) from ContractTable a join PropertyTable b on a.City +a.Road +a.[Address] =b.City +b.Road +b.[Address] where ContractRemarks like '%免租%' or ContractRemarks like '%递增%'order by b.City +b.Road +b.[Address]
[解决办法]
源代码少了%号