add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tblTimetable
(
PersonID INT,
TimetableID INT
);


INSERT INTO tblTimetable (PersonID, TimetableID)
VALUES (5215, 57), (18943, 221), (18943, 230), (18943, 238),
(21488, 257), (21488, 270), (5215, 67), (5215, 77),
(5215, 87), (5215, 97);
10 rows affected
with PersonCounts as(
select PersonId,count(*)xCnt,row_number()over(order by count(*))grn
from tblTimetable
group by PersonId
)
select * from PersonCounts order by personId
PersonId xCnt grn
5215 5 3
18943 3 2
21488 2 1
with PersonCounts as(
select PersonId,count(*)xCnt,row_number()over(order by count(*))grn
from tblTimetable
group by PersonId
)
,totCounts as(
select PersonId,xCnt,grn, xCnt totCnt
from PersonCounts where grn=1
union all
select t.PersonId,t.xCnt,t.grn,r.totCnt*t.xCnt
from totCounts r
inner join PersonCounts t on t.grn=(r.grn+1)
)
,rcTot as(
select *,max(totCnt)over()totOver from totCounts
)
,PrepRn as(
select PersonId,TimetableID,rQty,rnx
,(rnx-1)*totOver/rqty+1 fromN
,rnx*totOver/rqty toN
,totOver
from(
select t.PersonID,TimetableID
,row_number()over(partition by t.PersonId order by t.TimetableID) rnx
,count(*)over(partition by t.PersonID ) rQty
,totOver
from tblTimetable t
inner join rcTot t2 on t2.PersonID=t.PersonID
)a
)
select * from PrepRn order by personId
PersonId TimetableID rQty rnx fromN toN totOver
5215 57 5 1 1 6 30
5215 67 5 2 7 12 30
5215 77 5 3 13 18 30
5215 87 5 4 19 24 30
5215 97 5 5 25 30 30
18943 221 3 1 1 10 30
18943 230 3 2 11 20 30
18943 238 3 3 21 30 30
21488 257 2 1 1 15 30
21488 270 2 2 16 30 30
with PersonCounts as(
select PersonId,count(*)xCnt,row_number()over(order by count(*))grn
from tblTimetable
group by PersonId
)
,totCounts as(
select PersonId,xCnt,grn, xCnt totCnt
from PersonCounts where grn=1
union all
select t.PersonId,t.xCnt,t.grn,r.totCnt*t.xCnt
from totCounts r
inner join PersonCounts t on t.grn=(r.grn+1)
)
,rcTot as(
select *,max(totCnt)over()totOver from totCounts
)
,PrepRn as(
select PersonId,TimetableID,rQty,rnx
,(rnx-1)*totOver/rqty+1 fromN
,rnx*totOver/rqty toN
,totOver
from(
select t.PersonID,TimetableID
,row_number()over(partition by t.PersonId order by t.TimetableID) rnx
,count(*)over(partition by t.PersonID ) rQty
,totOver
from tblTimetable t
inner join rcTot t2 on t2.PersonID=t.PersonID
)a
)
,rec2 as(
select t.PersonId,TimetableID,fromN rnx,toN ,nn.value grn
,rQty,totOver
from PrepRn t
cross apply generate_series(fromN,toN)nn
)
PersonId TimetableID rnx toN grn rQty totOver
5215 57 1 6 1 5 30
18943 221 1 10 1 3 30
21488 257 1 15 1 2 30
5215 57 1 6 2 5 30
18943 221 1 10 2 3 30
21488 257 1 15 2 2 30
5215 57 1 6 3 5 30
18943 221 1 10 3 3 30
21488 257 1 15 3 2 30
5215 57 1 6 4 5 30
18943 221 1 10 4 3 30
21488 257 1 15 4 2 30
5215 57 1 6 5 5 30
18943 221 1 10 5 3 30
21488 257 1 15 5 2 30
5215 57 1 6 6 5 30
18943 221 1 10 6 3 30
21488 257 1 15 6 2 30
5215 67 7 12 7 5 30
18943 221 1 10 7 3 30
21488 257 1 15 7 2 30
5215 67 7 12 8 5 30
18943 221 1 10 8 3 30
21488 257 1 15 8 2 30
5215 67 7 12 9 5 30
18943 221 1 10 9 3 30
21488 257 1 15 9 2 30
5215 67 7 12 10 5 30
18943 221 1 10 10 3 30
21488 257 1 15 10 2 30
5215 67 7 12 11 5 30
18943 230 11 20 11 3 30
21488 257 1 15 11 2 30
5215 67 7 12 12 5 30
18943 230 11 20 12 3 30
21488 257 1 15 12 2 30
5215 77 13 18 13 5 30
18943 230 11 20 13 3 30
21488 257 1 15 13 2 30
5215 77 13 18 14 5 30
18943 230 11 20 14 3 30
21488 257 1 15 14 2 30
5215 77 13 18 15 5 30
18943 230 11 20 15 3 30
21488 257 1 15 15 2 30
5215 77 13 18 16 5 30
18943 230 11 20 16 3 30
21488 270 16 30 16 2 30
5215 77 13 18 17 5 30
18943 230 11 20 17 3 30
21488 270 16 30 17 2 30
5215 77 13 18 18 5 30
18943 230 11 20 18 3 30
21488 270 16 30 18 2 30
5215 87 19 24 19 5 30
18943 230 11 20 19 3 30
21488 270 16 30 19 2 30
5215 87 19 24 20 5 30
18943 230 11 20 20 3 30
21488 270 16 30 20 2 30
5215 87 19 24 21 5 30
18943 238 21 30 21 3 30
21488 270 16 30 21 2 30
5215 87 19 24 22 5 30
18943 238 21 30 22 3 30
21488 270 16 30 22 2 30
5215 87 19 24 23 5 30
18943 238 21 30 23 3 30
21488 270 16 30 23 2 30
5215 87 19 24 24 5 30
18943 238 21 30 24 3 30
21488 270 16 30 24 2 30
5215 97 25 30 25 5 30
18943 238 21 30 25 3 30
21488 270 16 30 25 2 30
5215 97 25 30 26 5 30
18943 238 21 30 26 3 30
21488 270 16 30 26 2 30
5215 97 25 30 27 5 30
18943 238 21 30 27 3 30
21488 270 16 30 27 2 30
5215 97 25 30 28 5 30
18943 238 21 30 28 3 30
21488 270 16 30 28 2 30
5215 97 25 30 29 5 30
18943 238 21 30 29 3 30
21488 270 16 30 29 2 30
5215 97 25 30 30 5 30
18943 238 21 30 30 3 30
21488 270 16 30 30 2 30