销售表T1
品名 出库数量 单价 金额 日期
香蕉 10 30 300 2015-06-01
香蕉 5 35 175 2015-06-13
期初成本表T2
品名 成本单价 数量
香蕉 5 100
加工表T3
品名 单价 数量 加工日期
香蕉 6 80 2015-05-10
香蕉 5 40 2015-05-20
香蕉 8 100 2015-06-05
如何得到下面结果：此结果由T1加一截至成本单价字段得来，其截止成本单价计算方法为：（表T2品名金额）+（表T3小于等于T1日期金额）/(对应T2数量+T3小于等于T1日期数量) 后四舍五入单价，即T2表对应品名金额加T1表小于等于日期之前的T3表品名金额之和除以两表数量计算出的对应平均单价，可以参看下面列式
品名 出库数量 单价 金额 日期 截至成本单价
香蕉 10 30 300 2015-06-01 5.36 = (5*100)+(6*80)+(5*40) / (100+80+40)
香蕉 5 35 175 2015-06-13 6.19 = (5*100)+(6*80)+(5*40)+() / (100+80+40)
------解决思路----------------------
create table #Temp_1(
Name varchar(200),
OutQTY numeric(18,4),
Price numeric(18,4),
moneys numeric(18,4),
Dates datetime
)
create table #Temp_2
(
Name varchar(200),
Price numeric(18,4),
CostQTY numeric(18,4)
)
Create table #Temp_3
(
Name varchar(200),
ProcessPrice numeric(18,4),
ProcessQTY numeric(18,4),
ProcessDate datetime
)
insert into #Temp_1
select '香蕉',10,30,300,'2015-06-01'union all
select '香蕉',5,35,175,'2015-06-13'
insert into #Temp_2
select '香蕉',5,100
insert into #Temp_3
select '香蕉',6,80,'2015-05-10' union all
select '香蕉',5,40,'2015-05-20' union all
select '香蕉',8,100,'2015-06-05'
select
distinct
t1.Name,
t1.OutQTY,
t1.Price,
t1.moneys,
t1.Dates ,
t2.CostQTY*t2.Price,
c.P,
((t2.CostQTY*t2.Price)+c.P)/d.SumProcessQTY,
d.SumProcessQTY,
cast(((t2.CostQTY*t2.Price)+c.P)/(d.SumProcessQTY) as numeric(18,4))as UnCost
from #Temp_1 t1
left join #Temp_2 t2 on t1.Name=t2.Name
left join (select distinct
cet.Dates,
cet.Name,
sum(t3.ProcessPrice* t3.ProcessQTY)as P
from #Temp_1 cet
left join #Temp_3 t3 on cet.Name=t3.Name and cet.Dates>=t3.ProcessDate
group by cet.Dates,cet.Name
)c on c.Name=t1.Name and c.Dates=t1.Dates
left join (select
t1.Name,
sum(distinct t3.ProcessQTY) as SumProcessQTY
from #Temp_1 t1
left join #Temp_3 t3 on t1.Name=t3.Name and t1.Dates>=t3.ProcessDate
group by t1.Name)d on d.Name=t1.Name