To create rows for rest days (+- 7 days) for an entire month from one rest day of the month

Transact-SQL
1

We have a table salarymonth which defines the range of dates for each salary month.
Our month goes from 26 to 25.
For January 2021 and February 2021 it is like.

insert @salarymonth (yearmonth, dt1, dt2)
 values (202101,'2020/12/26','2021/01/25')
,       (202102,'2021/01/26','2021/02/25') 

select * from @salarymonth
yearmonth dt1 dt2
202101 2020-12-26 2021-01-25
202102 2021-01-26 2021-02-25

Due to some porblem , I am finding one restday for each employee for each month
from transactions table in a CTE (reverse engineering).
One record for each employee for each month.

Let's hold the findings in a table variable.

insert @restdays (emp_code, yearmonth, dt,dn)
values ('000042',202101,'2021/01/06','Wednesday')
,      ('000042',202102,'2021/01/27','Wednesday')
,      ('000062',202101,'2020/12/29','Tuesday')
,      ('000062',202102,'2021/02/16','Tuesday') 

select * from @restdays
emp_code yearmonth dt dn
000042 202101 2021-01-06 Wednesday
000042 202102 2021-01-27 Wednesday
000062 202101 2020-12-29 Tuesday
000062 202102 2021-02-16 Tuesday

Now we need to create rows for all rest day dates (+- 7days)
in a month for each employee.

The final result should be

insert @finalresult (emp_code , yearmonth, dt, dn)
values ('000042',202101,'2020/12/30','Wednesday')
,      ('000042',202101,'2021/01/06','Wednesday')
,      ('000042',202101,'2021/01/13','Wednesday')
,      ('000042',202101,'2021/01/20','Wednesday')
,      ('000042',202102,'2021/01/27','Wednesday')
,      ('000042',202102,'2021/02/03','Wednesday')
,      ('000042',202102,'2021/02/10','Wednesday')
,      ('000042',202102,'2021/02/17','Wednesday')
,      ('000042',202102,'2021/02/24','Wednesday')


insert @finalresult (emp_code , yearmonth, dt, dn)
values ('000062',202101,'2020/12/29','Tuesday')
,      ('000062',202101,'2021/01/05','Tuesday')
,      ('000062',202101,'2021/01/12','Tuesday')
,      ('000062',202101,'2021/01/19','Tuesday')
,      ('000062',202102,'2021/01/26','Tuesday')
,      ('000062',202102,'2021/02/02','Tuesday')
,      ('000062',202102,'2021/02/09','Tuesday')
,      ('000062',202102,'2021/02/16','Tuesday')
,      ('000062',202102,'2021/02/23','Tuesday')

select * from @finalresult
emp_code yearmonth dt dn
000042 202101 2020-12-30 Wednesday
000042 202101 2021-01-06 Wednesday
000042 202101 2021-01-13 Wednesday
000042 202101 2021-01-20 Wednesday
000042 202102 2021-01-27 Wednesday
000042 202102 2021-02-03 Wednesday
000042 202102 2021-02-10 Wednesday
000042 202102 2021-02-17 Wednesday
000042 202102 2021-02-24 Wednesday
000062 202101 2020-12-29 Tuesday
000062 202101 2021-01-05 Tuesday
000062 202101 2021-01-12 Tuesday
000062 202101 2021-01-19 Tuesday
000062 202102 2021-01-26 Tuesday
000062 202102 2021-02-02 Tuesday
000062 202102 2021-02-09 Tuesday
000062 202102 2021-02-16 Tuesday
000062 202102 2021-02-23 Tuesday

How to get @finalresult via tsql from @salarymonth, @restdays

I want it in SQL 2008 (I am junior, to upgrade machines and SQL not in my hand)

Regards

2

I have a function that passes in min and max values to generate a tally table, I have put that code into the code here but you can make a TVF out of it.

