【两表进展对比，取最接近的日期的成本价格】

【两表进行对比，取最接近的日期的成本价格】
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,86

B表为导入实际销售情况表
型号，销售日期，实际价格
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

[解决办法]

SQL code

declare @A table(ccode int,cdate datetime,cmoney int)insert into @Aselect 801,'2012-08-01',120 union allselect 801,'2012-08-05',110 union allselect 801,'2012-08-10',100 union allselect 802,'2012-07-01',80 union allselect 802,'2012-08-10',60 union allselect 803,'2012-08-06',86declare @B table(ccode int,selldate datetime,sellmoney int)insert into @Bselect 801,'2012-08-01',130 union allselect 801,'2012-08-02',130 union allselect 801,'2012-08-08',150 union allselect 801,'2012-08-11',120 union allselect 802,'2012-08-12',100 union allselect 803,'2012-08-13',100select 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         10801         2012-08-02 00:00:00.000 130         120         10801         2012-08-08 00:00:00.000 150         110         40801         2012-08-11 00:00:00.000 120         100         20802         2012-08-12 00:00:00.000 100         60          40803         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
*/
[解决办法]
SQL code
SELECT  t.*, sellmoney - cmoney AS 毛利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
[解决办法]
探讨

SQL code
declare @A table(ccode int,cdate datetime,cmoney int)
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
se……