一个计算每日工时的SQL函数

1 问题

客户需要在指定的日期范围内，计算出每日机器设备的运行时间。

比如机器设备在3月份的运行时间为从 2014-3-20 23:00:00 到 2014-3-22 2:00:00，那么查询该月所得到的结果应该为：

日期	时间
2014-3-20	1小时
2014-3-21	24小时
2014-3-22	2小时

 

2 分析

1）如何将一个日期范围分割成连续的每一天？

2）查询范围和运行时间的关系？

3 实现

自定义SQL函数：

-- =============================================
-- Author:        locus
-- Create date:   2013-12-31
-- Description:   calculate working time for every day in a given date range
-- =============================================
Create FUNCTION [Fun_WorkingTimePerDay] 
(
    -- Add the parameters for the function here
    @lowDate datetime,   --low of date range
    @highDate datetime,  --high of date range
    @startDate datetime, --actual start date
    @endDate datetime    --actual end date
)
RETURNS 
@output TABLE 
(
    -- Add the column definitions for the TABLE variable here
    [Day] datetime, 
    [Seconds] int
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set
    set @lowDate = Convert(char(10), @lowDate, 120)
    set @highDate = Convert(char(10), @highDate, 120)
    if(@endDate is null) set @endDate = @highDate
    declare @day datetime  
    declare @n int
    declare @i int
    set @n = datediff(day, @lowDate, @highDate)
    set @i = 0
    while @i<@n
        begin
            set @day = dateadd(day, @i, @lowDate)
            
            if(datediff(day, @startDate, @day) > 0 and datediff(day, @endDate, @day) = 0)
            --start < day < end < day+1
                insert into @output select @day, datediff(second, @day, @endDate)
            else if(datediff(day, @startDate, @day) = 0 and datediff(day, @endDate, @day) < 0)
            --day < start < day+1 < end
                insert into @output select @day, datediff(second, @startDate, dateadd(day, 1, @day))
            else if(datediff(day, @startDate, @day) = 0 and datediff(day, @endDate, @day) = 0)
            --day < start < end < day+1
                insert into @output select @day, datediff(second, @startDate, @endDate)
            else if(datediff(day, @startDate, @day) > 0 and datediff(day, @endDate, @day) < 0)
            --start < day < day+1 < end
                insert into @output select @day, datediff(second, @day, dateadd(day, 1, @day))
            
            set @i=@i+1
        end    
        
    RETURN 
END

运行：

select * from 
Fun_WorkingTimePerDay('2014-3-1','2014-3-31','2014-3-20 23:00:00','2014-3-25 2:00:00')

结果：