none
如何将条件嵌套 RRS feed

  • 问题

  •     下面的代码是关于合同的,看起来很长,其实非常简单,筛选的条件就是TypeOfPayment(交款时间)和LeaseDateFrom(合同起始日)。

    ----TypeOfPayment(交款时间)有两种方式:1.月末;    2.季度末1、4、7、10  ,作为第一种筛选条件。

    ----LeaseDateFrom(合同起始日)和变量@Time2做比较的值,作为筛选的第二个条件

    ----

        我写的代码虽然可以得到正确的结果,但我想把代码变得更简单。我尝试用if 、case 等嵌套,但没有成功。下面的代码貌似很长,其实简单,几个when后面的条件都只有微小的区别。请大家帮忙看看!

     

        ------------------------------------代码---------------------------------

    declare @time2 datetime
    select @time2='2012-1-31'
    select a.TypeOfPayment,a.LeaseDateFrom,
    (case
    when a.TypeOfPayment like '%月末%' 
    then 1 
    
    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  2
    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  3
    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  4
    else null
    
    end) as 总应收
    from ContractTable a join PropertyTable b on a.City +a.Road +a.[Address] =b.City +b.Road +b.[Address] 



    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!



    2012年1月30日 10:30

答案

  • 另外一个帖子里面回复你了

    用 in 替换了 or,效率这2个都一样,不过看上去短点点。<> 用not in替换


    family as water
    2012年1月31日 13:11

全部回复

  • 抛砖引玉,看还能否更简化!

    declare @time2 datetime
    select @time2='2012-1-31'
    select a.TypeOfPayment,a.LeaseDateFrom,
    (case
    when 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*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 
    end
    else null
    end) as 总应收
    from ContractTable a join PropertyTable b on a.City +a.Road +a.[Address] =b.City +b.Road +b.[Address] 
    
    



    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!
    2012年1月31日 12:12
  • 另外一个帖子里面回复你了

    用 in 替换了 or,效率这2个都一样,不过看上去短点点。<> 用not in替换


    family as water
    2012年1月31日 13:11
  • 最后优化的结果发出来共享,代码缩短了一大半!
    declare @time2 datetime
    select @time2='2012-1-31'
    select a.TypeOfPayment,a.LeaseDateFrom,
    (case
    when 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))in (1,4,7,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)) not in (1,4,7,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*MonthRent
    
    when (DATEPART(month, DATEADD (DD,-7,a.LeaseDateFrom)) in (1,4,7,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 
    end
    else null
    end) as 总应收
    from ContractTable a join PropertyTable b on a.City +a.Road +a.[Address] =b.City +b.Road +b.[Address] 
    



    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!
    2012年2月1日 14:10