'Combining cte with dates and table with dates between two dates

I have a few pieces of code that give me things that I need, but I need some help for put them together. I have:

create table #test ( job int, dateL date)
insert into #test values
(1, '2022-04-01'),
(2, '2022-04-02'),
(3, '2022-04-12'),
(4, '2022-04-27'),
(5, '2022-05-01')

declare
@startdate date = '2022-04-01',
@enddate date = '2022-04-30'

for that I needed to see in which week of set period named job is appeared, but also I need to find the start date of every week. So I have cte that does that, and then code that finds jobs

;with cte as
(
  select @startDate StartDate
  union all
  select dateadd(ww, 1, StartDate)
  from cte
  where dateadd(ww, 1, StartDate)<=  @endDate
)



SELECT job,dateL,
(
  SELECT CASE 
    WHEN dateL BETWEEN @startdate AND  @enddate THEN DATEDIFF(wk, @startdate, dateL) 
    ELSE -1 
END
)
AS WeekNumber 
from #test

so cte shows every first day of the week:

StartDate
2022-04-01
2022-04-08
2022-04-15
2022-04-22
2022-04-29

and select shows

job dateL   weeknumber
1   2022-04-01  1
2   2022-04-02  1
3   2022-04-12  2
4   2022-04-27  4
5   2022-05-01  -1

How can I unite them, so there would be 4th column, that shows with week number, the start of that week? Thanks!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source