了解业务当前是否在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

原文链接：https://www.f2er.com/mssql/74810.html

了解业务当前是否在T-SQL中打开

解决方法

猜你在找的MsSQL相关文章