主页 / dba / 问题 / 100756
Accepted
gaktheknife
Asked: 2015-05-07 04:59:47 +0800 CST

如何在查询中排除周末和节假日

  • 772

我的任务是创建一个平面文件。我需要的字段之一是日期字段。我需要迪斯科日期为 LAST_EVENT_PROC_DT + 12，除非该日期在周末或节假日，如果是这样，迪斯科日期将是下一个工作日。此日期字段作为数字字段存储在我的系统中。我正在尝试使用我们的假期表和一些周末排除代码来编写它是一个案例陈述。这是我要查询的列：

LAST_EVENT_PROC_DT (numeric(8,0),not null)

它的日期格式如下：20150506

这是平面文件输出：

'' + '|' +
'' + '|' +
cast(q.NEXT_EVENT_PROC_DT as varchar) + '|' + -- Due Date
cast(q.LAST_EVENT_PROC_DT +12 as varchar) + '|' + -- Disco Date = Due date +15
CONVERT(VARCHAR(10),GETDATE() +15,112) + '|' + -- Agency Send Date todays date + 15

我正在使用 SQL Server 2014

sql-server

2 个回答

  1. 您可以简单地使用 sql 的 datediff 函数。然后您可以减去这些日期之间的周末（如果有）。此处将星期日设置为一周的第一天，例如检查下面的查询。

    Declare @startdatee as DateTime
Declare @enddatee as DateTime
Set @startdatee = GETDATE();
set @enddatee = GETDATE()+1;
select @startdatee,@enddatee,(datediff(day,@startdatee,@enddatee+1)-(2)*datediff(week,@startdatee,@enddatee))

    如果您也想排除假期，那么您还可以计算开始/结束日期之间的假期，并可以从最终选择中减去。我在这里给你举了例子。您可以使用您的假期表，这里我只是使用临时表为例。

    Declare @holidays Table (ID int,Name Varchar(50),[Date] date)
Insert into @holidays VALUES 
(1,'holiday1','2018-07-07'),
(2,'holiday2','2018-07-12')

Declare 
    @startdate datetime = '2018-07-02',
    @enddate datetime =  '2018-07-30',
    @holiday int = 0

    Set @holiday = (Select COUNT(*) FROM @holidays WHERE (Date BETWEEN @startdate AND @enddate) 
                    AND DATEPART(Weekday,Date) not in (1,7)
                    )

SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(case datepart(dw, @StartDate) when 1 then 1 else 0 end) 
  -(case datepart(dw, @EndDate) when 7 then 1 else 0 end)
  -@holiday
    • 3
  2. Best Answer

    尽管您无法创建永久表并且您的数据库设计存在严重问题......最好的办法是创建一个临时表，填充它，然后使用查询APPLY获取下一个既不是假期也不是假期的可用日期周末。

    这张桌子的基本设计是从这里提取的，我刚刚添加了周末。

    SET NOCOUNT ON

CREATE TABLE #Dates (
  d DATE,
  isWeekend BIT,
  PRIMARY KEY (d)
)
DECLARE @dIncr DATE = '2000-01-01'
DECLARE @dEnd DATE = '2100-01-01'


WHILE ( @dIncr < @dEnd )
BEGIN

  INSERT INTO #Dates (d, isWeekend) VALUES( @dIncr,  IIF(DATEPART(WEEKDAY, @dIncr) IN (7, 1), 1, 0 ))
  SELECT @dIncr = DATEADD(DAY, 1, @dIncr )
END

    一旦你得到了一个临时表并确定了那些是周末的，构建你的主查询......

    我为这个粗糙表示歉意，上面没有很多你的桌子。 

    SELECT
  '' + '|' +
  '' + '|' +
  cast(q.NEXT_EVENT_PROC_DT as varchar) + '|' + -- Due Date
  cast(dates.d as varchar) + '|' + -- Disco Date = Due date +15
  CONVERT(VARCHAR(10),GETDATE() +15,112) + '|' + -- Agency Send Date todays date + 15
  ...
FROM <TABLE>
CROSS APPLY
(
  SELECT TOP 1 d
  FROM #Dates AS dates
  LEFT OUTER JOIN <holidays> ON holidays.date = dates.d
  WHERE d >= DATEADD(DAY, 12, CAST(CONVERT(CHAR(8), q.LAST_EVENT_PROC_DT) AS DATE))
    AND d.IsWeekend = 0
    AND holidays.date IS NULL
  ORDER BY d ASC
) AS dates
    • 2

相关问题