在这种情况下有没有办法不使用？

问题描述

select @numberOfDays = -@number_of_days;
 select @startDate = getdate();

WHILE @numberOfDays < 0

   begin

      select @startDate = dateadd(dd, -1, @startDate);

      IF (((datepart(weekday, @startDate) + @@datefirst - 2) % 7 + 1 ) = 7)
	  begin
	  select @startDate = dateadd(dd, -1, @startDate) ;
	  end;

      IF (((datepart(weekday, @startDate) + @@datefirst - 2) % 7 + 1 ) = 6)
	  begin
	  select @startDate = dateadd(dd, -1, @startDate) ;
	  end;

      select @numberOfDays = @numberOfDays + 1 ;

end;

我尝试过：



出于显而易见的原因，我不想在sql SP中使用一段时间。

What I have tried:

I don't want to use a while in an sql SP for obvious reasons.

推荐答案

引用：

嘿，我想从当前日期减去工作日。

Hey, i am trying to subtract working days from the current date.

那么？为什么要使用循环？

您需要做的就是从当前日期偏回本周开始。

然后，您可以通过将剩下的数量除以一周中的工作日数来完成整周工作。剩余部分是第一周结束时需要检查工作日的天数

So? Why use a loop?
All you need to do is offset from the current day back to the start of the current week.
You can then work back in integral weeks by dividing what's left by the number of working days in the week. The remainder is the days at the end of the first week that need to be checked for "working days"

working days = working days since start of week + complete weeks * working days in whole week + working days in last part of first week.

这一切都是：重复减法！

That's all division is: repeated subtraction!

DECLARE @numberOfDays INT = 55
DECLARE @startDate DATE = GETDATE()

;WITH WorkingDays AS
(
	--initial part
	SELECT @startDate AS WorkingDate, 0 AS NoOfDays, DATEPART(DW, @startDate) AS PartOfWeek
	--recursive part
	UNION ALL
	SELECT DATEADD(DAY, 1,  WorkingDate) AS WorkingDate, NoOfDays +1  As NoOfDays, DATEPART(DW, DATEADD(DAY, 1,  WorkingDate)) AS PartOfWeek
	FROM WorkingDays
	WHERE NoOfDays < @numberOfDays
)
SELECT * --WorkingDate
FROM WorkingDays
WHERE PartOfWeek <> 1 AND PartOfWeek <> 7

这篇关于在这种情况下有没有办法不使用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！