sql通过打开和关闭时间范围来计算工作日的工作时间

LittleDragon 发表于 Dev

小龙

我想计算给定日期范围（营业日和营业日）之间的营业时间

例如：营业时间

ID WeekName OpeningHour ClosingHour

01 Monday 8.00AM 4.00PM

02 Tuesday 8.00AM 4.00PM

03 Wednesday 8.00AM 4.00PM

04 Thursday 8.00AM 4.00PM

05 Friday 8.00AM 4.00PM

例如，如果我指定日期范围，
营业时间：2014年1
月4日09.00上午营业时间：2014年3月4日上午14.00

我要计算给定日期范围内的营业时间，给定范围之间有3天

2014年1月4日

2014年2月4日

2014/03/04

首先，我想检查日期名称并从营业时间（从上表）计算小时。

2014年1月4日-星期二

2014年2月4日-星期三

2014/03/04-星期四

我期望的结果：

GivenDate DayID DayName DateDiff

01/04/2014 01 Tuesday 7

02/04/2014 02 Wednesday 8

03/04/2014 03 Thursday 6

和

在这里，在Stackoverflow上，通常问一个问题，并期望别人为您做您的工作是不受欢迎的。您至少应该显示一些最小的努力，例如到目前为止已经尝试过的一些SQL。

就是说，这是将返回所需内容的查询。即使没有日期（日历）表，此查询也可以使用，因为它会从系统表中生成数字序列。然后，将这些数字依次添加到开始日期，以返回该间隔中每一天的一条记录：

SELECT * INTO #WorkingHours 
FROM (VALUES (1, 'Monday', '08:00', '16:00')
    ,(2, 'Tuesday', '08:00', '16:00')
    ,(3, 'Wednesday', '08:00', '16:00')
    ,(4, 'Thursday', '08:00', '16:00')
    ,(5, 'Friday', '08:00', '16:00'))
E(DayId, DayName, OpeningHour, ClosingHour)

DECLARE @StartDate DATETIME = '2014-04-01 09:00:00'
DECLARE @EndDate DATETIME = '2014-04-03 14:00:00'

SELECT [GivenDate], [DayID], [DayName],
    DATEDIFF(HOUR, CASE WHEN @StartDate > [OpeningDateTime] THEN @StartDate ELSE [OpeningDateTime] END, 
        CASE WHEN @EndDate < [ClosingDateTime] THEN @EndDate ELSE [ClosingDateTime] END) AS [DateDiff]
FROM (
    SELECT CAST(@StartDate + n - 1 AS DATE) AS [GivenDate]
        , n AS [DayID]
        , DATENAME(dw, @StartDate + n - 1) AS [DayName]
        , CAST(CAST(CAST(@StartDate + n - 1 AS DATE) AS VARCHAR) + ' ' + OpeningHour AS DateTime) AS OpeningDateTime
        , CAST(CAST(CAST(@StartDate + n - 1 AS DATE) AS VARCHAR) + ' ' + ClosingHour AS DateTime) AS ClosingDateTime
    FROM    
        -- Numbers, for expanding the date range:
        (SELECT ROW_NUMBER() OVER (ORDER BY object_id) n FROM sys.all_objects) Numbers
    INNER JOIN #WorkingHours ON DayName = DATENAME(dw, @StartDate + n - 1)
    WHERE Numbers.n <= DATEDIFF(d, @StartDate, @EndDate) + 1
) SubQuery