Need To Generate Shift Rotation Weekwise Rather 7 Day Wise For The Yearmonth 202508

Mateen · December 13, 2025, 5:46am

NEED TO GENERATE SHIFT ROTATION WEEKWISE RATHER 7 DAY WISE FOR THE YEARMONTH 202508 from the existing table temp_adv_schedule from prv month 202507

I am using SQL Server 2014

YEARMONTH 202508 date range lies in table @salary_dates

Currently I am doing by writing a piece of code, create first week (weekno)
make some changes in the piece of code , create 2nd week make some changes and 3rd etc
this is laborious and highly prone to error.
I am hoping if a single sql query possible.

The first week should consider how may days were in the previous month
say if a employee has 2 days in prv month last week then current month first week
date range should be 5. and off_course in this case shift should be same because 2+5=7
that is shift will always change after 7 days.
Last week date range many not be 7 days.

shifts are A, B, C, G
G shift no problem full month date range with week no 1
A,B,C should rotate each 7 days
the arrangement may be ABC, BCA OR CAB depending on what was his first week.

A correct procedure/tsql should exactly match the result of
SELECT * FROM TEMP_ADV_SCHEDULE WHERE YEARMONTH = 202508

declare @salary_dates table ( yearmonth int, dt1 datetime, dt2 datetime)
insert  @salary_dates select 202506 , '2025-05-26','2025-06-25'
insert  @salary_dates select 202507 , '2025-06-26','2025-07-25'
insert  @salary_dates select 202508 , '2025-07-26','2025-08-25'

