C＃到T-SQL - 找到最近的工作日-Java 学习之路

任何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，因为它不是我最好的语言 .

任何有关这方面的帮助将不胜感激 .

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 Fiddle .

编辑：根据您的要求，我已更新了该功能 . 现在它将显示投放的“日期”和“日期列表”中最近的日期之间的天数 . 如果当天之前，它将显示负数 .

仅供参考 - 我没有像以前的版本那样详尽地测试过它，但是在我做的测试中，它运行正常 .

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 Fiddle.

回复于 2024-05-03T21:40:48+08:00

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

1 回答

相关问题