求一个存储过程，谢啦，比较难
我有三张表A,B,C
A的字段有Name(委托方)varchar(50),Money(管理费)decimal,Deparment(分配部门)varchar(50)，Create(创建时间datetime
B的字段有Close(结算状态1为结算0为未结算)varchar（1），CostType(费用类型0收费1付费)varchar(1),ShouldMoney(金额)decimal(18,4),Num(业务号)varchar(50),Currency_Rate(汇率，汇率不是统一的，有美元，日元等)decimal(18,4)
C的字段有Num(业务号)varchar(50),Name(委托方)varchar(50),Deparment(分配部门)varchar(50),InputDate(录入时间)datetime,
我想根据条件求出存利润，条件就是A表的分配部门和创建时间，和C表的委托方，
A的委托方与C的委托方是关联的B的业务号和C的业务号是关联的
我想求出2013-5月份所有的存利润(包括已结算和未结算的)，公式是
(已结算的收费*汇率-已结算的付费*汇率)=已结算毛利润
（未结算的收费*汇率-未结算的付费*汇率）=为结算毛利润
（已结算毛利润+未结算毛利润）-管理费 = 纯利润
我想最终显示的格式是这样的
委托方 分配部门 票数（已结算和未结算票数的总和） 应收费用 应付费用 管理费用 纯利润
求高手 给个存储过程 谢啦
select bbd.Consignor as '委托方',cmf.InputDepartment as '部门'
,COUNT(case when CostType='0' and Flag_ClosingCost='0' then bbd.Consignor else 0 end)
,(sum(case when CostType='0' and Flag_ClosingCost='0' then ShouldMoney else 0 end)*Currency_Rate) as '应收金额'
,(sum(case when CostType='1' and Flag_ClosingCost='0' then ShouldMoney else 0 end)*Currency_Rate) as '应付金额'
,(sum(case when CostType='0' and Flag_ClosingCost='0' then ShouldMoney else 0 end)*Currency_Rate)-
(sum(case when CostType='1' and Flag_ClosingCost='0' then ShouldMoney else 0 end)*Currency_Rate) as '毛利润'
,[Money] as '管理费用'
,(((sum(case when CostType='0' and Flag_ClosingCost='0' then ShouldMoney else 0 end)*Currency_Rate)-
(sum(case when CostType='1' and Flag_ClosingCost='0' then ShouldMoney else 0 end)*Currency_Rate))-([Money])) as '纯利润'
from [cost_FeeInfo] as cfi,bus_BusinessDetail as bbd,cost_ManageFee as cmf
where cfi.BusinessNum=bbd.BusinessNum and cmf.Consignor = bbd.Consignor
GROUP BY bbd.Consignor,cmf.InputDepartment,[Money],Currency_Rate
select bbd.Consignor as '委托方',cmf.InputDepartment as '部门'
,COUNT(case when CostType='0' and Flag_ClosingCost='0' then bbd.Consignor else 0 end)
,(sum(case when CostType='0' then ShouldMoney else 0 end)*Currency_Rate) as '应收金额'
,(sum(case when CostType='1' then ShouldMoney else 0 end)*Currency_Rate) as '应付金额'
,(sum(case when CostType='0' then ShouldMoney else 0 end)*Currency_Rate)-
(sum(case when CostType='1' then ShouldMoney else 0 end)*Currency_Rate) as '毛利润'
,[Money] as '管理费用'
,(((sum(case when CostType='0' then ShouldMoney else 0 end)*Currency_Rate)-
(sum(case when CostType='1' then ShouldMoney else 0 end)*Currency_Rate))-([Money])) as '纯利润'
from [cost_FeeInfo] as cfi,bus_BusinessDetail as bbd,cost_ManageFee as cmf
where cfi.BusinessNum=bbd.BusinessNum and cmf.Consignor = bbd.Consignor
GROUP BY bbd.Consignor,cmf.InputDepartment,[Money],Currency_Rate
[解决办法]
是你给的分数太少了
[解决办法]
我写的 不好用 求高手 给个存储过程
--> 为什么不好用呢?
