sql - 了解当前是否在T-SQL中营业

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

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;

最佳答案

CREATE TABLE #OpeningHours(
    [openinghoursid] int,
    [businessid] int NOT NULL,
    [daynumber] int NOT NULL,
    [opentime] time NOT NULL,
    [duration] int NOT NULL,
)

INSERT #OpeningHours
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);

DECLARE @Date   DATETIME

SELECT @Date = GETDATE()

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

    UNION

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

DROP TABLE #OpeningHours

关于sql - 了解当前是否在T-SQL中营业，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43114296/