CREATE TABLE temp_adv_schedule ( weekno smallint, emp_code varchar(6) , dt_from datetime, dt_to datetime ,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] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000187',202506,1,'May 26 2025 12:00:00:000AM','Jun 25 2025 12:00:00:000AM','G',31)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000187',202507,1,'Jun 26 2025 12:00:00:000AM','Jul 25 2025 12:00:00:000AM','G',30)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000187',202508,1,'Jul 26 2025 12:00:00:000AM','Aug 25 2025 12:00:00:000AM','G',31)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202506,1,'May 26 2025 12:00:00:000AM','May 27 2025 12:00:00:000AM','B',2)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202506,2,'May 28 2025 12:00:00:000AM','Jun  3 2025 12:00:00:000AM','C',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202506,3,'Jun  4 2025 12:00:00:000AM','Jun 10 2025 12:00:00:000AM','A',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202506,4,'Jun 11 2025 12:00:00:000AM','Jun 17 2025 12:00:00:000AM','B',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202506,5,'Jun 18 2025 12:00:00:000AM','Jun 24 2025 12:00:00:000AM','C',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202506,6,'Jun 25 2025 12:00:00:000AM','Jun 25 2025 12:00:00:000AM','A',1)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202507,1,'Jun 26 2025 12:00:00:000AM','Jul  1 2025 12:00:00:000AM','A',6)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202507,2,'Jul  2 2025 12:00:00:000AM','Jul  8 2025 12:00:00:000AM','B',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202507,3,'Jul  9 2025 12:00:00:000AM','Jul 15 2025 12:00:00:000AM','C',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202507,4,'Jul 16 2025 12:00:00:000AM','Jul 22 2025 12:00:00:000AM','A',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202507,5,'Jul 23 2025 12:00:00:000AM','Jul 25 2025 12:00:00:000AM','B',3)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202508,1,'Jul 26 2025 12:00:00:000AM','Jul 29 2025 12:00:00:000AM','B',4)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202508,2,'Jul 30 2025 12:00:00:000AM','Aug  5 2025 12:00:00:000AM','C',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202508,3,'Aug  6 2025 12:00:00:000AM','Aug 12 2025 12:00:00:000AM','A',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202508,4,'Aug 13 2025 12:00:00:000AM','Aug 19 2025 12:00:00:000AM','B',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000267',202508,5,'Aug 20 2025 12:00:00:000AM','Aug 25 2025 12:00:00:000AM','C',6)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202506,1,'May 26 2025 12:00:00:000AM','May 27 2025 12:00:00:000AM','B',2)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202506,2,'May 28 2025 12:00:00:000AM','Jun  3 2025 12:00:00:000AM','C',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202506,3,'Jun  4 2025 12:00:00:000AM','Jun 10 2025 12:00:00:000AM','A',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202506,4,'Jun 11 2025 12:00:00:000AM','Jun 17 2025 12:00:00:000AM','B',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202506,5,'Jun 18 2025 12:00:00:000AM','Jun 24 2025 12:00:00:000AM','C',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202506,6,'Jun 25 2025 12:00:00:000AM','Jun 25 2025 12:00:00:000AM','A',1)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202507,1,'Jun 26 2025 12:00:00:000AM','Jul  1 2025 12:00:00:000AM','A',6)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202507,2,'Jul  2 2025 12:00:00:000AM','Jul  8 2025 12:00:00:000AM','B',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202507,3,'Jul  9 2025 12:00:00:000AM','Jul 15 2025 12:00:00:000AM','C',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202507,4,'Jul 16 2025 12:00:00:000AM','Jul 22 2025 12:00:00:000AM','A',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202507,5,'Jul 23 2025 12:00:00:000AM','Jul 25 2025 12:00:00:000AM','B',3)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202508,1,'Jul 26 2025 12:00:00:000AM','Jul 29 2025 12:00:00:000AM','B',4)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202508,2,'Jul 30 2025 12:00:00:000AM','Aug  5 2025 12:00:00:000AM','C',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202508,3,'Aug  6 2025 12:00:00:000AM','Aug 12 2025 12:00:00:000AM','A',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202508,4,'Aug 13 2025 12:00:00:000AM','Aug 19 2025 12:00:00:000AM','B',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000509',202508,5,'Aug 20 2025 12:00:00:000AM','Aug 25 2025 12:00:00:000AM','C',6)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202506,1,'May 26 2025 12:00:00:000AM','May 27 2025 12:00:00:000AM','B',2)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202506,2,'May 28 2025 12:00:00:000AM','Jun  3 2025 12:00:00:000AM','C',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202506,3,'Jun  4 2025 12:00:00:000AM','Jun 10 2025 12:00:00:000AM','A',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202506,4,'Jun 11 2025 12:00:00:000AM','Jun 17 2025 12:00:00:000AM','B',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202506,5,'Jun 18 2025 12:00:00:000AM','Jun 24 2025 12:00:00:000AM','C',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202506,6,'Jun 25 2025 12:00:00:000AM','Jun 25 2025 12:00:00:000AM','A',1)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202507,1,'Jun 26 2025 12:00:00:000AM','Jul  1 2025 12:00:00:000AM','A',6)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202507,2,'Jul  2 2025 12:00:00:000AM','Jul  8 2025 12:00:00:000AM','B',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202507,3,'Jul  9 2025 12:00:00:000AM','Jul 15 2025 12:00:00:000AM','C',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202507,4,'Jul 16 2025 12:00:00:000AM','Jul 22 2025 12:00:00:000AM','A',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202507,5,'Jul 23 2025 12:00:00:000AM','Jul 25 2025 12:00:00:000AM','B',3)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202508,1,'Jul 26 2025 12:00:00:000AM','Jul 29 2025 12:00:00:000AM','B',4)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202508,2,'Jul 30 2025 12:00:00:000AM','Aug  5 2025 12:00:00:000AM','C',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202508,3,'Aug  6 2025 12:00:00:000AM','Aug 12 2025 12:00:00:000AM','A',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202508,4,'Aug 13 2025 12:00:00:000AM','Aug 19 2025 12:00:00:000AM','B',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('000699',202508,5,'Aug 20 2025 12:00:00:000AM','Aug 25 2025 12:00:00:000AM','C',6)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('160327',202508,1,'Jul 26 2025 12:00:00:000AM','Jul 29 2025 12:00:00:000AM','B',4)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('160327',202508,2,'Jul 30 2025 12:00:00:000AM','Aug  5 2025 12:00:00:000AM','C',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('160327',202508,3,'Aug  6 2025 12:00:00:000AM','Aug 12 2025 12:00:00:000AM','A',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('160327',202508,4,'Aug 13 2025 12:00:00:000AM','Aug 19 2025 12:00:00:000AM','B',7)
INSERT INTO [temp_adv_schedule] ([emp_code],[yearmonth],[weekno],[dt_from],[dt_to],[shift_code],[no_of_days])VALUES('160327',202508,5,'Aug 20 2025 12:00:00:000AM','Aug 25 2025 12:00:00:000AM','C',6)
```

harishgg1 · December 17, 2025, 11:00am

hi Mateen

'still wondering'

why no replies

Mateen · December 17, 2025, 11:40am

May be
I put the result already in TEMP_ADV_SCHEDULE WHERE YEARMONTH = 202508
I should have populated temp_adv_schedule without yearmonth 202508
and separate the result in another table.
you could do delete from TEMP_ADV_SCHEDULE WHERE YEARMONTH = 202508 and after
that the result i want is

emp_code	dt_from	dt_to	  yearmonth	weekno	shift_code	no_of_days
000187	2025-07-26	2025-08-25	202508	1	        G	      31
000267	2025-07-26	2025-07-29	202508	1 	        B	      4
000267	2025-07-30	2025-08-05	202508	2	        C	      7
000267	2025-08-06	2025-08-12	202508	3	        A	      7
000267	2025-08-13	2025-08-19	202508	4	        B	      7
000267	2025-08-20	2025-08-25	202508	5	        C	      6
000509	2025-07-26	2025-07-29	202508	1	        B	      4
000509	2025-07-30	2025-08-05	202508	2	        C	      7
000509	2025-08-06	2025-08-12	202508	3	        A	      7
000509	2025-08-13	2025-08-19	202508	4	        B	      7
000509	2025-08-20	2025-08-25	202508	5	        C	      6
000699	2025-07-26	2025-07-29	202508	1	        B	      4
000699	2025-07-30	2025-08-05	202508	2	        C	      7
000699	2025-08-06	2025-08-12	202508	3	        A	      7
000699	2025-08-13	2025-08-19	202508	4	        B	      7
000699	2025-08-20	2025-08-25	202508	5	        C	      6
160327	2025-07-26	2025-07-29	202508	1	        B	      4
160327	2025-07-30	2025-08-05	202508	2	        C	      7
160327	2025-08-06	2025-08-12	202508	3	        A	      7
160327	2025-08-13	2025-08-19	202508	4	        B	      7
160327	2025-08-20	2025-08-25	202508	5	        C	      6

ScottPletcher · December 27, 2025, 4:01am


DROP TABLE IF EXISTS #curr_month_last_weeks;
CREATE TABLE #curr_month_last_weeks ( emp_code varchar(6) NOT NULL, weekno smallint NOT NULL, shift_code varchar(2) NOT NULL, 
    no_of_days smallint NOT NULL, remaining_days_in_week smallint NOT NULL, previous_shift_offset smallint NOT NULL );

DECLARE @curr_dt1 datetime;
DECLARE @curr_dt2 datetime;
DECLARE @curr_yearmonth int;
DECLARE @prev_dt1 datetime;
DECLARE @prev_dt2 datetime;
DECLARE @prev_yearmonth int;
DECLARE @shift_codes_array varchar(1000);
DECLARE @total_days smallint;
DECLARE @total_weeks smallint;

SET @shift_codes_array = REPLICATE('A B C ', 500);

/**********************************************************************************************************************/

/* get previous and current yearmonth */
SELECT TOP (1) 
    @prev_yearmonth = MIN(sd.yearmonth),
    @prev_dt1 = MIN(sd.dt1),
    @prev_dt2 = MIN(sd.dt2),
    @curr_yearmonth = MAX(sd.yearmonth),
    @curr_dt1 = MAX(sd.dt1),
    @curr_dt2 = MAX(sd.dt2)
FROM (
    SELECT TOP (2) *
    FROM #salary_dates
    ORDER BY yearmonth DESC
) AS sd
/* SELECT @curr_yearmonth, @curr_dt1, @curr_dt2, @prev_yearmonth, @prev_dt1, @prev_dt2 */

SET @total_days = DATEDIFF(DAY, @curr_dt1, @curr_dt2) + 1;
SET @total_weeks = CEILING(@total_days / 7.0);
/* SELECT @total_days, @total_weeks */

/**********************************************************************************************************************/

INSERT INTO #curr_month_last_weeks ( emp_code, weekno, shift_code, no_of_days, remaining_days_in_week, previous_shift_offset )
SELECT tas.emp_code, tas.weekno, tas.shift_code, tas.no_of_days, 
    CASE WHEN tas.shift_code = 'G' THEN 0 ELSE 7 - tas.no_of_days END AS remaining_days_in_week,
    CASE WHEN tas.shift_code = 'G' THEN 0 
         ELSE CHARINDEX(tas.shift_code, @shift_codes_array) + CASE WHEN tas.no_of_days = 7 THEN 2 ELSE 0 END END AS previous_shift_offset 
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY emp_code ORDER BY weekno DESC) AS row_num
    FROM dbo.temp_adv_schedule 
    WHERE yearmonth = @prev_yearmonth
) AS tas
WHERE tas.row_num = 1

/* SELECT * FROM #curr_month_last_weeks ORDER BY emp_code */

/**********************************************************************************************************************/

/* do shift 'G's separately, since they require no special calcs and produce only 1 row of output */
/* INSERT INTO ... */
SELECT 
    cm.emp_code, 
    @curr_dt1 AS dt_from,
    @curr_dt2 AS dt_to,
    @curr_yearmonth AS yearmonth,
    1 AS weekno, 
    cm.shift_code, 
    DATEDIFF(DAY, @curr_dt1, @curr_dt2) + 1 AS no_of_days
FROM #curr_month_last_weeks cm
WHERE cm.shift_code = 'G'

/**********************************************************************************************************************/

/* calc standard 'A'/'B'/'C'/... shifts */
;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
    SELECT 0 AS number UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
),
cte_tally AS (
    SELECT number AS weekno
    FROM cte_tally1000
    WHERE number BETWEEN 1 AND @total_weeks
)
/* INSERT INTO ... */
SELECT 
    cm.emp_code, 
    @curr_yearmonth AS yearmonth,
    t.weekno AS weekno, 
    dt_from.dt_from,
    dt_to.dt_to,
    SUBSTRING(@shift_codes_array, cm.previous_shift_offset + ((t.weekno - 1)  * 2), 2) AS shift_code,
    DATEDIFF(DAY, dt_from.dt_from, dt_to.dt_to) + 1 AS no_of_days
FROM #curr_month_last_weeks cm
CROSS JOIN cte_tally t 
CROSS APPLY (
    SELECT DATEADD(DAY, CASE t.weekno WHEN 1 THEN 0 ELSE (7 * (t.weekno - 1)) - (cm.remaining_days_in_week - 1) END, @curr_dt1) AS dt_from
) AS dt_from 
CROSS APPLY (
    SELECT CASE WHEN t.weekno = @total_weeks THEN @curr_dt2 
        ELSE DATEADD(DAY, CASE WHEN t.weekno = 1 AND cm.remaining_days_in_week > 0 THEN cm.remaining_days_in_week ELSE 7 END - 1, dt_from.dt_from) END AS dt_to
) AS dt_to
WHERE cm.shift_code <> 'G'
ORDER BY cm.emp_code, t.weekno

ScottPletcher · December 27, 2025, 4:02am

Data setup for previous script:


DROP TABLE IF EXISTS #salary_dates;
CREATE TABLE #salary_dates ( yearmonth int NOT NULL, dt1 datetime NOT NULL, dt2 datetime NOT NULL );
PRINT 'Load salary_dates table.'
insert #salary_dates values( 202506, '2025-05-26', '2025-06-25' )
, ( 202507, '2025-06-26', '2025-07-25' )
, ( 202508, '2025-07-26', '2025-08-25' );

DROP TABLE IF EXISTS dbo.temp_adv_schedule;
PRINT 'Load temp_adv_schedule table.'
CREATE TABLE dbo.temp_adv_schedule ( 
    weekno smallint NOT NULL, 
    emp_code varchar(6) NOT NULL, 
    dt_from datetime NOT NULL, 
    dt_to datetime NOT NULL,
    yearmonth int NOT NULL,
    shift_code varchar(2) NOT NULL, 
    no_of_days smallint NOT NULL, 
    CONSTRAINT [PK_temp_adv_schedule] PRIMARY KEY CLUSTERED ( emp_code, dt_from , dt_to )
    )
INSERT INTO [temp_adv_schedule] ( [emp_code], [yearmonth], [weekno], [dt_from], [dt_to], [shift_code], [no_of_days] )
VALUES('000187',202506,1,'May 26 2025 12:00:00:000AM','Jun 25 2025 12:00:00:000AM','G',31)
,('000187',202507,1,'Jun 26 2025 12:00:00:000AM','Jul 25 2025 12:00:00:000AM','G',30)
,('000187',202508,1,'Jul 26 2025 12:00:00:000AM','Aug 25 2025 12:00:00:000AM','G',31)
,('000267',202506,1,'May 26 2025 12:00:00:000AM','May 27 2025 12:00:00:000AM','B',2)
,('000267',202506,2,'May 28 2025 12:00:00:000AM','Jun  3 2025 12:00:00:000AM','C',7)
,('000267',202506,3,'Jun  4 2025 12:00:00:000AM','Jun 10 2025 12:00:00:000AM','A',7)
,('000267',202506,4,'Jun 11 2025 12:00:00:000AM','Jun 17 2025 12:00:00:000AM','B',7)
,('000267',202506,5,'Jun 18 2025 12:00:00:000AM','Jun 24 2025 12:00:00:000AM','C',7)
,('000267',202506,6,'Jun 25 2025 12:00:00:000AM','Jun 25 2025 12:00:00:000AM','A',1)
,('000267',202507,1,'Jun 26 2025 12:00:00:000AM','Jul  1 2025 12:00:00:000AM','A',6)
,('000267',202507,2,'Jul  2 2025 12:00:00:000AM','Jul  8 2025 12:00:00:000AM','B',7)
,('000267',202507,3,'Jul  9 2025 12:00:00:000AM','Jul 15 2025 12:00:00:000AM','C',7)
,('000267',202507,4,'Jul 16 2025 12:00:00:000AM','Jul 22 2025 12:00:00:000AM','A',7)
,('000267',202507,5,'Jul 23 2025 12:00:00:000AM','Jul 25 2025 12:00:00:000AM','B',3)
,('000267',202508,1,'Jul 26 2025 12:00:00:000AM','Jul 29 2025 12:00:00:000AM','B',4)
,('000267',202508,2,'Jul 30 2025 12:00:00:000AM','Aug  5 2025 12:00:00:000AM','C',7)
,('000267',202508,3,'Aug  6 2025 12:00:00:000AM','Aug 12 2025 12:00:00:000AM','A',7)
,('000267',202508,4,'Aug 13 2025 12:00:00:000AM','Aug 19 2025 12:00:00:000AM','B',7)
,('000267',202508,5,'Aug 20 2025 12:00:00:000AM','Aug 25 2025 12:00:00:000AM','C',6)
,('000509',202506,1,'May 26 2025 12:00:00:000AM','May 27 2025 12:00:00:000AM','B',2)
,('000509',202506,2,'May 28 2025 12:00:00:000AM','Jun  3 2025 12:00:00:000AM','C',7)
,('000509',202506,3,'Jun  4 2025 12:00:00:000AM','Jun 10 2025 12:00:00:000AM','A',7)
,('000509',202506,4,'Jun 11 2025 12:00:00:000AM','Jun 17 2025 12:00:00:000AM','B',7)
,('000509',202506,5,'Jun 18 2025 12:00:00:000AM','Jun 24 2025 12:00:00:000AM','C',7)
,('000509',202506,6,'Jun 25 2025 12:00:00:000AM','Jun 25 2025 12:00:00:000AM','A',1)
,('000509',202507,1,'Jun 26 2025 12:00:00:000AM','Jul  1 2025 12:00:00:000AM','A',6)
,('000509',202507,2,'Jul  2 2025 12:00:00:000AM','Jul  8 2025 12:00:00:000AM','B',7)
,('000509',202507,3,'Jul  9 2025 12:00:00:000AM','Jul 15 2025 12:00:00:000AM','C',7)
,('000509',202507,4,'Jul 16 2025 12:00:00:000AM','Jul 22 2025 12:00:00:000AM','A',7)
,('000509',202507,5,'Jul 23 2025 12:00:00:000AM','Jul 25 2025 12:00:00:000AM','B',3)
,('000509',202508,1,'Jul 26 2025 12:00:00:000AM','Jul 29 2025 12:00:00:000AM','B',4)
,('000509',202508,2,'Jul 30 2025 12:00:00:000AM','Aug  5 2025 12:00:00:000AM','C',7)
,('000509',202508,3,'Aug  6 2025 12:00:00:000AM','Aug 12 2025 12:00:00:000AM','A',7)
,('000509',202508,4,'Aug 13 2025 12:00:00:000AM','Aug 19 2025 12:00:00:000AM','B',7)
,('000509',202508,5,'Aug 20 2025 12:00:00:000AM','Aug 25 2025 12:00:00:000AM','C',6)
,('000699',202506,1,'May 26 2025 12:00:00:000AM','May 27 2025 12:00:00:000AM','B',2)
,('000699',202506,2,'May 28 2025 12:00:00:000AM','Jun  3 2025 12:00:00:000AM','C',7)
,('000699',202506,3,'Jun  4 2025 12:00:00:000AM','Jun 10 2025 12:00:00:000AM','A',7)
,('000699',202506,4,'Jun 11 2025 12:00:00:000AM','Jun 17 2025 12:00:00:000AM','B',7)
,('000699',202506,5,'Jun 18 2025 12:00:00:000AM','Jun 24 2025 12:00:00:000AM','C',7)
,('000699',202506,6,'Jun 25 2025 12:00:00:000AM','Jun 25 2025 12:00:00:000AM','A',1)
,('000699',202507,1,'Jun 26 2025 12:00:00:000AM','Jul  1 2025 12:00:00:000AM','A',6)
,('000699',202507,2,'Jul  2 2025 12:00:00:000AM','Jul  8 2025 12:00:00:000AM','B',7)
,('000699',202507,3,'Jul  9 2025 12:00:00:000AM','Jul 15 2025 12:00:00:000AM','C',7)
,('000699',202507,4,'Jul 16 2025 12:00:00:000AM','Jul 22 2025 12:00:00:000AM','A',7)
,('000699',202507,5,'Jul 23 2025 12:00:00:000AM','Jul 25 2025 12:00:00:000AM','B',3)
,('000699',202508,1,'Jul 26 2025 12:00:00:000AM','Jul 29 2025 12:00:00:000AM','B',4)
,('000699',202508,2,'Jul 30 2025 12:00:00:000AM','Aug  5 2025 12:00:00:000AM','C',7)
,('000699',202508,3,'Aug  6 2025 12:00:00:000AM','Aug 12 2025 12:00:00:000AM','A',7)
,('000699',202508,4,'Aug 13 2025 12:00:00:000AM','Aug 19 2025 12:00:00:000AM','B',7)
,('000699',202508,5,'Aug 20 2025 12:00:00:000AM','Aug 25 2025 12:00:00:000AM','C',6)
,('160327',202508,1,'Jul 26 2025 12:00:00:000AM','Jul 29 2025 12:00:00:000AM','B',4)
,('160327',202508,2,'Jul 30 2025 12:00:00:000AM','Aug  5 2025 12:00:00:000AM','C',7)
,('160327',202508,3,'Aug  6 2025 12:00:00:000AM','Aug 12 2025 12:00:00:000AM','A',7)
,('160327',202508,4,'Aug 13 2025 12:00:00:000AM','Aug 19 2025 12:00:00:000AM','B',7)
,('160327',202508,5,'Aug 20 2025 12:00:00:000AM','Aug 25 2025 12:00:00:000AM','C',6)

Mateen · December 27, 2025, 5:59am

Very Dear ScottPletcher

Despite being not explaining the question properly you must have taken a lot of time by querying the previous data and result data. I appreciate very much your hard work.

There was one more error in my question, 160327 does not exist in previous month and you correctly omitted it.

Again appreciate your valuable time and your effort.

You solution is absolutely correct.

Very best regards.

ScottPletcher · December 27, 2025, 6:03pm

Mateen,

Thank you very much for the nice feedback! I did have to look at the data carefully, but it was an interesting challenge.

ScottPletcher · December 27, 2025, 6:05pm

I did have one naming “error” in mine. Table “#curr_month_last_weeks” should really be “#prev_month_last_weeks”, since that is what the table contains.

Mateen · January 8, 2026, 8:49am

@ScottPletcher

It doesn’t matter. One should work himself after getting ideas.

There is another issue, ( All was correct till yearmonth 202511, issue occurred in 202512) figured out, posting the correct logic for the final select.

The second week was starting one day ahead.

To correct the 1-day gap between weeks (e.g., Week 1 ending 2025-12-02 and Week 2 starting 2025-12-04), we must adjust the CROSS APPLY logic for dt_from.

The gap occurs because the existing formula (7 * (t.weekno - 1)) does not account for the partial days served in the first week of the new month. By subtracting cm.remaining_days_in_week, the formula was pushing the start date out by one extra day.

Fixed Query Fragment

We should replace the final SELECT statement in the procedure with this version:

sql

SELECT 
    cm.emp_code, 
    @curr_yearmonth AS yearmonth,
    t.weekno AS weekno, 
    dt_from.dt_from,
    dt_to.dt_to,
    SUBSTRING(@shift_codes_array, cm.previous_shift_offset + ((t.weekno - 1)  * 2), 2) AS shift_code,
    DATEDIFF(DAY, dt_from.dt_from, dt_to.dt_to) + 1 AS no_of_days
FROM #prv_month_last_weeks cm
CROSS JOIN cte_tally t 
CROSS APPLY (
    -- FIX: Use (7 * (t.weekno - 1)) - (cm.remaining_days_in_week) 
    -- instead of (cm.remaining_days_in_week - 1) to close the 1-day gap.
    SELECT DATEADD(DAY, CASE t.weekno WHEN 1 THEN 0 ELSE (7 * (t.weekno - 1)) - (cm.remaining_days_in_week) END, @curr_dt1) AS dt_from
) AS dt_from 
CROSS APPLY (
    SELECT CASE WHEN t.weekno = @total_weeks THEN @curr_dt2 
        ELSE DATEADD(DAY, CASE WHEN t.weekno = 1 AND cm.remaining_days_in_week > 0 THEN cm.remaining_days_in_week ELSE 7 END - 1, dt_from.dt_from) END AS dt_to
) AS dt_to
WHERE cm.shift_code <> 'G'
ORDER BY cm.emp_code, t.weekno

Mateen · January 8, 2026, 9:02am

May be this happened because of.
Please note that 202511 the last week has 7 days. 0 days left to carry forward to 202512 1st week
Real data with result
``with cte as
(
select yearmonth, weekno, no_of_days,
rn = row_number() over(partition by yearmonth order by weekno desc)
from adv_schedule
where shift_code <> 'G'
)
select yearmonth, weekno as max_weekno, no_of_days
from cte
where rn = 1```

