我正在嘗試計算每個日歷月的作業日數。例如，11 月份有 22 個作業日（1、2、3、...、22）。周六和周日將被視為之前的周五（例如 11 月 1、2、3、4、5、5、5、6、7、8、9、10、10、10、11、...、22）。
我在下面寫的邏輯不起作用。請問誰能幫我解決這個問題？
SELECT Date
, datepart(DW, DATE) AS DayName
, DATENAME(DW, DATE) ax
, datepart(DW, DATE)-1 AS DayName1
, CASE WHEN datepart(DW,DATE)-1 IN (0,6) THEN 5 ELSE datepart(DW,DATE)-1 END bx
FROM [STAGING_4_6_DIM_CALENDAR_V2]
ORDER BY 1 ASC
uj5u.com熱心網友回復：
我想你可以使用一個運行總和：
WITH cte AS (
SELECT Date
, CASE WHEN DATENAME(WEEKDAY, Date) IN ('SATURDAY', 'SUNDAY') THEN 0 ELSE 1 END AS wd
FROM t
)
SELECT Date, SUM(wd) OVER (PARTITION BY YEAR(Date), MONTH(Date) ORDER BY Date)
FROM cte
2021 年 11 月，它將回傳：
1,2,3,4,5,5,5,6,7,8,9,10,10,10,11,12,13,14,15,15,15,16,17,18,19,20,20,20,21,22
uj5u.com熱心網友回復：
SELECT S.[DATE]
,X.WeekDayNumber
,DATENAME(dw, [DATE]) AS ax
,5 * X.WeekOfMonth
CASE
WHEN X.WeekDayNumber > 5
THEN 5
ELSE X.WeekDayNumber
END
- X.FirstDayOfMonthOffset AS bx
FROM [STAGING_4_6_DIM_CALENDAR_V2] S
CROSS APPLY
(
VALUES
(
-- iso day of week regardless of locale
(DATEPART(dw, [DATE]) @@DATEFIRST 5) % 7 1
-- Zero based week of month
,DATEPART(iso_week, [DATE]) % 53
- DATEPART(iso_week, DATEADD(day, 1, EOMONTH([DATE], -1))) % 53
-- Zero based first day of month offset. Monday = 0 to Sunday =6
,(DATEPART(dw, DATEADD(day, 1, EOMONTH([DATE], -1))) @@DATEFIRST 5) % 7
)
) X (WeekDayNumber, WeekOfMonth, FirstDayOfMonthOffset)
ORDER BY [DATE];
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/348060.html
標籤：sql 日期 查询语句 sql-server-2008 日历