'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 |
|---|
