在这种情况下有没有办法不使用？ [英] Is there a way to not use a while in this case?

问题描述

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!

根据工作日的意思而定。

就我而言，工作日是我需要去上班（工作）的一天。这并不意味着：星期一到星期五。这意味着：星期一到星期五，节假日除外。每个国家都有很多假期。请参阅：假日 - 维基百科 [ ^ ]



所以，如果你想从星期一到星期五计算工作日数具体日期范围，请检查： sql - 计算两个日期之间的工作日 - Stack Overflow [ ^ ]



在其他情况下，您需要定义假期列表，然后您必须从工作日中减去假期数。



如果您想获得日期范围内的工作日列表，可以使用 CTE [ ^ ]。例如：

Depending on what you mean by saying working days.
As to me, working day is a day when i need to go to work (job). It does not mean: monday to friday. It means: monday to friday excluding holidays. There's a lot of holidays in each country. See: Holiday - Wikipedia[^]

So, if you would like to get count of working days from monday to friday in specific date range, check this: sql - Count work days between two dates - Stack Overflow[^]

In other case, you need to define a list of holidays and then you have to subtract number of holidays from "working days".

In case, you want to get list of working days in a date range, you can use CTE[^]. For example:

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

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