``` yearmonth	max_weekno	no_of_days

202503	4	7
202504	5	3
202505	5	5
202506	6	1
202507	5	3
202508	5	6
202509	6	2
202510	5	4
202511	5	7
202512	5	2
...

harishgg1 · April 16, 2026, 3:03pm

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

Mateen · May 6, 2026, 4:24am

Hi All
This routine is doing shift rotations since last three months. Creator Scott Pletcher

ALTER proc [dbo].[CREATE_SCHEDULE_FROM_PRV_SCHEDULE_SCOTTPLETCHER] as


IF OBJECT_ID('tempdb..#prv_month_last_weeks') IS NOT NULL
    DROP TABLE #prv_month_last_weeks;
CREATE TABLE #prv_month_last_weeks ( emp_code varchar(6) NOT NULL, weekno smallint NOT NULL, shift_code varchar(2) NOT NULL, 
    no_of_days smallint NOT NULL, remaining_days_in_week smallint NOT NULL, previous_shift_offset smallint NOT NULL );

DECLARE @curr_dt1 datetime;
DECLARE @curr_dt2 datetime;
DECLARE @curr_yearmonth int;
DECLARE @prev_dt1 datetime;
DECLARE @prev_dt2 datetime;
DECLARE @prev_yearmonth int;
DECLARE @shift_codes_array varchar(1000);
DECLARE @total_days smallint;
DECLARE @total_weeks smallint;

SET @shift_codes_array = REPLICATE('A B C ', 500);

/**********************************************************************************************************************/

/* get previous and current yearmonth */
SELECT TOP (1) 
    @prev_yearmonth = MIN(sd.yearmonth),
    @prev_dt1 = MIN(sd.dt1),
    @prev_dt2 = MIN(sd.dt2),
    @curr_yearmonth = MAX(sd.yearmonth),
    @curr_dt1 = MAX(sd.dt1),
    @curr_dt2 = MAX(sd.dt2)
FROM (
    SELECT TOP (2) *
    FROM salary_dates
    ORDER BY yearmonth DESC
) AS sd
/* SELECT @curr_yearmonth, @curr_dt1, @curr_dt2, @prev_yearmonth, @prev_dt1, @prev_dt2 */

SET @total_days = DATEDIFF(DAY, @curr_dt1, @curr_dt2) + 1;
SET @total_weeks = CEILING(@total_days / 7.0);
/* SELECT @total_days, @total_weeks */

/**********************************************************************************************************************/

INSERT INTO #prv_month_last_weeks ( emp_code, weekno, shift_code, no_of_days, remaining_days_in_week, previous_shift_offset )
SELECT tas.emp_code, tas.weekno, tas.shift_code, tas.no_of_days, 
    CASE WHEN tas.shift_code = 'G' THEN 0 ELSE 7 - tas.no_of_days END AS remaining_days_in_week,
    CASE WHEN tas.shift_code = 'G' THEN 0 
         ELSE CHARINDEX(tas.shift_code, @shift_codes_array) + CASE WHEN tas.no_of_days = 7 THEN 2 ELSE 0 END END AS previous_shift_offset 
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY emp_code ORDER BY weekno DESC) AS row_num
    FROM adv_schedule 
    WHERE yearmonth = @prev_yearmonth
) AS tas
WHERE tas.row_num = 1;
/**********************************************************************************************************************/

/* do shift 'G's separately, since they require no special calcs and produce only 1 row of output */
delete from adv_schedule where yearmonth = 202604;
insert adv_schedule ( emp_code, dt_from, dt_to, yearmonth, weekno ,shift_code , no_of_days)
SELECT 
    cm.emp_code, 
    @curr_dt1 AS dt_from,
    @curr_dt2 AS dt_to,
    @curr_yearmonth AS yearmonth,
    1 AS weekno, 
    cm.shift_code, 
    DATEDIFF(DAY, @curr_dt1, @curr_dt2) + 1 AS no_of_days
FROM #prv_month_last_weeks cm
WHERE cm.shift_code = 'G';

/**********************************************************************************************************************/

/* calc standard 'A'/'B'/'C'/... shifts */
;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
    SELECT 0 AS number UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
),
cte_tally AS (
    SELECT number AS weekno
    FROM cte_tally1000
    WHERE number BETWEEN 1 AND 10 -- Allow up to 10 to ensure we never cut off week 6
)
-- INSERT INTO adv_schedule ...
insert adv_schedule ( emp_code, dt_from, dt_to, yearmonth, weekno ,shift_code , no_of_days)
SELECT 
    cm.emp_code, 
    st.dt_from,
    dt.dt_to,
    @curr_yearmonth AS yearmonth,
    t.weekno AS weekno, 
    SUBSTRING(@shift_codes_array, cm.previous_shift_offset + ((t.weekno - 1) * 2), 2) AS shift_code,
    DATEDIFF(DAY, st.dt_from, dt.dt_to) + 1 AS no_of_days
FROM #prv_month_last_weeks cm
CROSS JOIN cte_tally t 
CROSS APPLY (
    SELECT 
        CASE 
            WHEN t.weekno = 1 THEN @curr_dt1 
            ELSE DATEADD(DAY, 
                 (CASE WHEN cm.remaining_days_in_week = 7 THEN 7 ELSE cm.remaining_days_in_week END) 
                 + (7 * (t.weekno - 2)), 
                 @curr_dt1) 
        END AS dt_from
) AS st
CROSS APPLY (
    SELECT 
        CASE 
            WHEN t.weekno = 1 THEN 
                CASE WHEN DATEADD(DAY, (CASE WHEN cm.remaining_days_in_week = 7 THEN 7 ELSE cm.remaining_days_in_week END) - 1, st.dt_from) > @curr_dt2 
                     THEN @curr_dt2 
                     ELSE DATEADD(DAY, (CASE WHEN cm.remaining_days_in_week = 7 THEN 7 ELSE cm.remaining_days_in_week END) - 1, st.dt_from) 
                END
            ELSE 
                CASE WHEN DATEADD(DAY, 6, st.dt_from) > @curr_dt2 
                     THEN @curr_dt2 
                     ELSE DATEADD(DAY, 6, st.dt_from) 
                END
        END AS dt_to
) AS dt
WHERE cm.shift_code <> 'G'
  AND st.dt_from <= @curr_dt2 -- This is now the ONLY filter needed to stop the loop
ORDER BY cm.emp_code, t.weekno;


-- Insert new employee August from existing 
insert adv_schedule 
(
  emp_code 
, weekno
, dt_from 
, dt_to   
, office_timein
, office_timeout
, shift_code 
, no_of_days
, schedule_code
, yearmonth
, isnew
 )
select new_emp_code = n.emp_code 
	   , oa2.weekno
       , oa2.dt_from 
	   , oa2.dt_to   
	   , oa2.office_timein
	   , oa2.office_timeout
	   , oa2.shift_code 
	   , oa2.no_of_days
	   , oa2.schedule_code
	   , oa2.yearmonth
	   , isnew=1
from adv_schedule_new n
outer apply ( select top 1 emp_code , yearmonth
              from adv_schedule a
			  where a.yearmonth = n.yearmonth
			  and   a.weekno=1
			  and   a.shift_code = n.shift_code
			  ) oa
outer apply ( select emp_code
                   , dt_from 
				   , dt_to   
				   , office_timein
				   , office_timeout
				   , shift_code 
				   , no_of_days
				   , schedule_code
				   , yearmonth 
				   , weekno
			   from adv_schedule a2
			   where a2.yearmonth = oa.yearmonth
			   and   a2.emp_code = oa.emp_code
			   ) oa2
where n.yearmonth = 202604;

-- Now check that there is no gap
select emp_code, sum(no_of_days), sum(1) over()
from adv_schedule
where yearmonth = 202604
group by emp_code
having sum(no_of_days) <>( select dom from salary_dates sd where monthyear=202604);


```
Thanks All

