Increment column value based on condition

Question

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

Whitespace and Linebreaks are paramount to making readable text; not just in code. Please get into the habit of making good use of both. Poor/bad formatting is not going to help you or others when you need to be able to quickly read and understand your code. Using indentation, instead of all left aligned text, and line breaks, really helps easily distinguish specific code blocks and sections, and make finding errors far easier when a line only contains 10's of characters, rather than 100's. — Thom A
– Thom A ♦, Commented Apr 1, 2022 at 8:42
You mentioned a trigger in your title, do you really mean you want a TRIGGER? nc is derived by an expression in your query, so it being affected by a TRIGGER is impossible. — Thom A
– Thom A ♦, Commented Apr 1, 2022 at 8:43
@Larnu I meant to effect a change, perhaps i should reword my question — OldNick
– OldNick, Commented Apr 1, 2022 at 8:45
"I have edited the code" and gone back to left aligned >_< Please format your code... — Thom A
– Thom A ♦, Commented Apr 1, 2022 at 9:04

KumarHarsh · Accepted Answer · 2022-04-01 17:51:05Z

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

GuidoG · Accepted Answer · 2022-04-01 09:41:56Z

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

@OldNick,if this work then it can be optmize and it is far better than writing lenghty,complicated and using so many window function.It will work ok in thousands of rows.

Collectives™ on Stack Overflow

Increment column value based on condition

2 Answers 2

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Related