t-sqlentity-framework-core

How in T-SQL can I set the next N isAudits to true every X records?


I'm trying to figure out, either using T-SQL or C# / Entity Framework, how I can mark a batch of N records true every X records that get created.

So here's an example of what the data would be if N = 3 and x = 10. So every 10 records mark the next 3 true...

declare @every int
set @every = 10

declare @batch int
set @batch = 3;

declare @evalData table (evalID int, isAudited bit);

insert into @evalData (evalID, isAudited)
values (1, 0), 
       (2, 1), (3, 1), (4, 1), 
       (5, 0), (6, 0), (7, 0), (8, 0), (9, 0), (10, 0), (11, 0), (12, 0), (13, 0),
       (14, 1), (15, 1), (16, 1), 
       (17, 0), (18, 0), (19, 0), (20, 0), (21, 0), (22, 0), (23, 0), (24, 0), (25, 0),
       (26, 1)

select 
    *, 
    row_number() over (order by evalID) rn
into 
    #ttt
from
    (select top (@batch + @every) *
     from @evalData
     order by evalID desc) as d

select * 
from #ttt 
where rn = (@every + @batch)

--select * from #ttt 
drop table #ttt
--select * from @evalData ed

I thought this would give me a sliding window that would look back to the corresponding place holder from the previous batch ( ie. 3 ) so I could use it as a check for setting isAudit to true for the record I was creating but it seemed really brittle.

Thanks for any help


Solution

  • use the modulus operator %. Since you need to mark @batch of record to true for @every rows, you use @every + @batch as the divisor in the modulus operation

    select * 
    from   #ttt t
           cross apply
           (
               select [every_n] = case when (rn - 1) % (@every + @batch) >= @every
                                       then 1
                                       else 0
                                       end
                              
           ) e
    

    Result (for @every = 5, @batch = 2) :

    evalID isAudited rn every_n
    1 False 1 0
    2 True 2 0
    3 True 3 0
    4 True 4 0
    5 False 5 0
    6 False 6 1
    7 False 7 1
    8 False 8 0
    9 False 9 0
    10 False 10 0
    .. .. .. ..

    db<>fiddle demo