Define number of week between two dates starting with start of the period-CodePudding

I have, for example:

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'

set @startdateweekstart = (select DATEPART(WEEKDAY, @startdate))
SELECT DATEADD( DAY , @startdateweekstart - DATEPART(WEEKDAY, @startdate), CAST (@startdate AS DATE )) [Week_Start_Date]

Select DATEADD( DAY , @startdateweekstart   6 - DATEPART(WEEKDAY, @startdate), CAST (@startdate AS DATE )) [Week_End_Date]

So I searched for a solution but could really find one. And not sure I am on the right track

I need to get my output look like this, so I could group it later in ssrs by weeks

job  dateL          Week
1    '2022-04-01'    1
2    '2022-04-02'    1
3    '2022-04-12'    2
4    '2022-04-27'    4
5    '2022-05-01'    0

where 0 is something that doesn't belong in range between @startdate and @enddate.

Would be grateful for any kick in the right direction! Thanks!

CodePudding user response：

Try this code:

SELECT job,dateL,
(
  SELECT CASE 
    WHEN dateL BETWEEN @startdate AND  @enddate THEN 
        (SELECT MAX(v) FROM (VALUES (1), (DATEDIFF(wk, @startdate, dateL))) AS VALUE(v)) 
    ELSE 0 
  END
)
AS Week 
FROM #test;