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