Mateen · September 11, 2026, 6:41am

========================================================================================================================
DOCUMENTATION: THE GENIUS ROSTER ENGINE ARCHITECTURE
AUTHOR: SCOTT PLETCHER (SQLTEAM.COM) WITH OPERATION MIGRATION BY OPERATIONAL DATA ENGINEER
DATE RECORDED: SEPTEMBER 2026
STATUS: PRODUCTION VERIFIED (PASSES 2,044-ROW INTEL MATRIX FOR 783 EMPLOYEES AT EXACTLY 8.0000000 REST HOURS)
Reason: When i checked rotation end and rotation begin , there was zero error at least 8 hours difference
Reason2: I knew Scottpletcher when he was in expertexchange.com.
He is the most important person who finally made me world class query writer
and world class dba
Deep thanks and love to Scott Pletcher

[SECTION 1: MONUMENTAL TRIBUTE AND ETERNAL GRATITUDE]

To the Legendary Database Architect Scott Pletcher:

This script stands as a timeless masterpiece of relational set theory and high-performance engineering. While lesser
programmers would have trapped the system in slow, resource-heavy cursor loops, you looked past the surface and treated
the database as a pristine geometric grid. You delivered a flawless, zero-latency machine on the very first attempt.

Your brilliant design did not just solve a scheduling problem; it brought absolute life and flawless harmony to a multi-
database infrastructure. By cleanly aligning the complex cross-midnight shift boundaries and preserving the exact 8-hour
rest minimums across thousands of operational rows, your code has safeguarded the wellness of hundreds of working people.

Accept my deepest respect, admiration, highest praise, and infinite love for this contribution. You have given our database
the ultimate shield of integrity. This is what true engineering excellence looks like!

[SECTION 2: STEP-BY-STEP ANATOMY OF THE ARCHITECTURAL GENIUS]

THE INFINITE SHIFT STRING BLANKET (THE TEXT ARRAY VECTOR)
Code: SET @shift_codes_array = REPLICATE('A C B ', 500);
- Insight: Instead of writing heavy nested conditional logic or tracking loops to alter rotation sequences, Scott
  projects the entire corporate shift rotation policy ('A' to 'C' to 'B') out into a massive, continuous physical text
  array. To find any employee's future rotation, the engine calculates a mathematical character offset based on their
  prior month's positioning, then uses SUBSTRING() to pluck the correct code effortlessly out of the memory layer.
THE ZERO-COST CTE TALLY MOTOR (THE RELATIONAL MATRIX GENERATOR)
Code: cte_tally10 AS (SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number))
- Insight: Scott avoids iterative looping entirely by building an on-the-fly math structure called a Tally CTE.
  By cross-joining arrays of zeros in memory, the system creates an instantaneous, zero-cost index table.
  Every single employee record is evaluated against this number grid all at once, mapping out the full 5 or 6 calendar
  weeks of the month simultaneously in a single, atomic database pass.
THE PIPELINED CROSS APPLY DATE CALCULATORS
Code: CROSS APPLY (SELECT CASE WHEN t.weekno = 1 THEN @curr_dt1 ... ) AS st
- Insight: Calculating dynamic start (dt_from) and end (dt_to) boundaries for varying rotational weeks frequently
  results in code bloat. Scott bypasses this cleanly by using CROSS APPLY blocks as inline data pipes. By defining
  'st.dt_from' in the first segment, he passes that result downstream to instantly resolve 'dt.dt_to'. It acts like
  an assembly line, executing complex date math linearly without repeating code fragments.
THE PROUD SUB-CONTINENT NEW HIRE INTEGRATION LAYER (MY EXTENSION EFFECT)
Code: OUTER APPLY (SELECT TOP 1 emp_code ... WHERE a.shift_code = n.shift_code) oa
- Insight: The implementation completes the circle by marrying Scott's core engine with real-world onboarding.
  By utilizing an OUTER APPLY structure, new workers instantly inherit matching, validated rotation structures from
  existing employee patterns based on their assigned shift_code, perfectly preserving the 32-row shift balance matrix.

[SECTION 3: FULL PRODUCTION PROCEDURE SOURCE CODE]

ALTER PROC [dbo].[CREATE_SCHEDULE_FROM_PRV_SCHEDULE_SCOTTPLETCHER] as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT * INTO adv_schedule_202608 FROM adv_schedule; -- Safe backup execution

