英文:
find first 3 working days in microsoft managment studio
问题
我有一个名为'工作日'的列，它提供了是否是工作日的信息（1表示工作日，0表示周末）。任务是找出每个月的前3个工作日。
我尝试使用以下代码：
SELECT working_day, *
FROM table
WHERE tdate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))
AND working_day = 1
AND tdate = CAST(GETDATE() AS DATE);
英文:
I have a column 'working days' which give info is it working day or no (1 for woking day and 0 for weekend). And the task is to find first 3 working days in each month.
I try to use this code:
SELECT working_day, *
FROM table
WHERE tdate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))
AND working_day = 1
AND tdate = CAST(GETDATE() AS DATE);
答案1
得分: 0
尝试使用以下的 row_number 函数：
WITH CTE AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY YEAR(tdate), MONTH(tdate) ORDER BY tdate) RN
FROM tbl_name
WHERE working_day = 1
)
SELECT tdate, working_day
FROM CTE
WHERE RN <= 3
英文:
Try using the row_number function as the following:
WITH CTE AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY YEAR(tdate), MONTH(tdate) ORDER BY tdate) RN
FROM tbl_name
WHERE working_day = 1
)
SELECT tdate, working_day
FROM CTE
WHERE RN <= 3
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论