了解业务当前是否在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,CAST(N'18:00:00' AS Time),240),(81,3,(82,(83,4,(84,(85,5,(86,(87,6,(88,300),(89,7,(90,600),(91,1,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,@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,(CAST(DATEADD(day,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,180);

DECLARE @Date   DATETIME

SELECT @Date = GETDATE()

SELECT COUNT(*)
FROM
(
    SELECT (CAST(CAST(@Date AS DATE) AS DATETIME) + h.opentime) AS Opened,(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,CAST(@Date AS DATE)) AS DATETIME) + h.opentime) AS Opened,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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!