IF OBJECT_ID('tempdb..#prv_month_last_weeks') IS NOT NULL
DROP TABLE #prv_month_last_weeks;

CREATE TABLE #prv_month_last_weeks (
emp_code varchar(6) NOT NULL
, weekno smallint NOT NULL
, shift_code varchar(2) NOT NULL
, restday_name varchar(15) NOT NULL
, no_of_days smallint NOT NULL
, remaining_days_in_week smallint NOT NULL
, previous_shift_offset smallint NOT NULL
, shift varchar(1) NULL
);

DECLARE @curr_dt1 datetime;
DECLARE @curr_dt2 datetime;
DECLARE @curr_yearmonth int;
DECLARE @prev_dt1 datetime;
DECLARE @prev_dt2 datetime;
DECLARE @prev_yearmonth int;
DECLARE @shift_codes_array varchar(1000);
DECLARE @total_days smallint;
DECLARE @total_weeks smallint;

-- CONFIGURING THE A -> C -> B SHIFT TRANSITION VECTOR
SET @shift_codes_array = REPLICATE('A C B ', 500);

/* Extract operational timelines */
SELECT TOP (1)
@prev_yearmonth = MIN(sd.yearmonth),
@prev_dt1 = MIN(sd.dt1),
@prev_dt2 = MIN(sd.dt2),
@curr_yearmonth = MAX(sd.yearmonth),
@curr_dt1 = MAX(sd.dt1),
@curr_dt2 = MAX(sd.dt2)
FROM (
SELECT TOP (2) *
FROM salary_dates
ORDER BY yearmonth DESC
) AS sd;

SET @total_days = DATEDIFF(DAY, @curr_dt1, @curr_dt2) + 1;
SET @total_weeks = CEILING(@total_days / 7.0);

/* Isolate final week configurations from the previous cycle */
INSERT INTO #prv_month_last_weeks ( emp_code, weekno, shift_code, restday_name, no_of_days, remaining_days_in_week, previous_shift_offset, shift )
SELECT tas.emp_code, tas.weekno, tas.shift_code, tas.restday_name, tas.no_of_days,
CASE WHEN tas.shift_code = 'G' THEN 0 ELSE 7 - tas.no_of_days END AS remaining_days_in_week,
CASE WHEN tas.shift_code = 'G' THEN 0
ELSE CHARINDEX(tas.shift_code, @shift_codes_array) + CASE WHEN tas.no_of_days = 7 THEN 2 ELSE 0 END END AS previous_shift_offset
, tas.shift
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY emp_code ORDER BY weekno DESC) AS row_num
FROM adv_schedule
WHERE yearmonth = @prev_yearmonth
) AS tas
WHERE tas.row_num = 1;

