Skip to content
Advertisement

Increment column value based on condition

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.

enter image description here

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];

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement