下面的代码是关于合同的，看起来很长，其实非常简单，筛选的条件就是TypeOfPayment（交款时间）和LeaseDateFrom（合同起始日）。
----TypeOfPayment（交款时间）有两种方式：1.月末； 2.季度末1、4、7、10作为第一种筛选条件。
----LeaseDateFrom（合同起始日）[email protected]，作为筛选的第二个条件
----
我写的代码虽然可以得到正确的结果，但我想把代码变得更简单。我尝试用if 、case 等嵌套，但没有成功。下面的代码貌似很长，其实简单，几个when后面的条件都只有微小的区别。请大家帮忙看看！
- SQL code
declare @time2 datetimeselect @time2='2012-1-31'select a.TypeOfPayment,a.LeaseDateFrom,(casewhen a.TypeOfPayment like '%月末%' then DATEDIFF(mm, DATEADD (MONTH ,-1,a.LeaseDateFrom), @time2)/12*12*a.MonthRent +(DATEDIFF (MM,DATEADD (MONTH ,-1,a.LeaseDateFrom),@time2)%12+1)*a.MonthRent when TypeOfPayment='季度末7日前预交1、4、7、10' and ((DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))=1) or (DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))=4) or (DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))=7) or (DATEPART(month,DATEADD (DD,-7,a.LeaseDateFrom))=10)) then DATEDIFF (MM,dateadd(DD ,-7, a.LeaseDateFrom),@time2)/12*a.MonthRent +DATEDIFF (MM,dateadd(DD ,-7, a.LeaseDateFrom),@time2)%12/3*3*a.MonthRent +3*a.MonthRent when TypeOfPayment='季度末7日前预交1、4、7、10' and ((DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))<>1) or (DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))<>4) or (DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))<>7) or (DATEPART(month,DATEADD (DD,-7,a.LeaseDateFrom))<>10))and DATEPART (MONTH,@time2)>DATEPART (MONTH ,a.LeaseDateFrom ) then DATEDIFF (MM,dateadd(DD ,-7, a.LeaseDateFrom),@time2)/12*a.MonthRent + DATEDIFF (MM,dateadd(DD ,-7, a.LeaseDateFrom),@time2)%12/3*3*a.MonthRent +3*MonthRentwhen TypeOfPayment='季度末7日前预交1、4、7、10' and ((DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))<>1) or (DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))<>4) or (DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))<>7) or (DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))<>10))and DATEPART (MONTH,@time2)<DATEPART (MONTH ,a.LeaseDateFrom) then DATEDIFF (MM,dateadd(DD ,-7, a.LeaseDateFrom),@time2)/12*a.MonthRent +DATEDIFF (MM,dateadd(DD ,-7, a.LeaseDateFrom),@time2)%12/3*3*a.MonthRent else nullend) as 总应收from ContractTable a join PropertyTable b on a.City +a.Road +a.[Address] =b.City +b.Road +b.[Address]
------解决方案--------------------
- SQL code
declare @time2 datetimeselect @time2='2012-1-31'select a.TypeOfPayment,a.LeaseDateFrom,(casewhen a.TypeOfPayment like '%月末%' then DATEDIFF(mm, DATEADD (MONTH ,-1,a.LeaseDateFrom), @time2)/12*12*a.MonthRent +(DATEDIFF (MM,DATEADD (MONTH ,-1,a.LeaseDateFrom),@time2)%12+1)*a.MonthRent when TypeOfPayment='季度末7日前预交1、4、7、10' then case when ((DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))=1) or (DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))=4) or (DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))=7) or (DATEPART(month,DATEADD (DD,-7,a.LeaseDateFrom))=10)) then DATEDIFF (MM,dateadd(DD ,-7, a.LeaseDateFrom),@time2)/12*a.MonthRent +DATEDIFF (MM,dateadd(DD ,-7, a.LeaseDateFrom),@time2)%12/3*3*a.MonthRent +3*a.MonthRent when ((DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))<>1) or (DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))<>4) or (DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))<>7) or (DATEPART(month,DATEADD (DD,-7,a.LeaseDateFrom))<>10))and DATEPART (MONTH,@time2)>DATEPART (MONTH ,a.LeaseDateFrom ) then DATEDIFF (MM,dateadd(DD ,-7, a.LeaseDateFrom),@time2)/12*a.MonthRent + DATEDIFF (MM,dateadd(DD ,-7, a.LeaseDateFrom),@time2)%12/3*3*a.MonthRent +3*MonthRentwhen ((DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))<>1) or (DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))<>4) or (DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))<>7) or (DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom))<>10))and DATEPART (MONTH,@time2)<DATEPART (MONTH ,a.LeaseDateFrom) then DATEDIFF (MM,dateadd(DD ,-7, a.LeaseDateFrom),@time2)/12*a.MonthRent +DATEDIFF (MM,dateadd(DD ,-7, a.LeaseDateFrom),@time2)%12/3*3*a.MonthRent endelse nullend) as 总应收from ContractTable a join PropertyTable b on a.City +a.Road +a.[Address] =b.City +b.Road +b.[Address]