sql - 营业时间逻辑和营业时间的异常(exception)情况

Question

我使用以下内容创建了日期维度:

https://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho

这只是一个标准的，我为 OpeningTime 和 Closing Time 添加了 2 列，其中 Opening 的值为 08:00:00，Closing 的值为 18:00:00。

我还有一个计算字段，它作为非持久计算字段计算开始时间和结束时间之间的分钟差。

我有以下逻辑，但在给你这个之前，让我设置一个场景来显示业务情况的背景，所以他们通常从周一至周五上午 8 点至下午 6 点开放，周六上午 8 点 -下午1点

但是，规则可能有异常(exception)，因为它们可以开放更长的时间，甚至在周日开放。由于某种原因，我下面的代码在周日显示较少，因为在极少数情况下，如果他们在周日早上 8 点到下午 1 点开放以进行争论。显然，无论他们延长开放时间，即使是在周日，我也必须手动将其添加到 Dim_Date 日历中，对于开始时间和结束日期，我都有 00:00:00 ，两者都给我一个 0 表示 datediff(以分钟为单位)默认除非商家另有说明。

但是下面的代码没有考虑到这一点，并且计算得比应有的要少，代码有问题，如果有人可以提供解决方案，基本上我只是希望代码灵活，所以我只进行更改即使是延长工作时间或异常工作时间的日历以及反射(reflect)这一点的代码。谢谢。

declare @Date1 datetime = '2017-08-01 08:00:00'
declare @Date2 datetime = '2017-08-07 09:10:00'


declare @StartTime time = cast(@Date1 as time)
declare @EndTime time = cast(@Date2 as time)
declare @CCStartTime time = (select StartTime from dim_date where id = convert(nvarchar(8),@Date1,112))
declare @CCEndTime time = (select EndTime from dim_date where id = convert(nvarchar(8),@Date2,112))


declare @ActualStart time = (select case 
            when datename(DW,@Date1)='Sunday' then '00:00:00'
            when @StartTime between '00:00:00' and @CCStartTime then @CCStartTime
            when @StartTime between  @CCStartTime and @CCEndTime then @StartTime
            when @StartTime between @CCEndTime and  '23:59:59' then @CCEndTime end)

declare @ActualEnd time = (select case 
            when datename(DW,@Date2)='Sunday' then '00:00:00'
            when @EndTime between '00:00:00' and @CCStartTime then @CCStartTime
            when @EndTime between @CCStartTime and @CCEndTime then @EndTime
            when @EndTime between @CCEndTime and '23:59:59' then @CCEndTime end)


declare @DiffrenceStart int = isnull(DATEDIFF(minute,@CCStartTime,@ActualStart),0)

declare @DiffrenceEnd int = isnull(DATEDIFF(minute,@ActualEnd,@CCEndTime),0)

/*
select @StartTime as StartDate
select @EndTime as EndDate
select @CCStartTime as CCStartDate
select @CCEndTime as CCEndDate
select @ActualStart as ActualStart
select @ActualEnd as ActualEnd
select abs(@DiffrenceStart) as DiffrenceStart 
select abs(@DiffrenceEnd) as DifrenceEnd
*/



select sum(Min)- (@DiffrenceStart + @DiffrenceEnd)
from dim_date
where id between convert(nvarchar(8),@Date1,112) and convert(nvarchar(8),@Date2,112)

Answer 1

看看这个:

declare @ActualEnd time = (select case 
            when datename(DW,@Date2)='Sunday' then '00:00:00'

不应该是:

declare @ActualEnd time = (select case 
            when datename(DW,@Date2)='Sunday' then '23:59:59'