我有，例如：
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]
所以我搜索了一個解決方案，但真的能找到一個。并且不確定我是否在正確的軌道上
我需要讓我的輸出看起來像這樣，所以我可以稍后在 ssrs 中按周分組
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
其中 0 不屬于@startdate 和@enddate 之間的范圍。
將不勝感激任何正確的方向！謝謝！
uj5u.com熱心網友回復：
試試這個代碼：
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/468280.html