我试图编写sql查询来查找过去的第三个工作日（不包括节假日和周末）。下面是我的方法，但没有给出正确的解决方案。请建议
第一种方法：
alter function getdate(@givendate datetime)
returns datetime
AS
BEGIN
declare @calcdate datetime
declare @reqdate datetime
declare @count bit
set @count=1
select @calcdate=@givendate-4;
select @reqdate=@calcdate;
while(@calcdate<@givendate)
begin
while(@count=1)
begin
if exists(select 1 from dbo.holidays with(Nolock) where dbo.holidays.date=@reqdate)
begin
select @reqdate=@reqdate-1
end
else
begin
set @count=0
end
end
select @calcdate=@calcdate+1
end
return @reqdate
END
第二种方法：
alter function dbo.getpast3day (@date date)
returns datetime
As
begin
declare @reqdate datetime;
declare @Newyears_day datetime;
declare @Martin_Luther_Kin_Birthday datetime;
declare @presidents_day datetime;
declare @Memorial_day datetime;
declare @Independence_day datetime;
declare @Labour_day datetime;
declare @Columbus_day datetime;
declare @Veterans_day datetime;
declare @Thanksgiving_day datetime;
declare @Christmas_day datetime;
SELECT @reqdate= DATEADD(DAY, CASE (DATEPART(WEEKDAY, @date) + @@DATEFIRST) % 7
WHEN 1 THEN -5
WHEN 2 THEN -6
WHEN 3 THEN -6
WHEN 4 THEN -6
WHEN 5 THEN -6
ELSE -4
END, DATEDIFF(DAY, 0, @date)) ;
--@Martin_Luther_Kin_Birthday
begin
WITH dates AS (
SELECT DATEADD(day, number, @date) AS theDate
FROM master.dbo.spt_values
WHERE type = 'P'
)
SELECT @Martin_Luther_Kin_Birthday=theDate
FROM dates
WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
AND DATEPART(day, thedate)>=15 and DATEPART(day, thedate)<=21 and DATEPART(month,thedate)=1 and datepart(year,thedate)= year(@date);
end
--@presidents_day
begin
WITH dates AS (
SELECT DATEADD(day, number, @date) AS theDate
FROM master.dbo.spt_values
WHERE type = 'P'
)
SELECT @presidents_day=theDate
FROM dates
WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
AND DATEPART(day, thedate)>=15 and DATEPART(day, thedate)<=21 and DATEPART(month,thedate)=2 and datepart(year,thedate)= year(@date);
end
--@Memorial_day
begin
WITH dates AS (
SELECT DATEADD(day, number, @date) AS theDate
FROM master.dbo.spt_values
WHERE type = 'P'
)
SELECT @Memorial_day=theDate
FROM dates
WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
AND DATEPART(day, thedate)>=22 and DATEPART(day, thedate)<=31 and DATEPART(month,thedate)=2 and datepart(year,thedate)= year(@date);
end
--@Labour_day
begin
WITH dates AS (
SELECT DATEADD(day, number, @date) AS theDate
FROM master.dbo.spt_values
WHERE type = 'P'
)
SELECT @Labour_day=theDate
FROM dates
WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
AND DATEPART(day, thedate)>=1 and DATEPART(day, thedate)<=7 and DATEPART(month,thedate)=9 and datepart(year,thedate)= year(@date);
end
--@Columbus_day
begin
WITH dates AS (
SELECT DATEADD(day, number, @date) AS theDate
FROM master.dbo.spt_values
WHERE type = 'P'
)
SELECT @Columbus_day=theDate
FROM dates
WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
AND DATEPART(day, thedate)>=8 and DATEPART(day, thedate)<=14 and DATEPART(month,thedate)=10 and datepart(year,thedate)= year(@date);
end
--@Thanksgiving_day
begin
WITH dates AS (
SELECT DATEADD(day, number, @date) AS theDate
FROM master.dbo.spt_values
WHERE type = 'P'
)
SELECT @Thanksgiving_day=theDate
FROM dates
WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
AND DATEPART(day, thedate)>=22 and DATEPART(day, thedate)<=31 and DATEPART(month,thedate)=11 and datepart(year,thedate)= year(@date);
end
if (@reqdate=@Martin_Luther_Kin_Birthday or @reqdate=@presidents_day or @reqdate=@Memorial_day or @reqdate=@Labour_day or @reqdate=@Columbus_day or @reqdate=@Thanksgiving_day)
begin
select @reqdate= DATEADD(day,-6,@reqdate)
end
--RETURN '2216-09-20 00:00:00.000'
return @reqdate
end
最佳答案
对于过去的第三个工作日是什么还不是百分之百的清楚，但也许这能有所帮助。显然你得决定什么是假日。
Select [dbo].[SomeFunctionName]('2016-01-20',3)
退换商品
2016-01-15
自定义函数是：
CREATE FUNCTION [dbo].[SomeFunctionName](@Date date,@nDay int)
Returns Date
As
Begin
Declare @RetVal Date
;with cteHolidays as (
Select * from (Values
('2016-01-01','New Year''s Day'),
('2016-01-18','Martin Luther King, Jr,'),
('2016-02-15','Washington''s Birthday'),
('2016-03-25','Good Friday'),
('2016-05-30','Memorial Day'),
('2016-07-04','Independence Day'),
('2016-09-05','Labor Day'),
('2016-11-24','Thanksgiving'),
('2016-11-25','Black Friday'),
('2016-12-26','Christmas Day')
) as Holidays (Date,Name)
), cteDays as (
Select *
,RowNr = Row_Number() over (Order By RetVal Desc)
From [dbo].[udf-Create-Range-Date](DateAdd(DD,-3*@nDay,@Date),@Date,'DD',1)
Where RetVal not in (Select Date from cteHolidays)
and DatePart(DW,RetVal) between 2 and 6
)
Select @RetVal=RetVal
From cteDays
Where RowNr=@nDay
Return @RetVal
End
它确实需要一个通用的helper函数，我用它来创建动态日期范围
helper函数
CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)
Returns
@ReturnVal Table (RetVal datetime)
As
Begin
With DateTable As (
Select DateFrom = @DateFrom
Union All
Select Case @DatePart
When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
End
From DateTable DF
Where DF.DateFrom < @DateTo
)
Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)
Return
End
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1)
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1)
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15)
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1)
编辑
验证上面列出的示例
Date Weekday BusDayNr
2016-01-20 Wednesday 1 << Selected Date
2016-01-19 Tuesday 2
2016-01-18 Monday - << Holiday Martin Luther King, Jr
2016-01-17 Sunday -
2016-01-16 Saturday -
2016-01-15 Friday 3 << Date Returned
2016-01-14 Thursday
2016-01-13 Wednesday
2016-01-12 Tuesday
2016-01-11 Monday