A表为成本表
型号,日期,成本
ccode,cdate,cmoney
801,2012-08-01,120
801,2012-08-05,110
801,2012-08-10,100
802,2012-07-01,80
802,2012-08-10,60
803,2012-08-06,86B表为导入实际销售情况表
型号,销售日期,实际价格
ccode,selldate,sellmoney
801,2012-08-01,130
801,2012-08-02,130
801,2012-08-08,150
801,2012-08-11,120
802,2012-08-12,100
803,2012-08-13,100根据销售日期,取成本A表中最接近销售日期的成本价格,计算毛利最后的结果就是型号,销售日期,实际价格,成本价格,毛利
801,2012-08-01,130,120,10
801,2012-08-02,130,120,10
801,2012-08-08,150,110,40
801,2012-08-11,120,100,20
802,2012-08-12,100,60,40
803,2012-08-13,100,86,14
型号,日期,成本
ccode,cdate,cmoney
801,2012-08-01,120
801,2012-08-05,110
801,2012-08-10,100
802,2012-07-01,80
802,2012-08-10,60
803,2012-08-06,86B表为导入实际销售情况表
型号,销售日期,实际价格
ccode,selldate,sellmoney
801,2012-08-01,130
801,2012-08-02,130
801,2012-08-08,150
801,2012-08-11,120
802,2012-08-12,100
803,2012-08-13,100根据销售日期,取成本A表中最接近销售日期的成本价格,计算毛利最后的结果就是型号,销售日期,实际价格,成本价格,毛利
801,2012-08-01,130,120,10
801,2012-08-02,130,120,10
801,2012-08-08,150,110,40
801,2012-08-11,120,100,20
802,2012-08-12,100,60,40
803,2012-08-13,100,86,14
insert into @A
select 801,'2012-08-01',120 union all
select 801,'2012-08-05',110 union all
select 801,'2012-08-10',100 union all
select 802,'2012-07-01',80 union all
select 802,'2012-08-10',60 union all
select 803,'2012-08-06',86
declare @B table(ccode int,selldate datetime,sellmoney int)
insert into @B
select 801,'2012-08-01',130 union all
select 801,'2012-08-02',130 union all
select 801,'2012-08-08',150 union all
select 801,'2012-08-11',120 union all
select 802,'2012-08-12',100 union all
select 803,'2012-08-13',100
select t.*,sellmoney-cmoney as 毛利
from
(
select b.*,(select top 1 cmoney from @A a where a.ccode=b.ccode and datediff(day,a.cdate,b.selldate)>=0 order by a.cdate desc) cmoney from @B b
) t/*
ccode selldate sellmoney cmoney 毛利
----------- ----------------------- ----------- ----------- -----------
801 2012-08-01 00:00:00.000 130 120 10
801 2012-08-02 00:00:00.000 130 120 10
801 2012-08-08 00:00:00.000 150 110 40
801 2012-08-11 00:00:00.000 120 100 20
802 2012-08-12 00:00:00.000 100 60 40
803 2012-08-13 00:00:00.000 100 86 14
*/
;WITH cte AS (
SELECT b.ccode,a.cmoney,b.sellmoney,rr=ROW_NUMBER() OVER(PARTITION BY b.rn ORDER BY ABS(datediff(dd,a.cdate,b.selldate))) FROM @a a join
(SELECT rn=ROW_NUMBER() OVER(ORDER BY GETDATE()),* FROM @b) b
ON a.ccode=b.ccode
)
SELECT *,sellmoney-cmoney FROM cte WHERE rr=1;
/*
ccode cmoney sellmoney rr
----------- ----------- ----------- -------------------- -----------
801 120 130 1 10
801 120 130 1 10
801 100 150 1 50
801 100 120 1 20
802 60 100 1 40
803 86 100 1 14
*/
FROM (
SELECT b.* ,
(
SELECT TOP 1
cmoney
FROM ta a
WHERE a.ccode = b.ccode
AND DATEDIFF(day, a.cdate, b.selldate) >= 0
ORDER BY a.cdate DESC
) cmoney
FROM tb b
) t
--if object_id('t1') is not null
-- drop table t1
--Go
Create table t1([ccode] smallint,[cdate] datetime,[cmoney] smallint)
Insert into t1
Select 801,'2012-08-01',120
Union all Select 801,'2012-08-05',110
Union all Select 801,'2012-08-10',100
Union all Select 802,'2012-07-01',80
Union all Select 802,'2012-08-10',60
Union all Select 803,'2012-08-06',86--if object_id('t2') is not null
-- drop table t2
--Go
Create table t2([ccode] smallint,[selldate] datetime,[sellmoney] smallint)
Insert into t2
Select 801,'2012-08-01',130
Union all Select 801,'2012-08-02',130
Union all Select 801,'2012-08-08',150
Union all Select 801,'2012-08-11',120
Union all Select 802,'2012-08-12',100
Union all Select 803,'2012-08-13',100
--型号,销售日期,实际价格,成本价格,毛利
--801,2012-08-01,130,120,10
--801,2012-08-02,130,120,10
--801,2012-08-08,150,110,40
--801,2012-08-11,120,100,20
--802,2012-08-12,100,60,40
--803,2012-08-13,100,86,14
SELECT
a.ccode AS [型号]
,a.selldate AS [销售日期]
,a.sellmoney AS [实际价格]
,c.cmoney AS [成本价格]
,a.sellmoney-c.cmoney AS [毛利]
FROM t2 AS a
CROSS APPLY(SELECT ccode,MAX(cdate) AS cdate FROM t1 AS x
WHERE x.ccode=a.ccode
AND x.cdate<=a.selldate
GROUP BY ccode
) AS b
INNER JOIN t1 AS c ON b.ccode=C.ccode AND b.cdate=c.cdate
---------------------------------------------------------------------------SELECT
a.ccode AS [型号]
,a.selldate AS [销售日期]
,a.sellmoney AS [实际价格]
,b.cmoney AS [成本价格]
,a.sellmoney-b.cmoney AS [毛利]
FROM t2 AS a
INNER JOIN t1 AS b ON a.ccode=b.ccode AND b.cdate=(SELECT MAX(cdate) FROM t1 AS x WHERE x.ccode=b.ccode AND x.cdate<=a.selldate)