我正在嘗試為兩個日期之間的所有周生成年、ISO 周數和周開始/結束日期的表格。
這有效，除非第 53 周溢位到新的一年。在這種情況下，2020 年第 53 周應跨度為 2020-12-28 至 2021-01-03。相反，因為我按 ISO 周和年分組，所以我得到了 2020 周 53 2020-12-28 到 2020-12-31 的一個記錄，然后是 2021 周 53 2021-01-01 到 2021-的另一個錯誤記錄- 01-03.
DECLARE @windowStart DATE = '20200101',
@windowEnd DATE = '20211031';
;WITH report_dates AS (
SELECT
TOP (DATEDIFF(DAY, @windowStart, @windowEnd) 1)
report_date = DATEADD(DAY,
ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,
@windowStart)
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b
),
report_weeks AS (
SELECT DATEPART(YEAR, report_date) AS report_year,
DATEPART(ISO_WEEK, report_date) AS report_week,
MIN(report_date) AS week_beginning_date,
MAX(report_date) AS week_ending_date
FROM report_dates
GROUP BY DATEPART(YEAR, report_date),
DATEPART(ISO_WEEK, report_date)
)
SELECT * FROM report_weeks ORDER BY week_beginning_date
我還嘗試手動將 week_ending_date 計算為 week_beginning_date 6 - 但我仍然得到從 1 月開始的 2021 年第 53 周的錯誤記錄。我可以再添加一個過濾器來洗掉額外的記錄——也許使用滯后函式來檢測連續的第 53 周記錄并洗掉第二個記錄——但這似乎是比必要的更復雜的解決方案。有沒有更簡單的方法來做到這一點？
這是在 SQLServer
uj5u.com熱心網友回復：
當然，支持ISO_WEEK是很愚蠢的，但是沒有ISO_YEAR，只有YEAR/WEEK組合才有用。
這會根據 ISO 日歷的定義修改您現有的計算 ISO 周星期四的查詢，每個星期四總是在正確的年份：
DECLARE @windowStart DATE = '20200101',
@windowEnd DATE = '20211031';
;WITH report_dates AS (
SELECT
TOP (DATEDIFF(DAY, @windowStart, @windowEnd) 1)
report_date = DATEADD(DAY,
ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,
@windowStart)
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b
),
report_weeks AS (
SELECT DATEPART(YEAR, D.thursday) AS report_year,
DATEPART(ISO_WEEK, D.thursday) AS report_week,
MIN(report_date) AS week_beginning_date,
MAX(report_date) AS week_ending_date
FROM report_dates
-- get thursday of week
CROSS APPLY(VALUES(CAST(DATEADD(DAY, (DATEDIFF(DAY, 0, report_date) / 7) * 7, 3) AS DATE))) AS D(thursday)
GROUP BY D.thursday
)
SELECT * FROM report_weeks ORDER BY week_beginning_date
見小提琴
順便說一句，這是日歷表的完美用例。與其一遍又一遍地重復相同的計算（日期中的一周永遠不會改變），你只需做一次。您可以將任何您需要的日期計算添加為新列，而無需用戶考慮正確的演算法。
uj5u.com熱心網友回復：
我想這就是你想要的：
DECLARE @windowStart DATE = '20200101',
@windowEnd DATE = '20211031';
with report_dates AS
(SELECT @windowStart AS report_date
UNION ALL
SELECT dateadd(wk, 1, report_date) as NextDate FROM report_dates WHERE report_date < @windowEnd)
SELECT DATEPART(YEAR, report_date) AS report_year,
DATEPART(ISO_WEEK, report_date) AS report_week,
dateadd(dd, -datepart(weekday, report_date) 2, report_date) AS week_beginning_date,
dateadd(dd, -datepart(weekday, report_date) 8, report_date) AS week_ending_date
FROM report_dates
在日期部分我申請 2 和 8，因為我的星期從星期一開始。如果您有不同的一周開始日，請查看：https : //docs.microsoft.com/en-us/sql/t-sql/statements/set-datefirst-transact-sql?view=sql-server -ver15
uj5u.com熱心網友回復：
也許...？
DECLARE @windowStart date = '20201220',
@windowEnd date = '20210111';
;
WITH report_dates AS (SELECT TOP (DATEDIFF(DAY, @windowStart, @windowEnd) 1)
report_date = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY a.object_id) - 1, @windowStart)
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b)
SELECT
*,
DATEPART(ISO_WEEK, report_date) AS report_week,
CASE
WHEN DATEPART(ISO_WEEK, report_date) > 50 AND MONTH(report_date) = 1 THEN YEAR(report_date) - 1
WHEN DATEPART(ISO_WEEK, report_date) = 1 AND MONTH(report_date) = 12 THEN YEAR(report_date) 1
ELSE YEAR(report_date)
END AS report_year
FROM report_dates
uj5u.com熱心網友回復：
我接受了 dnoeth 的回答，因為它開箱即用地產生了正確的輸出——我不完全理解“星期四的交叉應用”，所以我需要花一些時間來解決這個問題
Carlos' 本來可以完美運行，但我得到了一些年份值不一致的記錄 - 我需要運行多年，將 MAXRECURSION 設定得這么高讓我感到不合理的緊張。
我確實喜歡 GrahamH 的支票（如果周 > 50 且月 = 一月，則有問題） - 這似乎是最直接的。基于此，這就是我想出的 - 最終我要么使用它，要么使用 dnoeth 的答案（前提是我能自信地理解它）
這也確保了整周都包含在結果集中，以防您的開始/結束日期縮短一周（我沒想到這是一個問題）
DECLARE @windowStart DATE = '20100101',
@windowEnd DATE = '20211031';
;WITH date_list AS (
SELECT
TOP (DATEDIFF(DAY, @windowStart, @windowEnd) 8)
report_date = DATEADD(DAY,
ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,
DATEADD(DAY, -7, @windowStart))
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b
),
report_weeks AS (
SELECT CASE
WHEN DATEPART(ISO_WEEK, report_date) > 50
AND MONTH(report_date) = 1
THEN YEAR(report_date) - 1
WHEN DATEPART(ISO_WEEK, report_date) = 1
AND MONTH(report_date) = 12
THEN YEAR(report_date) 1
ELSE YEAR(report_date) END AS report_year,
DATEPART(ISO_WEEK, report_date) AS report_week,
report_date
FROM date_list
),
min_max_dates AS (
SELECT report_year,
report_week,
MIN(report_date) AS min_window,
MAX(report_date) AS max_window
FROM report_weeks
GROUP BY report_year,
report_week
),
report_windows AS (
SELECT report_year,
report_week,
min_window,
max_window
FROM min_max_dates
WHERE max_window >= @windowStart
AND min_window <= @windowEnd
)
SELECT * FROM report_windows ORDER BY min_window
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/348037.html
標籤：sql sql-server 查询语句