求一段时期内采购价格不同的记录，物料代码及单价相同的只取一个，根据时间降序排列，这样可以知道最近的涨价是什么时候，并把采购单价的变动数据输出。
select
t2.FBillNo 采购订单号,
t2.FCheckDate 审核日期,
t1.FItemID 物料内码,
t1.FPrice 采购单价
from POOrderEntry t1
left join POOrder t2 on t1.FInterID = t2.FInterID
where FCheckDate >= '2014-12-1'
数据如下
采购订单号 审核日期 物料内码 采购单价
POORD033689 2014-12-12 00:00:00.000 120559 7.2649570000
POORD034455 2014-12-16 00:00:00.000 124461 0.0000000000
POORD035122 2014-12-20 00:00:00.000 124025 2.1367520000
POORD035339 2015-01-22 00:00:00.000 22659 0.7521370000
POORD035500 2014-12-09 00:00:00.000 124445 35.8974360000
POORD035553 2014-12-22 00:00:00.000 123953 0.7692310000
POORD036007 2015-01-07 00:00:00.000 38507 5.5555560000
POORD036089 2014-12-05 00:00:00.000 125560 47.8632480000
POORD036089 2014-12-05 00:00:00.000 125561 73.5042740000
POORD036089 2014-12-05 00:00:00.000 125562 101.7094020000
POORD036089 2014-12-05 00:00:00.000 125563 129.0598290000
POORD036089 2014-12-05 00:00:00.000 125564 161.5384620000
POORD036192 2014-12-04 00:00:00.000 125588 7.3717950000
POORD036192 2014-12-04 00:00:00.000 113628 14.7435900000
POORD036192 2014-12-04 00:00:00.000 125589 29.4871790000
POORD036192 2014-12-04 00:00:00.000 113630 58.9743590000
POORD036192 2014-12-04 00:00:00.000 125590 117.9487180000
POORD036192 2014-12-04 00:00:00.000 111528 23.2478630000
POORD036192 2014-12-04 00:00:00.000 111530 46.4957260000
POORD036192 2014-12-04 00:00:00.000 111532 69.7435900000
POORD036192 2014-12-04 00:00:00.000 111534 92.9914530000
POORD036192 2014-12-04 00:00:00.000 111536 116.2393160000
POORD036192 2014-12-04 00:00:00.000 111038 5.5555560000
POORD036192 2014-12-04 00:00:00.000 111040 11.1111110000
POORD036192 2014-12-04 00:00:00.000 111042 16.6666670000
POORD036129 2015-01-06 00:00:00.000 102405 0.7008550000
POORD036129 2015-01-06 00:00:00.000 102407 0.5982910000
POORD036129 2015-01-06 00:00:00.000 102409 2.9401710000
POORD036129 2015-01-06 00:00:00.000 101310 1.3247860000
POORD036129 2015-01-06 00:00:00.000 101310 1.3247860000
POORD036129 2015-01-06 00:00:00.000 101309 0.5982910000
POORD036246 2015-01-06 00:00:00.000 125744 295.7264960000
POORD036695 2014-12-02 00:00:00.000 126372 19.2307690000
POORD036727 2014-12-08 00:00:00.000 23480 9.8290600000
POORD036929 2015-01-26 00:00:00.000 40654 2.9059830000
POORD037192 2014-12-02 00:00:00.000 49404 0.1034190000
POORD037338 2014-12-11 00:00:00.000 49617 36.9350430000
POORD037338 2014-12-11 00:00:00.000 47092 0.0529910000
POORD037338 2014-12-11 00:00:00.000 124035 7.0965810000
POORD037338 2014-12-11 00:00:00.000 49624 0.4504270000
POORD037338 2014-12-11 00:00:00.000 46849 0.1111110000
POORD037381 2014-12-08 00:00:00.000 108804 6.8582880000
POORD037381 2014-12-08 00:00:00.000 21915 14.3979870000
POORD037381 2014-12-08 00:00:00.000 81656 23.5863370000
POORD037381 2014-12-08 00:00:00.000 21933 17.7777650000
POORD037542 2014-12-11 00:00:00.000 45675 5.9829060000
POORD037785 2014-12-22 00:00:00.000 127477 0.2564100000
POORD037785 2014-12-22 00:00:00.000 127478 0.2564100000
POORD037785 2014-12-22 00:00:00.000 127479 0.2564100000
POORD037785 2014-12-22 00:00:00.000 127480 0.2564100000
POORD037785 2014-12-22 00:00:00.000 127481 0.2564100000
POORD037785 2014-12-22 00:00:00.000 127482 0.2564100000
POORD037785 2014-12-22 00:00:00.000 127483 0.2564100000
POORD037701 2014-12-11 00:00:00.000 21979 24.6153850000
POORD037737 2014-12-04 00:00:00.000 23574 1.1965810000
POORD037737 2014-12-04 00:00:00.000 23579 9.8290600000
POORD037737 2014-12-04 00:00:00.000 23410 1.1111110000
POORD037737 2014-12-04 00:00:00.000 23904 0.4444440000
POORD037737 2014-12-04 00:00:00.000 23612 4.3589740000
POORD037737 2014-12-04 00:00:00.000 23622 0.2393160000
POORD037737 2014-12-04 00:00:00.000 23622 0.2393160000
POORD037737 2014-12-04 00:00:00.000 23625 0.5128210000
POORD037737 2014-12-04 00:00:00.000 23625 0.5128210000
POORD037737 2014-12-04 00:00:00.000 23628 0.6837610000
POORD037737 2014-12-04 00:00:00.000 23629 0.7179490000
POORD037737 2014-12-04 00:00:00.000 23629 0.7179490000
POORD037737 2014-12-04 00:00:00.000 23630 0.7350430000
POORD037737 2014-12-04 00:00:00.000 23664 2.1282050000
POORD037737 2014-12-04 00:00:00.000 23838 1.0085470000
POORD037737 2014-12-04 00:00:00.000 23839 2.1367520000
POORD037766 2015-01-23 00:00:00.000 39308 0.9401710000
POORD037766 2015-01-23 00:00:00.000 39308 4.7008550000
POORD037798 2014-12-30 00:00:00.000 127274 42.7350430000
POORD037798 2014-12-30 00:00:00.000 127278 42.7350430000
POORD037798 2014-12-30 00:00:00.000 127345 12.8205130000
POORD037798 2014-12-30 00:00:00.000 127283 8.1196580000
POORD037798 2014-12-30 00:00:00.000 127340 8.1196580000
POORD037893 2014-12-30 00:00:00.000 113706 47.0085470000
POORD037893 2014-12-30 00:00:00.000 113708 72.6495730000
POORD037893 2014-12-30 00:00:00.000 113712 129.0598290000
POORD037893 2014-12-30 00:00:00.000 127655 68.3760680000
POORD037998 2014-12-11 00:00:00.000 22170 8.4200000000
POORD037975 2014-12-24 00:00:00.000 117279 19.2307690000
POORD038009 2015-01-23 00:00:00.000 23320 5.8119660000
POORD038009 2015-01-23 00:00:00.000 23508 6.2393160000
POORD038010 2015-01-23 00:00:00.000 23317 5.8119660000
POORD038010 2015-01-23 00:00:00.000 23503 11.1965810000
------解决思路----------------------
WITH a AS (
select
t2.FCheckDate,
t1.FItemID,
t1.FPrice,
MIN(FBillNo) FBillNo
from POOrderEntry t1
left join POOrder t2
on t1.FInterID = t2.FInterID
GROUP BY t1.FItemID, t2.FCheckDate, t1.FPrice
)
,b AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY FItemID ORDER BY FCheckDate,FBillNo) rn
FROM a
)
SELECT b1.FCheckDate 审核日期,
b0.FPrice 旧采购单价,
b0.FBillNo 旧采购订单号,
b1.FPrice 新采购单价,
b1.FBillNo 新采购订单号,
b1.FItemID 物料内码
FROM b b1
LEFT JOIN b b0
ON b1.FItemID = b0.FItemID
AND b1.rn = b0.rn + 1
WHERE ISNULL(b0.FPrice,-1.0000000000) <> b1.FPrice