Create table #salarymonth (yearmonth int, dt1 date, dt2 date)
insert #salarymonth (yearmonth, dt1, dt2)
 values (202101,'2020/12/26','2021/01/25')
,       (202102,'2021/01/26','2021/02/25') 

Create Table #restdays (emp_code int, yearmonth int, dt date,dn varchar(15))
insert #restdays (emp_code, yearmonth, dt,dn)
values ('000042',202101,'2021/01/06','Wednesday')
,      ('000042',202102,'2021/01/27','Wednesday')
,      ('000062',202101,'2020/12/29','Tuesday')
,      ('000062',202102,'2021/02/16','Tuesday') 


; WITH T1(F) AS 
    (
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1
    ),
    T2(F) AS 
        (SELECT 1 FROM T1 A, T1 B),
    T3(F) AS 
        (SELECT 1 FROM T2 A, T2 B),
    T4(F) AS 
        (SELECT 1 FROM T3 A, T3 B)
, cte as (
			select YearMonth,  DateAdd(Day, N, dt1) as dt1, 
				   DatePart(WeekDay, DateAdd(Day, N, dt1)) as DW,
				   dt2 as EndDate
			from #salarymonth
		   Cross Apply (SELECT TOP ((31-(((ABS(0)+0)/2)))+1)
							ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
							+ ((((ABS(0)+0)/2))-1) as N
						FROM T4) V	   
		   )

Select Distinct r.Emp_Code, r.YearMonth , c.dt1 as dt, DatePart(WeekDay, c.dt1) as dn
  from #RestDays r
	join cte c
		on r.YearMonth = c.YearMonth
		and DatePart(WeekDay,r.dt) = c.dw
	order by 1,2
1 Like
3 
declare @salarymonth table ( yearmonth int not null, dt1 date not null, dt2 date not null )
insert @salarymonth (yearmonth, dt1, dt2) values (202101,'2020/12/26','2021/01/25'),(202102,'2021/01/26','2021/02/25') 
declare @restdays table ( emp_code char(6) not null, yearmonth int not null, dt date not null, dn varchar(9) null )
insert @restdays (emp_code, yearmonth, dt,dn) values ('000042',202101,'2021/01/06','Wednesday'),('000042',202102,'2021/01/27','Wednesday')
    ,('000062',202101,'2020/12/29','Tuesday'),('000062',202102,'2021/02/16','Tuesday') 


DECLARE @debug smallint
DECLARE @first_salary_date date
DECLARE @last_salary_date date
SET @debug = 99
--SET @first_salary_date = <hardcode_date_if_you_need_to>

IF @first_salary_date IS NULL
    /*dynamically determine the range of dates to be generated for possible INSERT*/
    SELECT @first_salary_date = MIN(sm.dt1), @last_salary_date = MAX(sm.dt2)
    FROM @salarymonth sm
    INNER JOIN (
        SELECT MIN(rd.yearmonth) AS min_yearmonth, MAX(rd.yearmonth) AS max_yearmonth
        FROM @restdays rd
    ) AS rd ON sm.yearmonth IN (min_yearmonth, max_yearmonth)

IF @debug >= 1
    SELECT @first_salary_date, @last_salary_date


;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_final AS (
    SELECT TOP (DATEDIFF(DAY, @first_salary_date, @last_salary_date) / 7) number AS week_number
    FROM cte_tally1000
    ORDER BY number
),
cte_restdays AS (
    SELECT emp_code, 
        DATEADD(DAY, -7 * (DATEDIFF(DAY, @first_salary_date, MIN(dt)) / 7), MIN(dt)) AS first_rest_date,
        MIN(dn) AS dn
    FROM @restdays
    GROUP BY emp_code
)
--INSERT INTO @restdays ( emp_code, yearmonth, dt, dn )
SELECT rd.emp_code, sm.yearmonth, ca1.rest_date, rd.dn
FROM cte_restdays rd
CROSS JOIN cte_tally_final t
CROSS APPLY (
    SELECT DATEADD(DAY, t.week_number * 7, rd.first_rest_date) AS rest_date
) AS ca1
LEFT OUTER JOIN @salarymonth sm ON ca1.rest_date BETWEEN sm.dt1 AND sm.dt2
WHERE NOT EXISTS (
    /* don't want or need to insert rows that are already in the restdays table */
    SELECT 1
    FROM @restdays rd2
    WHERE rd2.emp_code = rd.emp_code AND
        rd2.dt = ca1.rest_date
    )
