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 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)
```