在SQL Server 2005中,我有此要求,在某些活动中开始和结束日期。
另外,在一天中,有两个时间段,例如从上午5点到上午9点,从下午2点到下午6点,我需要找出此开始日期和结束日期落在这些时间段内的几分钟。
例如,如果开始是24-01-2014 6:30 AM,而Enn是另一天,即25-01-2014 11 AM。结果应该是24日上午9点-6:30,因此150分钟和晚上的全插槽,因此第二天早上240分钟,所以240分钟
总计= 150 240 240。
我正在尝试编写一个函数并在我的查询中使用它。我将通过起点和结束日期到我的功能。
任何帮助都将不胜感激。
我已经开始编写代码,但是它的条件非常多,所以我认为从比我了解更多SQL的人的建议更好。
让我尝试更好地解释。
我有两个日期是开始日期,另一个是结束日期,都有时间。
现在,一天有两个时间段,我想知道这两个日期之间有多少分钟的时间。
因此,如果每天的时间插槽为5至9点,则为16至20 pm。
这两个插槽中有几分钟的开始和结束日期被倒下了?
因此,如果开始日期是24/01/2014 6:30,结束日期为24/01/2014 17:30。
因此,这两个日期使用9-6:30,因此从5到9 am和17:30-16:00 = 1个小时= 1小时距离第二个插槽的2小时30分钟。
这些开始和结束日期可以是同一天的任何日期,也可以是第二天或之后的n天数。
我已经准备了两个用户定义的标量值函数,这些函数与SQL Server 2005中提供的DateTime功能缺乏作用。请注意,所有分数分钟都被截断了(例如00:59:59将切成59分钟)。
基本的前提是,我们将时间域转移到您关心的几分钟中仅考虑。
测试
select dbo.fnDateDiff_SigMinutes('2014-01-24 6:30 AM', '2014-01-24 11:00 AM') --returns 150
select dbo.fnDateDiff_SigMinutes('2014-01-24 6:30 AM', '2014-01-26 11:00 AM') --returns 1110
select dbo.fnDateDiff_SigMinutes('2014-01-24 6:30 AM', '2014-01-25 11:00 AM') --returns 630
select dbo.fnDateDiff_SigMinutes('2014-01-24 6:30 AM', '2014-01-25 5:00 PM') --returns 810
select dbo.fnDateDiff_SigMinutes('2014-01-24 11:00 AM', '2014-01-27 5:00 PM') --returns 1620
select dbo.fnDateDiff_SigMinutes('2014-01-24 11:00 AM', '2014-01-27 5:00 AM') --returns 1200
dbo.fndatediff_sigminutes
CREATE FUNCTION [dbo].[fnDateDiff_SigMinutes](@DateBegin datetime, @DateEnd datetime)
RETURNS int
AS
BEGIN
DECLARE @diff int
--Figure out if the two dates are on the same actual day.
DECLARE @dateOnly_start datetime, @dateOnly_end datetime
select @dateOnly_start = DATEADD(dd, 0, DATEDIFF(dd, 0, @DateBegin))
select @dateOnly_end = DATEADD(dd, 0, DATEDIFF(dd, 0, @DateEnd))
IF @dateOnly_start = @dateOnly_end
begin
--figure out our minutes
declare @minuteBegin int, @minuteEnd int
select @minuteBegin = dbo.fnGetSigMinutes(@DateBegin)
select @minuteEnd = dbo.fnGetSigMinutes(@DateEnd)
select @diff = @minuteEnd - @minuteBegin
end
else
begin
--compute the number of days between the two, multiply result by 480
select @diff = (datediff(day, @DateBegin, @DateEnd)) * 480
if (@diff = 0)
--compute difference between midnight and start, end and midnight
select @diff = @diff + dbo.fnDateDiff_SigMinutes(@DateBegin, dateadd(minute, 1439, @dateOnly_start))
+ dbo.fnDateDiff_SigMinutes(@dateOnly_end, @DateEnd)
else
select @diff = @diff + dbo.fnDateDiff_SigMinutes(@DateBegin, dateadd(day, datediff(day, @DateEnd, @DateBegin), @DateEnd))
end
return @diff
END
dbo.fngetsigminutes
CREATE FUNCTION [dbo].[fnGetSigMinutes](@date DateTime)
RETURNS int
AS
BEGIN
/*
Minutes 0-300 do not count
Minutes 300-540 count
Minutes 540-840 do not count
Minutes 840-1080 count
Minutes 1080-1440 do not count
Therefore, you can shift the minute of the day into a your chosen domain using a stepwise transform:
*/
declare @m int, @ma int --minute (m), minute_adjusted (ma)
select @m = datediff(minute, DATEADD(dd, 0, DATEDIFF(dd, 0, @date)), @date)
select @ma =
case when (@m < 300) then 0
when (@m < 540) then @m - 300
when (@m < 840) then 240
when (@m < 1080) then @m - 600
else 480
end
return @ma
END
一个小改进。
我尊重Mayer06进行分析,努力工作和解决问题的问题。
并认为第一个功能可能会被简化 -
CREATE FUNCTION [dbo].[fnDateDiff_SigMinutes](@DateBegin datetime, @DateEnd datetime)
RETURNS int
AS
BEGIN
DECLARE @diff int
--Figure out if the elapsed days between two dates w/o times, and multiply by 480.
DECLARE @dateOnly_start datetime, @dateOnly_end datetime
select @dateOnly_start = DATEADD(dd, 0, DATEDIFF(dd, 0, @DateBegin))
select @dateOnly_end = DATEADD(dd, 0, DATEDIFF(dd, 0, @DateEnd))
select @diff = (datediff(day, @DateBegin, @DateEnd)) * 480
--figure out our minutes
declare @minuteBegin int, @minuteEnd int
select @minuteBegin = dbo.fnGetSigMinutes(@DateBegin)
select @minuteEnd = dbo.fnGetSigMinutes(@DateEnd)
-- compute the total difference
select @diff = @diff + @minuteEnd - @minuteBegin
return @diff
END
具有原始解决方案的Mayer06的适当学分。