如何从工期表SQL Server中获取排除已定义工作日的工期

Question

我正试图获得每个月除周末外的两个日期之间的持续时间(如果持续时间加起来是两个月，我有一个两张表(一张用于获得持续时间，另一张用于获取工作日(

我的目标是：

• 将持续时间拆分为每个月，例如：

我假设星期五是周末

DayId      DayName
1         Sunday
2         Monday 
3         Tuesday
4         Wednesday
5         Thursday
7         Saturday

持续时间表：

StartDate            Endate          Duration
2021-03-17         2021-03-25           8
2021-03-28         2021-04-02           5

预期结果：

StartDate            Endate          Duration
2021-03-17         2021-03-25           8
2021-03-28         2021-03-31           4
2021-04-01         2021-04-02           1

计算的持续时间(考虑start_date的日期和end_date的日期，休息日除外=>星期五Get_CalendarDateSQL函数：

USE [Attendance]
GO
/****** Object:  UserDefinedFunction [dbo].[Get_Calendar_Date]    Script Date: 3/18/2021 12:26:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Get_Calendar_Date]
(
@StartDate DATETIME
,   @EndDate DATETIME
)
RETURNS TABLE
AS

RETURN
(
SELECT  Tbl_Obj.RNo
,   DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate) AS [Date]
,   DATEPART(quarter,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Quarter]
,   DATEPART(dayofyear,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [DayofYear]
,   DATEPART(WEEK,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [WeekofYear]
,   DATEPART(YEAR,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Year]
,   DATEPART(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Month]
,   DATEPART(DAY,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Day]
,   DATEPART(weekday,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Weekday]
,   DATENAME(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [MonthName]
,   DATENAME(weekday,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [WeekdayName]
,   (RIGHT( REPLICATE('0',(4)) +
CONVERT([VARCHAR],DATEPART(YEAR,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)),0)
,(4)
)+
RIGHT( REPLICATE('0',(2)) +
CONVERT([VARCHAR],DATEPART(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)),0)
,(2)
)
) AS [Vintage]

FROM    ( SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS [RNo]
FROM sys.all_objects WITH (NOLOCK)
) Tbl_Obj

WHERE   DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate) <= @EndDate
)

如何从工期表SQL Server中获取排除已定义周末的工期？

Answer 1

我建议使用递归CTE扩展天数，然后过滤：

with cte as (
select id, startdate, enddate
from duration
union all
select id, dateadd(day, 1, startdate), enddate
from cte
where startdate < enddate
)
select min(startdate), max(startdate),
sum(case when datepart(weekday, startdate) not in (1, 7) then 1 else 0 end) as duration
from cte
group by id, year(startdate), month(startdate);

这假设每一行都有一个id，以便更简单地组合一行的所有结果。。

请注意，这将使用一周中某一天的内置函数。我看不出你的表有什么用，因为它仍然依赖于从datepart()获得星期几。

这里有一个db<gt；不停摆弄