ORDER BY emp_code, rest_date
1 Like
4

@ScottPletcher
Giving

emp_code yearmonth rest_date dn
000042 202101 2020-12-30 Wednesday
000042 202101 2021-01-13 Wednesday
000042 202101 2021-01-20 Wednesday
000042 202102 2021-02-03 Wednesday
000042 202102 2021-02-10 Wednesday
000042 202102 2021-02-17 Wednesday
000062 202101 2021-01-05 Tuesday
000062 202101 2021-01-12 Tuesday
000062 202101 2021-01-19 Tuesday
000062 202102 2021-01-26 Tuesday
000062 202102 2021-02-02 Tuesday
000062 202102 2021-02-09 Tuesday

Should give
result of @mike01

Emp_Code YearMonth dt dn
000042 202101 2020-12-30 4
000042 202101 2021-01-06 4
000042 202101 2021-01-13 4
000042 202101 2021-01-20 4
000042 202102 2021-01-27 4
000042 202102 2021-02-03 4
000042 202102 2021-02-10 4
000042 202102 2021-02-17 4
000042 202102 2021-02-24 4
000062 202101 2020-12-29 3
000062 202101 2021-01-05 3
000062 202101 2021-01-12 3
000062 202101 2021-01-19 3
000062 202101 2021-01-26 3
000062 202102 2021-01-26 3
000062 202102 2021-02-02 3
000062 202102 2021-02-09 3
000062 202102 2021-02-16 3
000062 202102 2021-02-23 3
5

Very smart!!!

6

@mike01

Please look row number 14 of your result.

Emp_Code YearMonth dt dn
000062 202101 2021-01-26 3

2021/01/26, its salary month is 202102 and not 202101.

7

@mike
producing 19 rows instead of 18 rows.
Line number 14 seems to be redundant.

8

Change the cte definition to filter on the dt calc.

, cte as (
select YearMonth, DateAdd(Day, N, dt1) as dt1,
DatePart(WeekDay, DateAdd(Day, N, dt1)) as DW,
dt2 as EndDate
from #salarymonth
Cross Apply (SELECT TOP ((31-(((ABS(0)+0)/2)))+1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
+ ((((ABS(0)+0)/2))-1) as N
FROM T4) V
where DateAdd(Day, N, dt1) between dt1 and dt2

1 Like
9

Thanks, it's part of a TVF where I pass in the min/max values that I want. This case we would pass in 0 and the days between the start and end date, but I couldn't attach the sql script. The part that is hardcode would look like this

    SELECT TOP ((@pMax-(((ABS(@pMin)+@pMin)/2)))+1)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
        + ((((ABS(@pMin)+@pMin)/2))-1)
    FROM T4
1 Like
10

As my code indicates, I don't generate rows that are already in the @restdays table, I thought that was your requirement too. You wouldn't want to re-add the row for 000042, 202101, '2021-01-06', 'Wednesday', for example, would you?

YOUR example data showed the day name, not a day number. If you want a day number, that's easy enough to do.

1 Like
11

Thanks

12

Oh! then it's ok

Thanks a lot.

13

Remove the "NOT EXISTS" from the WHERE if you want to see all the rows.

I figured you wouldn't want duplicate rows generated, since you were planning on inserting them into the @restdays table, as best I could tell.

1 Like
14

It's ok.
I have managed it.

Thanks again