select CurrentBucket =
CASE WHEN
(CASE receive WHEN '0' THEN datediff(dd, PaymentDate, getdate())
WHEN '1' THEN datediff(dd, DueDate, getdate())
END) between 1 and 29
THEN amount
ELSE 0
END
from mytable或
select CurrentBucket =
CASE WHEN
receive* datediff(dd, PaymentDate, getdate())
+(1-receive)* datediff(dd, DueDate, getdate()) between 1 and 29
THEN amount
ELSE 0
END
from mytable
CASE WHEN
(CASE receive WHEN '0' THEN datediff(dd, PaymentDate, getdate())
WHEN '1' THEN datediff(dd, DueDate, getdate())
END) between 1 and 29
THEN amount
ELSE 0
END
from mytable或
select CurrentBucket =
CASE WHEN
receive* datediff(dd, PaymentDate, getdate())
+(1-receive)* datediff(dd, DueDate, getdate()) between 1 and 29
THEN amount
ELSE 0
END
from mytable
ceiling(receive* datediff(dd, PaymentDate, getdate())
+(1-receive)* datediff(dd, DueDate, getdate())/30.0)*amount
from mytable
receive是bit类型的,能用第二种方法吗?
我来试试!
非常感谢。不过你的第三个方法是不对的。但是思路我知道了。
我会给分的另外,还想请教一下,你认为case效率高还是使用这种运算效率高?因为我需要做多个类似的字段。
同CASE过多会影响SQL的效率,追好用IF不过我写的时候,感觉区别不大。
给分!