将时间excel公式转换为t-sql

Question

所以我是新的SQL(我相信这是T-SQL)，我正试图转换我在Excel中使用的函数到SQL。

L2成为列1G2成为第2列

=(INT(L2)-INT(G2))*("17:00"-"08:45")+MEDIAN(MOD(L2,1),"17:00","08:45")-MEDIAN(MOD(G2,1),"17:00","08:45")

计算上午8:45到下午05:00之间的营业时间。

如果从下午4点工作到第二天早上9点，结果应该是01:15:00。

如果过几天(1号下午4点到4号上午9点)，应该是17点45分。

我不喜欢有一个单独的函数，因为我不知道如何使用它们，因为我对这个很陌生-我更喜欢把它作为我可以在SELECT * , <code here here> FROM db.name部分中编写的东西。

Thanks in advance

Answer 1

我知道你说你不希望这在一个函数中，但他们真的不是很难使用，你需要的逻辑在SQL Server中太复杂了，不能合理地包含内联(虽然它可以，如果你真的想要那个家伙)。

如果你的任何参数不合适，这个函数没有错误处理，尽管我将把它留给你作为一个关于NULL值的学习练习，过程流和充分思考你可能需要处理的所有可能性:

-- This bit creates your function.  You can rename the function from fnWorkingDays to anything you want, though try to keep your naming conventions sensible:
create function fnWorkingDays(@Start datetime
                            ,@End datetime
                            )
returns decimal(10,2)
as
begin
-- Declare the start and end times of your working day:
declare @WorkingStart time = '08:45:00.000'
declare @WorkingEnd time = '17:00:00.000'

-- Work out the number of minutes outside the working day in 24 Hour Notation:
declare @OvernightMinutes int = datediff(minute                                         -- Work out the difference in minutes,
                                        ,cast(@workingend as datetime)                  -- between the end of the working day (CASTing a TIME as DATETIME gives you 1900-01-01 17:00:00)
                                        ,dateadd(d,1,cast(@WorkingStart as datetime))   -- and the start of the next working day (CAST the TIME value as DATETIME [1900-01-01 08:45:00] and then add a day to it [1900-01-02 08:45:00])
                                        )

-- There is no need to retain the minutes that fall outside your Working Day, to if the very start or very end of your given period fall outside your Working Day, discard those minutes:
declare @TrueStart datetime = (select case when cast(@Start as time) < @WorkingStart
                                        then dateadd(d,datediff(d,0,@Start),0) + cast(@WorkingStart as datetime)
                                        else @Start
                                        end
                                )
declare @TrueEnd datetime = (select case when cast(@End as time) > @WorkingEnd
                                        then dateadd(d,datediff(d,0,@End),0) + cast(@WorkingEnd as datetime)
                                        else @End
                                        end
                                )

-- You can now calculate the number of minutes in your true working period, and then subtract the total overnight periods in minutes to get your final value.
        -- So firstly, if your Working Period is not long enough to stretch over two days, there is not need to do any more than calculate the difference between the True Start and End:
return (select case when datediff(minute,@Start,@End) < @OvernightMinutes
            then datediff(minute,@TrueStart,@TrueEnd)
            -- If you do need to calculate over more than one day, calculate the total minutes between your True Start and End, then subtract the number of Overnight Minutes multiplied by the number of nights.
            -- This works because DATEDIFF calculated the number of boundaries crossed, so when using DAYS, it actually counts the number of midnights between your two dates:
            else (datediff(minute,@TrueStart,@TrueEnd) - (datediff(d,@TrueStart,@TrueEnd) * @OvernightMinutes))/1440.
        -- If you want to return your value in a slightly different format, you could use variations of these two, though you will need to change the RETURNS DECIMAL(10,2) at the top to RETURNS NVARCHAR(25) if you use the last one:
            -- else datediff(minute,@TrueStart,@TrueEnd) - (datediff(d,@TrueStart,@TrueEnd) * @OvernightMinutes)
            -- else cast((datediff(minute,@TrueStart,@TrueEnd) - (datediff(d,@TrueStart,@TrueEnd) * @OvernightMinutes))/60 as nvarchar(5)) + ' Hours ' + cast((datediff(minute,@TrueStart,@TrueEnd) - (datediff(d,@TrueStart,@TrueEnd) * @OvernightMinutes))%60 as nvarchar(5)) + ' Minutes'
            end
            )
end
go

函数是这样调用的:

select dbo.fnWorkingDays('2016-09-04 12:00:00.000', '2016-09-06 12:10:00.000') as WorkingDays

您可以用适当的列名替换关于的两个DATETIME值，以获得所需的内联结果:

select dbo.fnWorkingDays(Dates.StartDate, Dates.EndDate) as WorkingDays
from (select '2016-09-04 12:00:00.000' as StartDate
            ,'2016-09-06 12:10:00.000' as EndDate
            ) as Dates