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

harishgg1 · December 17, 2025, 1:09pm

hi mateen

that was not my point

other people need to undestand your explanation and stuff extremely easily

if i put a whole bunch of text where 23 = ert and as the of is

somebody else has to break their head to understand it or keep on asking doubts to you

i noticed these things sometimes i have remote accesed their laptops and helped them
most important concept being "live interaction with them

i have seen the same issues many many many times in this forum

BUT throughout the history of mankind = its always been the same "idiotic" issues
do not know if people do not pay attention or just make it extremely hard on others and expect them to go through the GRIND

please excuse me if am being impolite
my apologies

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.