How to create a specific SQL Server stored procedure?

I have a CONTRACTOR_SCHEDULER table. Letters in columns "Schedule" mean working time (m - 8:00-20:00, n - 20:00-8:00(next day), d - 8:00-8:00(next day), h-day off). (Name, Begin_date) is unique.

Name	Schedule	Begin_date	End_date
John	nhmh	2019-01-01	2019-01-08
John	nnh	2019-01-09	2019-01-25
Kate	dh	2019-01-01	2019-01-07
Kate	mnhdh	2019-01-08	2019-01-14
Mike	nh	2019-01-01	2019-02-01
Mike	mh	2019-02-02	2019-12-31

I need a SQL Server stored procedure that creates new table CONTRACTOR_WORK_DAY of working days using CONTRACTOR_SCHEDULER rows (days off do not appear in table).

Example:

First row - John has schedule nhm. First letter is n so begin_date - 2019-01-01 20:00, End_date - 2019-01-02 08:00. Next letter is h, skip it as a day off. Last letter is m so begin_date - 2019-01-03 08:00, End_date - 2019-01-03 20:00. Repeat schedule until 2019-01-08 - end_date in first table.

Table for the first row of CONTRACTOR_SCHEDULER would be:

Name	Begin_date	End_date
John	2019-01-01 20:00	2019-01-02 08:00
John	2019-01-03 08:00	2019-01-03 20:00
John	2019-01-05 20:00	2019-01-06 08:00
John	2019-01-08 08:00	2019-01-08 20:00

I wrote this in python using some loop over schedule string etc. but can not figure out how to do it in T-SQL for SQL Server.

https://stackoverflow.com/questions/67291662/how-to-create-a-specific-sql-server-stored-procedure April 28, 2021 at 06:58AM