C#到T-SQL-查找最近的工作日

Question

任何C#/T-SQL大师可以帮助我吗？我有一个困难的时间试图将这个C#代码转换为T-SQL。

以下是C代码：

        public (int distance, int absoluteDistance) MinimumDayOfWeekDistance(DayOfWeek dayOfWeekOne,
        DayOfWeek dayOfWeekTwo)
    {
        int forwardDaysDifference(int iteratorDayOfWeekOne, int iteratorDayOfWeekTwo) => iteratorDayOfWeekTwo -
                                                                                         iteratorDayOfWeekOne +
                                                                                         ((iteratorDayOfWeekOne >
                                                                                           iteratorDayOfWeekTwo)
                                                                                             ? 7
                                                                                             : 0);

        int forwardOneTwo = forwardDaysDifference((int) dayOfWeekOne, (int) dayOfWeekTwo);
        int forwardTwoOne = forwardDaysDifference((int) dayOfWeekTwo, (int) dayOfWeekOne);
        if (forwardOneTwo < forwardTwoOne)
        {
            return (forwardOneTwo, forwardOneTwo);
        }
        return (-forwardTwoOne, forwardTwoOne);
    }

    public int DaysToClosestDayOfWeek(DayOfWeek dayOfWeekOne, List<DayOfWeek> dayOfWeekList)
    {
        return dayOfWeekList.Select(dayOfWeek => MinimumDayOfWeekDistance(dayOfWeekOne, dayOfWeek))
            .Aggregate((x, y) => (x.absoluteDistance < y.absoluteDistance)
                ? x
                : ((x.absoluteDistance == y.absoluteDistance) ? ((x.distance > 0) ? x : y) : y)).distance;
    }

以下是实施方案：

int dayOfWeekDistance = this.DaysToClosestDayOfWeek(passedInDateParameter.DayOfWeek, myDayOfWeekList);
passedInDateParameter = passedInDateParameter.AddDays(dayOfWeekDistance);

这个想法是您有一个列表（C#端的DayOfWeekList），该列表可以填写一周中的任何一天。可能是星期一和星期二，可能是星期二、星期三、星期五和星期六，等等。

有一个传入的DateTime参数正在与该列表进行比较，并根据其在列表中最接近的工作日进行更新。

例如，如果传入的DateTime参数是周日，而我的DayOfWeek列表包含周三和周六，则需要将该参数移回周六，因为它在列表中最接近。

类似地，如果我的列表包含星期天、星期一和星期六，并且传入的参数是星期四，那么必须将参数移到星期六。

最后，如果参数与列表中的两个星期等距（周三传入，周一和周五在列表中......或者周日传入，周二和周五在列表中），则参数需要向前移动到下一个最近的星期（在第一种情况下，这将是星期五，在第二种情况下是星期二）。

到目前为止，在SQL方面，我有一个VARCHAR变量，表示基于每个工作日的数字列表。例如，如果星期日、星期一和星期五应该包含在变量中，那么变量将类似于“126”。

此外，在SQL方面，最好返回输入参数与列表中最近的工作日之间的最小距离。

例如，如果我传入一个星期四（或者当前实现的方式为5），而星期日、星期一和星期二在列表中（或1,2和3），则应该返回一个-2（因为星期二最接近列表中的星期四），这样我就可以从我的目标日期时间变量中减去2天（日期时间变量超出了本问题的范围，仅供参考）。

简单地说，输入参数与一周中最近的一天之间的距离应该被返回，它们可以是正的，也可以是负的，以便有效地说明应该增加或减少的天数。

到目前为止，该实现在C#中运行得非常好，我只是很难将其转换为SQL，因为它不是我最好的语言。

在这方面的任何帮助将不胜感激。

Answer 1

我有答案给你。首先，SQL Fiddle

这是SQL：

create table test_Table (Days int);

declare @DaysList int
declare @x int
declare @Day int

set @DaysList =  36 
/*This is the variable that you mention in your original question. 
For example, 36 means Tuesday and Friday*/

set @Day = 1 
/*This is the day that you're checking against*/

set @x = 1
/*This is just our while loop variable*/

WHILE @x <= len(@DaysList)
  BEGIN 
    INSERT INTO test_Table
    SELECT substring(cast(@DaysList as nvarchar(7)),@x,1)
    SET @X = @X + 1 
  END 
