使用函数从TSQL SELECT中删除周末和银行假日（英国）

2 回答

• 0

  SQL Server 2008：

  首先，我会使用一个表来度假，我会插入 all holidays (#Saturdays & Sundays) ：

  CREATE TABLE dbo.Holiday(HolidayDate DATE PRIMARY KEY);
  GO
  INSERT INTO dbo.Holiday(HolidayDate) VALUES ('2013-07-06'); -- Saturday
  GO
  INSERT INTO dbo.Holiday(HolidayDate) VALUES ('2013-07-07'); -- Sunday
  GO
  ...
  

  然后，为了获得两个日期之间的工作日，我会使用此查询：

  DECLARE @StartDate DATE,@EndDate DATE;
  SELECT  @StartDate='2013-07-01',
      @EndDate='2013-07-31';
  
  SELECT  DATEDIFF(DAY,@StartDate,@EndDate) + 1 - COUNT(*) AS WorkingDaysCount
  FROM    dbo.Holyday h
  WHERE   h.HolidayDate BETWEEN @StartDate AND @EndDate;
  

  注意： DATENAME 不确定：

  SET LANGUAGE english;
  SELECT DATENAME(dw, '2013-08-01') AS DateNm_EN;
  GO
  SET LANGUAGE romanian;
  SELECT DATENAME(dw, '2013-08-01') AS DateNm_RO;
  GO
  

  结果：

  DateNm_EN
  ---------
  Thursday
  
  DateNm_RO
  ---------
  joi
  

  Edit 1:

  USE [InvoiceManagement];
  GO
  CREATE FUNCTION dbo.fn_WorkDays_v2 (@StartDate AS DATE, @EndDate AS DATE) -- Arguments should have the same type as column's type
  RETURNS INT
  AS
  BEGIN
      DECLARE @HolidaysCount INT;
  
      SELECT @HolidaysCount=COUNT(*)
      FROM [dbo].[tblBankHolidays] h
      WHERE h.[Date] BETWEEN @StartDate AND @EndDate;
  
      DECLARE @WeekendDaysCount INT;
      WITH N10(Num)
      AS
      (
          SELECT Num FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))  n(Num)
      ), N100(Num)
      AS
      (
          SELECT (a.Num-1)*10 + b.Num AS Num
          FROM N10 a CROSS JOIN N10 b
      ), N10000(Num)
      AS
      (
          SELECT (a.Num-1)*100 + b.Num AS Num
          FROM N100 a CROSS JOIN N100 b
      )
      SELECT @WeekendDaysCount=COUNT(*)
      FROM N10000 n
      WHERE DATEDIFF(DAY,@StartDate,@EndDate) >= n.Num
      AND DATEDIFF(DAY,0, DATEADD(DAY,n.Num-1,@StartDate)) % 7 IN (5,6); -- 5=Saturday, 6=Sunday
  
      RETURN (DATEDIFF(DAY,@StartDate, @EndDate)+1 - @HolidaysCount - @WeekendDaysCount);       
  END
  

  回复于 2024-05-03T05:13:27+08:00
• 3

  我相信我找到了另一种可行的解决方案;一个比上面更简单的解决方案（对我而言）;我认为以上内容对我来说有点难以阅读并理解对不起@Bogdan . 这是使用该函数的脚本，并且当我针对日历检查它们时，似乎显示正确的结果：

  SELECT 
  cir.[PW Number] AS PWNum
  --Get the correct number of working dayts since the order date by using the fn_WorkDays function
  ,CONVERT(VARCHAR(10),singleended2.dbo.fn_WorkDays(cir.[Install Date], GETDATE()),103) AS NumberOfDaysSinceOrderDate 
  ,CONVERT(VARCHAR(10), ISNULL(cir.[Install Date],'01/01/1900'),103) AS  OrderDate--Get the order dates
  ,ISNULL(cirRep.CurrentStage, 'Not Set') AS CurrentStage
  ,cir.[ID] as CircuitID
  FROM Quotebase.dbo.Circuits cir
      LEFT JOIN Quotebase.dbo.CircuitReports cirRep ON Cir.[PW Number] = CirRep.PWNumber
  WHERE Cir.Status='New Circuit Order' 
  ORDER BY Cir.[PW Number]
  

  这是被调用的函数脚本：

  ALTER FUNCTION dbo.fn_WorkDays (@StartDate AS DATETIME, @EndDate AS DATETIME)
  --Define the output data type.
  RETURNS INT
  AS
  --Calculate the RETURN of the function.
  BEGIN
      RETURN (
       SELECT
          (DATEDIFF(dd,@StartDate, @EndDate)+1)--Start with total number of days including weekends +1 Includes the day run
          -(DATEDIFF(wk,@StartDate, @EndDate)*2)--Subtact 2 days for each full weekend
          -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' --If StartDate is a Sunday, Subtract 1
              THEN 1 
              ELSE 0 
          END) 
          -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday'--If EndDate is a Saturday, Subtract 1 
              THEN 1 
              ELSE 0 
          END)
          --Now check if there are any bank holidays between the start and end and remove that amount 
          - (SELECT COUNT(ivm.[Date])
            FROM [EUROPEVUK386].[InvoiceManagement].[dbo].[tblBankHolidays] ivm
            WHERE ivm.[Date] BETWEEN @StartDate AND @EndDate)     
            --WHERE ivm.[Date] BETWEEN '2006-04-14' AND '2006-05-29')     
  )
   END
  GO
  

  如果确实不正确，我可能会去检查并编辑代码 .

  回复于 2024-05-03T05:13:27+08:00