Multi-Table Association query, mainly in T1 table date in T2, Tab

Sales Form T1
name of the number of out of stock unit price amount date
Banana Ten 2015-06-01
Banana 5 175 2015-06-13

Opening cost table T2
Product name cost unit quantity
Banana 5

Processing Table T3
Product Price Quantity processing Date
Banana 6 2015-05-10
Banana 5 2015-05-20
Banana 8 2015-06-05

how to get the following results: This result is obtained by T1 plus one up to the cost Unit Price field, its cutoff cost unit price calculation method is: (table T2 symbol amount) + (table T3 less than equals T1 date amount)/(corresponding T2 quantity +t3 less than equals T1 date quantity), rounding unit price, That is, the T2 table corresponds to the value of the symbol and T1 table is less than or equal to the date of the T3 table name of the sum divided by the number of two tables calculated by the corresponding average unit price, you can see the following column

number of goods out of stock unit price amount date up to cost unit price
Banana Ten 2015-06-01 5.36 = (5*100) + (6*80) + (5*40)/(100+80+40)
Banana 5 175 2015-06-13 6.19 = (5*100) + (6*80) + (5*40) + (8*100)/(100+80+40+100)Answer 1------------------------------------------------------------------------------------------sql2005 ; with jgcb  as ( select *, cast ( ‘1900-01-01‘ as datetime) 加工日期  from T2 UNION select *  from T3) select a.*, ( select sum (成本单价*数量)/ sum (数量)  from jgcb b where a.品名=b.品名  and a.日期>=b.加工日期 ) 截至成本单价    from T1 aSql2000select a.*,
(SELECT sum (cost per unit * quantity)/SUM (quantity)
From (SELECT *, cast (' 1900-01-01 ' as datetime) processing date
From T2
UNION
SELECT *
From T3) b
WHERE A. Name = B. Name and a. Date >= B. Processing date) as up to cost unit price
From T1 a answer 2-----------------------------------------------------------------------

drop table #Temp_1
Go
drop table #Temp_2
Go
drop table #Temp_3
Go

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 ' Banana ', 10,30,300, ' 2015-06-01 ' union ALL
Select ' Banana ', 5,35,175, ' 2015-06-13 '
INSERT INTO #Temp_2
Select ' Banana ', 5,100
INSERT INTO #Temp_3
Select ' Banana ', 6,80, ' 2015-05-10 ' union ALL
Select ' Banana ', 5,40, ' 2015-05-20 ' union ALL
Select ' Banana ', 8,100, ' 2015-06-05 '

Select
Distinct
T1. Name as name,
T1. Outqty as out of stock,
T1. Price as per unit,
T1.moneys as amount,
T1. Dates as Date,
T2. Costqty*t2. Price as period initial cost amount,
C.P as processing amount,
(T2. Costqty*t2. Price) +C.P)/d.sumprocessqty as cost per unit,
D.sumprocessqty,
Cast (((T2. Costqty*t2. Price) +C.P)/(D.sumprocessqty) as numeric (18,4)) as Uncost
From #Temp_1 T1
Left joins #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,t1. Dates,
SUM (distinct T3. Processqty) as Sumprocessqty
From #Temp_1 T1
Left joins #Temp_3 T3 on T1. Name=t3. Name and T1. Dates>=t3. Processdate
GROUP by T1. Name,t1. Dates) d on D.name=t1. Name

Multi-Table association query, mainly in the date of the T1 table in T2, Tab