/*The while loop is taking each digit in your @DaysList variable 
and putting it in its own row. 
This makes it so that we can compare our day to each day in the list
more easily.*/

;with cte as (SELECT case when abs(@Day-days) < 7-abs(@Day-days)
            then abs(@Day-days) 
            else 7-abs(@Day-days) end daysDiff
/*This field finds the number of days between the @Day value and the day
from @DaysList that we're comparing to. Basically, the smaller the number 
the better.*/
, days
/*This value is going to be the result of our query*/
, case when days-@Day between 0 and 8 
   then days-@Day
   when days+7-@day between 0 and 8
   then days+7-@Day 
   else -100 end a
/*This is doing a bunch of stuff to meet the requirement of finding the "next day" 
if the day is in the next week (for example, if @DaysList is Friday and Sunday (6 and 1)
and our @Day is Saturday (7), we want to show 1, not 6.*/ 
FROM #test_Table)

SELECT TOP 1 days FROM cte ORDER BY DaysDiff Asc, a
/*this just gives us our final result.*/

如果您希望将其作为SQL函数，您可以创建以下函数：

create function ClosestDayToDate(@Day int, @DaysList int)
RETURNS int AS BEGIN 

declare @x int
declare @result int
declare @test_table table (days int)
set @x = 1 

WHILE @x <= len(@DaysList)
  BEGIN 
    INSERT INTO @test_Table
    SELECT substring(cast(@DaysList as nvarchar(7)),@x,1)
    SET @X = @X + 1 
  END 

;with cte as (SELECT case when abs(@Day-days) < 7-abs(@Day-days)
            then abs(@Day-days) 
            else 7-abs(@Day-days) end daysDiff
, days
, case when days-@Day between 0 and 8 
   then days-@Day
   when days+7-@day between 0 and 8
   then days+7-@Day 
   else -100 end a
FROM @test_Table)

Select @Result = (SELECT TOP 1 days FROM cte ORDER BY DaysDiff Asc, a)

return @result
END

然后，您可以运行该函数：

SELECT dbo.ClosestDayToDate(1,72)

这将返回2。

如果你需要更多的澄清，请让我知道。

编辑：我仍然对这个功能着迷，所以我一直在编辑它，使它变短。我认为这是我在不完全重新设计它的编写方式的情况下所能得到的最短时间，不过：

create function ClosestDayToDate(@Day int, @DaysList int)
RETURNS int AS BEGIN 

declare @x int
declare @result int
declare @test_table table (days int)
set @x = 1 

WHILE @x <= len(@DaysList)
  BEGIN 
    INSERT INTO @test_Table
    SELECT substring(cast(@DaysList as nvarchar(7)),@x,1)
    SET @X = @X + 1 
  END 

;with cte as (SELECT days
, case when days-@Day between 0 and 8 
       then days-@Day
       when days+7-@day between 0 and 8
       then days+7-@Day 
       end a

FROM @test_Table)

Select @Result = (SELECT TOP 1 days FROM cte ORDER BY a)

return @result
END

这是该函数的SQL小提琴。

编辑：根据你的要求，我已经更新了功能。现在，它将显示输入的“日”与“日列表”中最近的一天之间的天数如果是前一天，它将显示一个负数。

仅供参考——我没有像以前的版本那样彻底地测试过这个，但在我做的测试中，它工作正常。

create function ClosestDayToDate(@Day int, @DaysList int)
RETURNS int AS BEGIN 

declare @x int
declare @result int
declare @test_table table (days int)
set @x = 1 

WHILE @x <= len(@DaysList)
  BEGIN 
    INSERT INTO @test_Table
    SELECT substring(cast(@DaysList as nvarchar(7)),@x,1)
    SET @X = @X + 1 
  END 

;with cte as (SELECT days
, case when abs(days-@Day) < days+7-@Day then days-@Day else days+7-@Day end b
, case when abs(days-@Day) < days+7-@Day then abs(days-@Day) else days+7-@Day end c         
FROM @test_Table)

Select @Result = (SELECT TOP 1 b FROM cte ORDER BY c, b desc)

return @result
END

还有SQL小提琴。