hi
hope this helps
welcoming any comments 
create sample data script
DECLARE @salary_dates TABLE (yearmonth INT, dt1 DATE, dt2 DATE);
INSERT INTO @salary_dates VALUES
(202506, '2025-05-26','2025-06-25'),
(202507, '2025-06-26','2025-07-25'),
(202508, '2025-07-26','2025-08-25');
CREATE TABLE temp_adv_schedule (
weekno SMALLINT,
emp_code VARCHAR(6),
dt_from DATE,
dt_to DATE,
yearmonth INT,
shift_code VARCHAR(2),
no_of_days SMALLINT,
CONSTRAINT PK_temp_adv_schedule PRIMARY KEY CLUSTERED (emp_code, dt_from, dt_to)
);
INSERT INTO temp_adv_schedule VALUES
(1,'E001','2025-05-26','2025-06-01',202506,'A',7),
(2,'E001','2025-06-02','2025-06-08',202506,'B',7),
(3,'E001','2025-06-09','2025-06-15',202506,'C',7),
(4,'E001','2025-06-16','2025-06-22',202506,'A',7),
(5,'E001','2025-06-23','2025-06-25',202506,'B',3);
INSERT INTO temp_adv_schedule VALUES
(1,'E002','2025-05-26','2025-06-01',202506,'B',7),
(2,'E002','2025-06-02','2025-06-08',202506,'C',7),
(3,'E002','2025-06-09','2025-06-15',202506,'A',7),
(4,'E002','2025-06-16','2025-06-22',202506,'B',7),
(5,'E002','2025-06-23','2025-06-25',202506,'C',3);
INSERT INTO temp_adv_schedule VALUES
(1,'E003','2025-05-26','2025-06-25',202506,'G',31);
INSERT INTO temp_adv_schedule VALUES
(1,'E001','2025-06-26','2025-07-02',202507,'B',7),
(2,'E001','2025-07-03','2025-07-09',202507,'C',7),
(3,'E001','2025-07-10','2025-07-16',202507,'A',7),
(4,'E001','2025-07-17','2025-07-23',202507,'B',7),
(5,'E001','2025-07-24','2025-07-25',202507,'C',2);
INSERT INTO temp_adv_schedule VALUES
(1,'E002','2025-06-26','2025-07-02',202507,'C',7),
(2,'E002','2025-07-03','2025-07-09',202507,'A',7),
(3,'E002','2025-07-10','2025-07-16',202507,'B',7),
(4,'E002','2025-07-17','2025-07-23',202507,'C',7),
(5,'E002','2025-07-24','2025-07-25',202507,'A',2);
INSERT INTO temp_adv_schedule VALUES
(1,'E003','2025-06-26','2025-07-25',202507,'G',30);
t-sql code
DECLARE @targetYearMonth INT = 202508;
;WITH md AS (
SELECT dt1, dt2
FROM @salary_dates
WHERE yearmonth = @targetYearMonth
),
prev AS (
SELECT emp_code, shift_code, no_of_days
FROM temp_adv_schedule t
WHERE yearmonth = @targetYearMonth - 1
AND dt_to = (SELECT MAX(dt_to)
FROM temp_adv_schedule
WHERE yearmonth = @targetYearMonth - 1
AND emp_code = t.emp_code)
),
days AS (
SELECT DATEADD(DAY, v.n-1, md.dt1) AS d, p.emp_code, p.shift_code, p.no_of_days
FROM md
CROSS JOIN prev p
CROSS APPLY (SELECT TOP (DATEDIFF(DAY, md.dt1, md.dt2)+1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.objects) v
),
weeks AS (
SELECT ((ROW_NUMBER() OVER (PARTITION BY emp_code ORDER BY d) + no_of_days - 1)/7)+1 AS weekno,
d, emp_code, shift_code
FROM days
)
INSERT INTO temp_adv_schedule (weekno, emp_code, dt_from, dt_to, yearmonth, shift_code, no_of_days)
SELECT w.weekno, w.emp_code, MIN(d), MAX(d), @targetYearMonth,
CASE WHEN w.shift_code='G' THEN 'G'
ELSE CASE ((w.weekno-1 + ASCII(w.shift_code)-ASCII('A'))%3)
WHEN 0 THEN 'A' WHEN 1 THEN 'B' WHEN 2 THEN 'C' END END,
COUNT(*)
FROM weeks w
GROUP BY w.weekno, w.emp_code, w.shift_code;
Result Set
weekno emp_code dt_from dt_to yearmonth shift_code no_of_days
------ -------- ---------- ---------- --------- ---------- ----------
1 E001 2025-05-26 2025-06-01 202506 A 7
2 E001 2025-06-02 2025-06-08 202506 B 7
3 E001 2025-06-09 2025-06-15 202506 C 7
4 E001 2025-06-16 2025-06-22 202506 A 7
5 E001 2025-06-23 2025-06-25 202506 B 3
1 E001 2025-06-26 2025-07-02 202507 B 7
2 E001 2025-07-03 2025-07-09 202507 C 7
3 E001 2025-07-10 2025-07-16 202507 A 7
4 E001 2025-07-17 2025-07-23 202507 B 7
5 E001 2025-07-24 2025-07-25 202507 C 2
1 E001 2025-07-26 2025-07-30 202508 C 5
2 E001 2025-07-31 2025-08-06 202508 A 7
3 E001 2025-08-07 2025-08-13 202508 B 7
4 E001 2025-08-14 2025-08-20 202508 C 7
5 E001 2025-08-21 2025-08-25 202508 A 5
1 E002 2025-05-26 2025-06-01 202506 B 7
2 E002 2025-06-02 2025-06-08 202506 C 7
3 E002 2025-06-09 2025-06-15 202506 A 7
4 E002 2025-06-16 2025-06-22 202506 B 7
5 E002 2025-06-23 2025-06-25 202506 C 3
1 E002 2025-06-26 2025-07-02 202507 C 7
2 E002 2025-07-03 2025-07-09 202507 A 7
3 E002 2025-07-10 2025-07-16 202507 B 7
4 E002 2025-07-17 2025-07-23 202507 C 7
5 E002 2025-07-24 2025-07-25 202507 A 2
1 E002 2025-07-26 2025-07-30 202508 A 5
2 E002 2025-07-31 2025-08-06 202508 B 7
3 E002 2025-08-07 2025-08-13 202508 C 7
4 E002 2025-08-14 2025-08-20 202508 A 7
5 E002 2025-08-21 2025-08-25 202508 B 5
1 E003 2025-05-26 2025-06-25 202506 G 31
1 E003 2025-06-26 2025-07-25 202507 G 30
5 E003 2025-07-26 2025-07-30 202508 G 5
6 E003 2025-07-31 2025-08-06 202508 G 7
7 E003 2025-08-07 2025-08-13 202508 G 7
8 E003 2025-08-14 2025-08-20 202508 G 7
9 E003 2025-08-21 2025-08-25 202508 G 5