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
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 |
.. | .. | .. | .. |