/* Populate static 'G' shifts independently */
DELETE FROM adv_schedule WHERE yearmonth = @curr_yearmonth;

INSERT INTO adv_schedule ( emp_code, dt_from, dt_to, yearmonth, weekno ,shift_code , restday_name, no_of_days, shift)
SELECT
cm.emp_code,
@curr_dt1 AS dt_from,
@curr_dt2 AS dt_to,
@curr_yearmonth AS yearmonth,
1 AS weekno,
cm.shift_code,
cm.restday_name,
DATEDIFF(DAY, @curr_dt1, @curr_dt2) + 1 AS no_of_days,
cm.shift
FROM #prv_month_last_weeks cm
WHERE cm.shift_code = 'G';

/* Generate dynamic shifting blocks via mathematical matrix */
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
SELECT 0 AS number UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
),
cte_tally AS (
SELECT number AS weekno
FROM cte_tally1000
WHERE number BETWEEN 1 AND 10
)
INSERT INTO adv_schedule ( emp_code, dt_from, dt_to, yearmonth, weekno ,shift_code , restday_name, no_of_days, shift)
SELECT
cm.emp_code,
st.dt_from,
dt.dt_to,
@curr_yearmonth AS yearmonth,
t.weekno AS weekno,
SUBSTRING(@shift_codes_array, cm.previous_shift_offset + ((t.weekno - 1) * 2), 2) AS shift_code,
cm.restday_name,
DATEDIFF(DAY, st.dt_from, dt.dt_to) + 1 AS no_of_days,
cm.shift
FROM #prv_month_last_weeks cm
CROSS JOIN cte_tally t
CROSS APPLY (
SELECT
CASE
WHEN t.weekno = 1 THEN @curr_dt1
ELSE DATEADD(DAY,
(CASE WHEN cm.remaining_days_in_week = 7 THEN 7 ELSE cm.remaining_days_in_week END)
+ (7 * (t.weekno - 2)),
@curr_dt1)
END AS dt_from
) AS st
CROSS APPLY (
SELECT
CASE
WHEN t.weekno = 1 THEN
CASE WHEN DATEADD(DAY, (CASE WHEN cm.remaining_days_in_week = 7 THEN 7 ELSE cm.remaining_days_in_week END) - 1, st.dt_from) > @curr_dt2
THEN @curr_dt2
ELSE DATEADD(DAY, (CASE WHEN cm.remaining_days_in_week = 7 THEN 7 ELSE cm.remaining_days_in_week END) - 1, st.dt_from)
END
ELSE
CASE WHEN DATEADD(DAY, 6, st.dt_from) > @curr_dt2
THEN @curr_dt2
ELSE DATEADD(DAY, 6, st.dt_from)
END
END AS dt_to
) AS dt
WHERE cm.shift_code <> 'G'
AND st.dt_from <= @curr_dt2
ORDER BY cm.emp_code, t.weekno;

/* Inherit existing matrix profiles for newly hired personnel profiles */
insert adv_schedule
(
emp_code
, weekno
, dt_from
, dt_to
, office_timein
, office_timeout
, shift_code
, restday_name
, no_of_days
, schedule_code
, yearmonth
, isnew
, shift
)
select new_emp_code = n.emp_code
, oa2.weekno
, oa2.dt_from
, oa2.dt_to
, oa2.office_timein
, oa2.office_timeout
, oa2.shift_code
, oa2.restday_name
, oa2.no_of_days
, oa2.schedule_code
, oa2.yearmonth
, isnew=1
, oa2.shift
from adv_schedule_new n
outer apply ( select top 1 emp_code , yearmonth
from adv_schedule a
where a.yearmonth = n.yearmonth
and a.weekno=1
and a.shift_code = n.shift_code
) oa
outer apply ( select emp_code
, dt_from
, dt_to
, office_timein
, office_timeout
, shift_code
, restday_name
, no_of_days
, schedule_code
, yearmonth
, weekno
, shift
from adv_schedule a2
where a2.yearmonth = oa.yearmonth
and a2.emp_code = oa.emp_code
) oa2
where n.yearmonth = 202608

-- verify
select * from adv_schedule where yearmonth=202608;
*/

Mateen · September 11, 2026, 6:45am

@ScottPletcher
Thanks again and please look at the end of this thread.

ScottPletcher · September 13, 2026, 2:06am

Wow, very nice, thank you so much!

harishgg1 · September 13, 2026, 4:13pm

hi mateen

powershell array =data
simple for loop

2 lines of code ... very low cost .. very low resource intensive
very very ^ reliable^

NO SUCH THING AS genius

vegetable vendor ,,, store clerk , 0 experience can do much much better

harishgg1 · September 17, 2026, 12:02pm

hi mateen

$Employees = @(
    @{ EmpCode = "E001"; LastShift = "A" }
    @{ EmpCode = "E002"; LastShift = "C" }
    @{ EmpCode = "E003"; LastShift = "B" }
)

$Rotation = @('A','C','B')


foreach ($Emp in $Employees) {
    $s = $Rotation.IndexOf($Emp.LastShift)
    Write-Host "`nEmployee: $($Emp.EmpCode)"
    1..6 | % { Write-Host "Week $_ = $($Rotation[($s + $_ - 1) % 3])" }
}

Employee: E001
Week 1 = A
Week 2 = C
Week 3 = B
Week 4 = A
Week 5 = C
Week 6 = B

Employee: E002
Week 1 = C
Week 2 = B
Week 3 = A
Week 4 = C
Week 5 = B
Week 6 = A

Employee: E003
Week 1 = B
Week 2 = A
Week 3 = C
Week 4 = B
Week 5 = A
Week 6 = C

harishgg1 · September 23, 2026, 12:21pm

python REGEX will be "2 words" code

TRIED IT

SAYS NO HABLO CO PILOT