下面的代码是关于合同的,看起来很长,其实非常简单,筛选的条件就是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 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*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
else nullend) as 总应收
from ContractTable a join PropertyTable b on a.City +a.Road +a.[Address] =b.City +b.Road +b.[Address]
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'
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*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
else nullend) as 总应收
from ContractTable a join PropertyTable b on a.City +a.Road +a.[Address] =b.City +b.Road +b.[Address]
解决方案 »
- win7下sql2005sp4问题
- MSSQL数据导入Access 问题
- 求一个sql,取得连续的上级
- 为何定义了2000字节的字符串,存到数据库里面以后,全部都变成之后256个字节的了
- 一条比较难的SQL语句..高手进来帮帮我好吗?
- 统计问题
- 关于sql查询的效率问题,有知道的告知一下,急急急急
- 菜鸟问题求解
- 如果在安装SQLSERVER2K时选择NT身份验证,现在是否可以将其改成混和验证?
- SQL2000與SQL2005的差異 虛字段﹑多表關聯后允許更新問題 (望各人達者解惑﹐謝!)
- 急求:“未能在 sysdatabases 中找到数据库 'test' 所对应的条目” 是怎么回事
- 这样的SQL怎么写呢?
精简的话,主要把这个,写成一个判断就好了。
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*MonthRentwhen (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]