LAG OVER提取调价记录

With purchcal as (
SELECT
	QH_PurchCalculate.FSecondTime 出厂时间,
    QH_PurchCalculate.FBillNo 地磅编号,
	convert(nvarchar(64),QH_PurchCalculate.FCarNo) 车辆,
	QH_PurchCalculateContract.FEPBillNo 金蝶编号,
	convert(nvarchar(64),QH_Supplier.FName) AS [供应商名称],
	QH_Supplier.FBillNo AS [供应商编码],
    convert(nvarchar(64),QH_RawMaterial.FName) AS [物料名称],
	QH_RawMaterial.Fbillno  AS [物料编码],
	QH_PurchCalculateContract.FPrice AS 地磅单价,
	LAG(QH_PurchCalculateContract.FPrice,1) OVER(PARTITION BY QH_Supplier.FBillNo,QH_RawMaterial.Fbillno ORDER BY QH_PurchCalculate.FSecondTime) AS 前单单价
    
FROM            QH_Supplier
	LEFT JOIN QH_PurchCalculate ON QH_Supplier.FID = QH_PurchCalculate.FSupplierID
	LEFT JOIN QH_PurchCalculateContract on QH_PurchCalculateContract.FID = QH_PurchCalculate.FID 
	LEFT JOIN QH_RawMaterial ON QH_RawMaterial.FID = QH_PurchCalculate.FItemID  
WHERE
    QH_PurchCalculate.FCancel <>1 AND 
    QH_PurchCalculate.FDeleted <> 1 AND 
    QH_Supplier.FBillNo NOT IN ('00000') AND
	QH_PurchCalculate.FType <> 1
)
select 
出厂时间
,地磅编号
,金蝶编号
,车辆
,供应商名称
,供应商编码
,物料名称
,物料编码
,地磅单价 AS 现价
,前单单价 AS 原价
,IIF((地磅单价-前单单价)>0,'提价'+FORMAT(ABS(地磅单价-前单单价)/前单单价,'P'), '降价'+FORMAT(ABS(地磅单价-前单单价)/前单单价,'P')) as 备注 
from purchcal 
where 地磅单价-前单单价 <>0 AND 出厂时间 BETWEEN DATEADD(DAY, -15, GETDATE()) AND GETDATE()
ORDER BY 出厂时间