时间戳

首页 > 解决方案 > 如何从持续时间表 SQL Server 中获取不包括定义的工作日的持续时间？

sql - 如何从持续时间表 SQL Server 中获取不包括定义的工作日的持续时间？

问题描述

我试图在每个月的周末以外的两个日期之间获得一个持续时间（如果持续时间结合两个月，我有一个两个表（一个用于获取持续时间，另一个用于获取工作日）

我的对象是：

我想星期五是周末。

 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 中获取不包括定义的周末的持续时间？

标签： sqlsql-server

解决方案

我建议使用递归 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<>fiddle。

推荐阅读