Increment column value based on condition-CodePudding

I'd like to increment the values in the column nc by 1 each time the value in 10minDiff changes. In the Table below, the values in nc should read 2 from row 1246 onwards and 3 the next time 10minDiff changes from 10.

SELECT [Einspeiser/Netzbetreiber],     
       [Stufe%],  
       [Start], 
       [10minDiff],
       [nc] = IIF([10minDiff] = 10, 1, 0) 
FROM 
   (
   SELECT [Einspeiser/Netzbetreiber],      
          [Stufe%],      
          [Start],      
          [10minDiff] = DATEDIFF(MINUTE, LAG([Start]) OVER (ORDER BY [Start]),
          [Start])
   FROM 
    (
            SELECT 'Merkur AC156' AS [Einspeiser/Netzbetreiber],
            [Stufe%] = ROUND([Active power demand setpoint] * 100 / 198, 2), 
            [TimeStampLocalSystem] AS Start
        FROM 
       (
           SELECT [Systemnumber],
           [TimeStampLocalSystem],
           [TimeStampUTCSystem],
           [Minute10Average],
           [Name]
         FROM [SCADACustomerHistorical].[dbo].[CV_English_ChannelData]
         WHERE [TimeStampLocalSystem] 
         BETWEEN 
        '2022-02-01 00:00:00.000' AND '2022-03-31 23:50:00.000'
         AND [Systemnumber] IN ('1082704200')
         AND [Name] 
         IN 
         ('Active power demand setpoint', 
          'Actual active power', 
          'DMI power demand in MW', 
          'Active power reference setpoint')
       )temp_table
     PIVOT 
     (
      SUM(
      [Minute10Average])
       FOR 
       [Name] IN 
      ([Actual active power], 
       [Active power demand setpoint], 
       [DMI power demand in MW], 
        [Active power reference setpoint]
       )
      ) pivot_table
    )tbl2
   WHERE [Stufe%] <> 100
  ) 
 tbl3

ORDER BY [Start];

CodePudding user response：

Do you want something like this ?

id	10mindiff	nc
1238	10	1
1239	10	1
1240	780	0
1241	10	2
1242	10	2
1243	10	2
1244	369	0
1245	10	3
1246	10	3

If that is the case you can use a simple subquery to fetch the number of deviations from 10

declare @table1 table (id int, [10mindiff] int)
insert into @table1 values 
(1238, 10),(1239, 10),(1240, 780),(1241, 10),(1242, 10),(1243, 10),
(1244, 369),(1245, 10),(1246, 10)

select t.id,
       t.[10mindiff],
       case when t.[10mindiff] <> 10 then 0
            else 1   (select count(1) from @table1 t2 where t2.id < t.id and t2.[10mindiff] <> 10)
       end as nc 
from   @table1 t
order by t.id

EDIT

The advantage if this method is that it does not matter in what order you fetch your data

CodePudding user response：

Since data is not textual.I am writing UnTested query.

create  table #temp(id int,t1minDiff varchar(20),t1nc int,t2id int)
insert into #temp
select t1.id,t1.10minDiff,t1.nc ,t2.id as t2id
from tabl11 t1
outer apply(select top 1 t2.id from tabl11 t2 where t2.id>t1.id 
and t1.10minDiff!=t2.10minDiff order by t2.id )oa

--in #temp t2id if not null that need to be updated with next increatment
--Select * from #temp (test this)

update t1
set nc=ca.nc 1
from tabl11 t1
inner #temp t2 on t1.id=t2.t2id
cross apply(select max(nc)nc from #temp t3 where t3.id<t2.t2id )ca

    drop table #temp