select Number400,_year ,_Month ,FeeType, a_FixedFee ,FeeTypeName,Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,DXEffectDate,
case when rec>a_FixedFee
then rec else a_FixedFee end as rec,
case when pay>c_FixedFee then pay else c_FixedFee end as pay,
case when rec>a_FixedFee then rec else a_FixedFee end-case when
pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段
from
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.DXEffectDate,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400) t如下图 我2013年9月份实际应收的价格应该是330元,要判断一下 我DXEffectDate日期是否小于年份和月份 如果大于的话 就显示实际的价格 要是小于才按着抵消和实际
打的费用 我想着通过datepart(yyyy,DXEffectDate)得到年和datepart(mm,DXEffectDate)得到月份去比较_year和month值 sqlselect
Consumer1,Consumer2,Consumer3,Consumer4,DXEffectDate,
case when rec>a_FixedFee
then rec else a_FixedFee end as rec,
case when pay>c_FixedFee then pay else c_FixedFee end as pay,
case when rec>a_FixedFee then rec else a_FixedFee end-case when
pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段
from
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.DXEffectDate,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400) t如下图 我2013年9月份实际应收的价格应该是330元,要判断一下 我DXEffectDate日期是否小于年份和月份 如果大于的话 就显示实际的价格 要是小于才按着抵消和实际
打的费用 我想着通过datepart(yyyy,DXEffectDate)得到年和datepart(mm,DXEffectDate)得到月份去比较_year和month值 sqlselect
convert(varchar(7),DXEffectDate,120)select convert(varchar(7),getdate(),120)
得到格式
/*
2013-10 -->以这种格式去判断
*/
convert(varchar(7),DXEffectDate,120)select convert(varchar(7),getdate(),120)
得到格式
/*
2013-10 -->以这种格式去判断
*/
(比如下面是9月份的数据,他的生效日期是10月份 9月份的数据应该是打多少花多少钱 不存在抵消这个概念 从10月份才可以和抵消去计算的
Consumer1,Consumer2,Consumer3,Consumer4,DXEffectDate,
case when rec>a_FixedFee and datepart(yy,DXEffectDate)>= _year and datepart(mm,DXEffectDate)>=_Month
then rec else a_FixedFee end as rec,
case when pay>c_FixedFee then pay else c_FixedFee end as pay,
case when rec>a_FixedFee then rec else a_FixedFee end-case when
pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段
from
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.DXEffectDate,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400) t不是我想要的价格啊 我的结果如之前发布的图
只有4008008825号码 9月份的REC的价格应该是330元才对的
这个数据
2013 4008008825 10月份的数据 他的抵消生效日期是10月份 rec应收价格应该是500
2013 4008008825 9月份的数据 他的抵消生效日期是10月份 9月份应该是实际打多少是多少 9月份打了330元 rec应收价格应该是330
我还想在问下 这个语句是否可以在优化一下
select Number400,_year ,_Month , a_FixedFee ,FeeTypeName,Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,DXEffectDate,
case when (datepart(yy,DXEffectDate) >=_year and datepart(mm,DXEffectDate) > _Month)
then rec else
(case when rec>a_FixedFee
then rec else a_FixedFee end)
end as rec, -->rec字段
case when pay>c_FixedFee then pay else c_FixedFee end as pay, -->pay字段
case when (datepart(yy,DXEffectDate) >=_year and datepart(mm,DXEffectDate) > _Month)
then rec else
(case when rec>a_FixedFee
then rec else a_FixedFee end)
end
-
case when pay>c_FixedFee then pay else c_FixedFee end as profit -->profit字段
from
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.DXEffectDate,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400)
t where t._year=2013 and t._Month=10
select Number400,_year ,_Month , a_FixedFee ,FeeTypeName,Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,DXEffectDate,
case when (datepart(yy,DXEffectDate) >=_year and datepart(mm,DXEffectDate) > _Month)
then rec else
(case when rec>a_FixedFee
then rec else a_FixedFee end)
end as rec, -->rec字段
case when pay>c_FixedFee then pay else c_FixedFee end as pay, -->pay字段
case when (datepart(yy,DXEffectDate) >=_year and datepart(mm,DXEffectDate) > _Month)
then rec else
(case when rec>a_FixedFee
then rec else a_FixedFee end)
end
-
case when pay>c_FixedFee then pay else c_FixedFee end as profit -->profit字段
from
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.DXEffectDate,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400)
t where t._year=2013 and t._Month=10
select Number400,_year ,_Month , a_FixedFee ,FeeTypeName,
Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,DXEffectDate,case when (datepart(yy,DXEffectDate) >=_year
and datepart(mm,DXEffectDate) > _Month)
or (rec>a_FixedFee)
then rec
else a_FixedFee
end as rec,
-->rec字段
case when pay>c_FixedFee then pay else c_FixedFee end as pay, -->pay字段 case when (datepart(yy,DXEffectDate) >=_year
and datepart(mm,DXEffectDate) > _Month)
or rec>a_FixedFee
then rec
else a_FixedFee
end - case when pay>c_FixedFee then pay else c_FixedFee end as profit -->profit字段from
(select a.Number400,b._year ,b._Month ,a.FeeType,
a.FixedFee as a_FixedFee,a.DXEffectDate,a.FeeTypeName,
a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,
c.FixedFee as c_FixedFee, case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec, case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as payfrom S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400
) t
where t._year=2013 and t._Month=10
Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,DXEffectDate,--简化case when
case when (datepart(yy,DXEffectDate) >=_year
and datepart(mm,DXEffectDate) > _Month)
or (rec>a_FixedFee)
then rec
else a_FixedFee
end as rec,
-->rec字段
case when pay>c_FixedFee then pay else c_FixedFee end as pay, -->pay字段 case when (datepart(yy,DXEffectDate) >=_year
and datepart(mm,DXEffectDate) > _Month)
or rec>a_FixedFee
then rec
else a_FixedFee
end - case when pay>c_FixedFee then pay else c_FixedFee end as profit -->profit字段from
(select a.Number400,b._year ,b._Month ,a.FeeType,
a.FixedFee as a_FixedFee,a.DXEffectDate,a.FeeTypeName,
a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,
c.FixedFee as c_FixedFee, case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec, case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as payfrom S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400
where b._year=2013 and b._Month=10 --查询条件移入内层
) t
a.FixedFee,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,a.DXEffectDate,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400现在业务又改动了 我现在这个语句对应这个结果如上图
我要在这个基础上进行比较
如果DXEffectDate抵消时间生效的话 rec 小于fixedFee的时候 就应该是fixedfee这个值
这会我咋弄这不对,
4008008827 2013 10 rec应该是700
4008008830 2013 11 rec应该是900
4008008828 2013 11 rec应该是900
a.FixedFee,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,a.DXEffectDate,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400现在业务又改动了 我现在这个语句对应这个结果如上图
我要在这个基础上进行比较
如果DXEffectDate抵消时间生效的话 rec 小于fixedFee的时候 就应该是fixedfee这个值
这会我咋弄这不对,
4008008827 2013 10 rec应该是700
4008008830 2013 11 rec应该是900
4008008828 2013 11 rec应该是900
select Number400,Cyear,Cmonth,SaleHTNo,Contractcustomers,FeeTypeName,
FixedFee,Fee1,Fee2,Fee3,Fee4,FeeType
Consumer1,Consumer2,Consumer3,Consumer4,DXEffectDate,
case when (Cyear>=datepart(yy,DXEffectDate) and Cmonth>=datepart(mm,DXEffectDate) and rec<FixedFee)
then FixedFee else rec end as rec
from
(
select a.Number400,b._year Cyear,b._Month Cmonth,a.SaleHTNo,a.Contractcustomers,a.FeeTypeName,a.FeeType,
a.FixedFee,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,a.DXEffectDate,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
) t