我设置了以下脚本作为完整的工作示例：

CREATE TABLE #tmpSampleData(
    Row_no int NOT NULL PRIMARY KEY  
    ,Flag varchar(1) NULL
    ,Curr_value varchar(3) NULL 
); 

INSERT INTO #tmpSampleData (Row_no,Flag,Curr_value) 
VALUES (1, 'C', 'V1')
,(2, 'P', 'V1')
,(3, 'P', 'V1')
,(4, 'C', 'V2')
,(5, 'C', 'V3')
,(6, 'P', 'V3'); 

SELECT 
    sample_data.Row_no
    ,sample_data.Flag
    ,sample_data.Curr_value
    ,LAG(sample_data.Curr_value,1,'0') OVER(ORDER BY sample_data.Row_no) AS Prev_value
    ,CASE 
        WHEN sample_data.Flag = 'C'
            THEN sample_data.Curr_value
        ELSE LAG(sample_data.Curr_value,1,'0') OVER(ORDER BY sample_data.Row_no)
    END AS CalculatedField
FROM 
    #tmpSampleData AS sample_data; 

IF OBJECT_ID(N'tempdb..#tmpSampleData', N'U') IS NOT NULL 
BEGIN 
    DROP TABLE #tmpSampleData; 
END;

但我认为你感兴趣的主要部分是：

CASE 
    WHEN sample_data.Flag = 'C'
        THEN sample_data.Curr_value
    ELSE LAG(sample_data.Curr_value,1,'0') OVER(ORDER BY sample_data.Row_no)
END AS CalculatedField

只需添加sample_data.Flag = 'C'的条件，然后返回Curr_value或'LAG的结果

只需使用滞后功能...

Create table #temp
(
id int,
Fuel int,
dates date
)

Insert into #temp values(1,10,getdate())
Insert into #temp values(2,5,getdate()+1)
Insert into #temp values(3,18,getdate()+2)
Insert into #temp values(4,10,getdate()+3)
Insert into #temp values(5,8,getdate()+4)
Insert into #temp values(6,20,getdate()+5)
Insert into #temp values(7,10,getdate()+6)

Select * from (
SELECT id,
    LAG(Fuel, 1) OVER (ORDER BY id ASC) AS previous_Fuel , Fuel, dates
FROM #temp )A where Isnull(previous_Fuel,0) < Fuel