sql - 在 T-SQL 中查找企业当前是否营业

Question

我在此表中存储营业时间。企业可以在同一天有多个营业时间。 关闭时间可以是当天午夜之后。

CREATE TABLE [Process].[OpeningHours](
    [openinghoursid] [int] IDENTITY(1,1) NOT NULL,
    [businessid] [int] NOT NULL,
    [daynumber] [int] NOT NULL,
    [opentime] [time](7) NOT NULL,
    [duration] [int] NOT NULL,
    [closetime]  AS (dateadd(minute,[duration],[opentime])
)

此表中的示例数据包括:

INSERT [Process].[OpeningHours]
([openinghoursid], [businessid], [daynumber], [opentime], [duration]) 
VALUES (79, 18, 2, CAST(N'12:00:00' AS Time), 165),
       (80, 18, 2, CAST(N'18:00:00' AS Time), 240),
       (81, 18, 3, CAST(N'12:00:00' AS Time), 165),
       (82, 18, 3, CAST(N'18:00:00' AS Time), 240),
       (83, 18, 4, CAST(N'12:00:00' AS Time), 165),
       (84, 18, 4, CAST(N'18:00:00' AS Time), 240),
       (85, 18, 5, CAST(N'12:00:00' AS Time), 165),
       (86, 18, 5, CAST(N'18:00:00' AS Time), 240),
       (87, 18, 6, CAST(N'12:00:00' AS Time), 165),
       (88, 18, 6, CAST(N'18:00:00' AS Time), 300),
       (89, 18, 7, CAST(N'12:00:00' AS Time), 165),
       (90, 18, 7, CAST(N'18:00:00' AS Time), 600),
       (91, 18, 1, CAST(N'12:00:00' AS Time), 180);

现在我想创建一个函数，该函数返回企业当前是否营业或已关闭的情况。

CREATE FUNCTION [Process].[ufnIsSpaceOpen](@businessid int)  
RETURNS BIT   
AS    
BEGIN  
    DECLARE @currentdatetime DATETIME = GETDATE();  
    DECLARE @dayofweek INT = DATEPART(dw,@currentdatetime);
    DECLARE @currentdate DATETIME = CONVERT(DATE, @currentdatetime);
    DECLARE @isopen BIT;

    SELECT @isopen = COUNT(*)
    FROM Process.OpeningHours
    WHERE 
    daynumber = @dayofweek
    AND businessid = @businessid
    AND
    (   
        @currentdatetime >= @currentdate + CONVERT(DATETIME, opentime)

        AND

        @currentdatetime <= 
            CASE 
                WHEN closetime < '00:00:00' THEN @currentdate + CONVERT(DATETIME, closetime)
                ELSE DATEADD(DAY,1,@currentdate) + CONVERT(DATETIME, closetime)
            END
    );

    RETURN @isopen; 

END;  

GO

我正在使用 COUNT() 来查看是否有任何行与条件匹配，如果 0 匹配则表示已关闭，如果 COUNT() 大于 0 则表示已打开。当关闭时间在当天内时，此功能有效，但当关闭时间在午夜之后或当前时间在午夜之后时，此功能不起作用。

知道如何修复它吗？

编辑:感谢您的所有回复。最后我继续使用@DenisRubashkin 的解决方案。对于任何感兴趣的人来说，这是我使用的最终函数:

CREATE FUNCTION [Process].[ufnIsSpaceOpen](@businessid int)  
RETURNS BIT   
AS    
BEGIN  


    DECLARE @isopen BIT;
    DECLARE @Date DATETIME = GETDATE(); 

    SELECT @isopen = COUNT(*)
    FROM
    (
        SELECT (CAST(CAST(@Date AS DATE) AS DATETIME) + CAST(h.opentime AS DATETIME)) AS Opened,
            DATEADD(mi, h.duration, (CAST(CAST(@Date AS DATE) AS DATETIME) + CAST(h.opentime AS DATETIME))) AS Closed
        FROM Process.OpeningHours h
        WHERE h.daynumber = DATEPART(dw, @Date)
        AND businessid = @businessid

        UNION 

        SELECT (CAST(DATEADD(day, -1, CAST(@Date AS DATE)) AS DATETIME) + CAST(h.opentime AS DATETIME)) AS Opened,
            DATEADD(mi, h.duration, (CAST(DATEADD(day, -1, CAST(@Date AS DATE)) AS DATETIME) + CAST(h.opentime AS DATETIME))) AS Closed
        FROM Process.OpeningHours h
        WHERE h.daynumber = CASE WHEN DATEPART(dw, @Date) = 1
                                 THEN 7
                                 ELSE DATEPART(dw, @Date) - 1
                                 END
        AND businessid = @businessid
    ) w
    WHERE @Date BETWEEN Opened AND Closed

    RETURN @isopen; 

END;  

Answer 1

SQL Server 的日期/时间函数很糟糕。这是一个查询，首先获取今天工作日的开放时间并将其转换为今天的开放时间(例如，星期四 13:00 到 2017-03-30 13:00)。然后用它来检查它现在是否打开。

select count(*) as isopen
from
(
  select
    cast(cast(getdate() as date) as datetime) + opentime as opendatetime,
    duration
  from process.openinghours
  where daynumber = datepart(dw, getdate())
) as today
where getdate() between opendatetime and dateadd(mi, duration, opendatetime);

您可以简单地将其应用到您的函数中，以使其更具可读性和方便性。

更新:我们也必须考虑昨天，以获得午夜以上的开放时间。感谢 Serg 指出了这一点。不幸的是，查询变得更加复杂:

select count(*) as isopen
from
(
  select -- today's opening hours
    cast(cast(getdate() as date) as datetime) + opentime as opendatetime,
    duration
  from process.openinghours
  where daynumber = datepart(dw, getdate())
  union all
  select -- yesterday's opening hours
    cast(dateadd('d', -1, cast(getdate() as date)) as datetime) + opentime as opendatetime,
    duration
  from process.openinghours
  where daynumber % 7 + 1 = datepart(dw, getdate())
) as today_and yesterday
where getdate() between opendatetime and dateadd(mi, duration, opendatetime);

更新2:这是一个简化版本，我将所有开放时间转换为过去六天加上今天(希望我的数学正确)。我还添加了在上面的查询中忘记的businessid。

select count(*) as isopen
from
(
  select
    cast(dateadd('d', - (7 + datepart(dw, getdate()) - daynumber) % 7, cast(getdate() as date)) as datetime) + opentime as opendatetime,
    duration
  from process.openinghours
  where businessid = @businessid
) as these_last_seven_days
where getdate() between opendatetime and dateadd(mi, duration, opendatetime);