Find out if a business is currently open in T-SQL

Question

I am storing business opening hours in this table. Businesses can have multiple opening hours in the same day. The closing time can be after midnight of the current day.

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])
)

Sample data in this table includes:

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);

Now I want to create a function which returns if a business is currently open or closed.

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

I am using the COUNT() to see if any of the rows matches the condition, if 0 match it means it is closed, if COUNT() is greater than 0 it is open. This works when the closing time is within the same day, however it does not work when the closing time is after midnight or if the current time is after midnight.

Any idea how I can fix it?

EDIT: Thank you for all the responses. Finally I went ahead with @DenisRubashkin's solution. For anyone interested this is the final function I used:

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's date/time functions are horrible. Here is a query that get's today's weekday's opening hours and converts them into today's opening hours (e.g. Thursday 13:00 to 2017-03-30 13:00) first. Then this is used to check whether now it's open or not.

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);

You can simply adopt this to your function to get it more readable and convenient.

UPDATE: We must consider yesterday, too, to get opening times over midnight. Thanks to Serg for pointing this out. The query gets way more complicated unfortunately:

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);

UPDATE 2: Here a simplified version where I convert all opening times to the last six days plus today (hope I've got the math right). I've also added the businessid that I forgot in above queries.

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);

Answer 2

This should do the trick for you

SELECT  @isopen = COUNT(*)
FROM [Process].[OpeningHours]
WHERE daynumber = @dayofweek
    AND businessid = @businessid
    AND @CheckDateTime BETWEEN CAST(CONCAT('1900-01-01 ',(CAST([opentime] AS TIME))) AS DATETIME2) AND DATEADD(minute,[duration],CAST(CONCAT('1900-01-01 ',(CAST([opentime] AS TIME))) AS DATETIME2))

Test Data

DECLARE @currentdatetime DATETIME = '2017-03-30 13:01:51.550';
DECLARE @dayofweek INT = DATEPART(dw, @currentdatetime);
DECLARE @currentTime VARCHAR(16) = CAST(@currentdatetime AS Time)
DECLARE @CheckDateTime DATETIME2 = CAST(CONCAT('1900-01-01 ',(CAST(@currentTime AS TIME))) AS DATETIME2)
DECLARE @isopen BIT;
DECLARE @businessid INT = 18

DECLARE @BusinessOpeningHours TABLE ([openinghoursid] [int] NOT NULL,[businessid] INT NOT NULL,
    [daynumber] [int] NOT NULL,
    [opentime] [time](7) NOT NULL,
    [duration] [int] NOT NULL,
    [closetime] [time] NOT NULL)


INSERT INTO @BusinessOpeningHours([openinghoursid], [businessid], [daynumber], [opentime], [duration],[closetime])
SELECT 79, 18, 2, CAST(N'12:00:00' AS Time), 165, dateadd(minute,165,CAST(N'12:00:00' AS Time)) UNION ALL
SELECT 80, 18, 2, CAST(N'18:00:00' AS Time), 240, dateadd(minute,240,CAST(N'18:00:00' AS Time)) UNION ALL
SELECT 81, 18, 3, CAST(N'12:00:00' AS Time), 165, dateadd(minute,165,CAST(N'12:00:00' AS Time)) UNION ALL
SELECT 82, 18, 3, CAST(N'18:00:00' AS Time), 240, dateadd(minute,240,CAST(N'18:00:00' AS Time)) UNION ALL
SELECT 83, 18, 4, CAST(N'12:00:00' AS Time), 165, dateadd(minute,165,CAST(N'12:00:00' AS Time)) UNION ALL
SELECT 84, 18, 4, CAST(N'18:00:00' AS Time), 240, dateadd(minute,240,CAST(N'18:00:00' AS Time)) UNION ALL
SELECT 85, 18, 5, CAST(N'12:00:00' AS Time), 165, dateadd(minute,165,CAST(N'12:00:00' AS Time)) UNION ALL
SELECT 86, 18, 5, CAST(N'18:00:00' AS Time), 240, dateadd(minute,240,CAST(N'18:00:00' AS Time)) UNION ALL
SELECT 87, 18, 6, CAST(N'12:00:00' AS Time), 165, dateadd(minute,165,CAST(N'12:00:00' AS Time)) UNION ALL
SELECT 88, 18, 6, CAST(N'18:00:00' AS Time), 300, dateadd(minute,300,CAST(N'18:00:00' AS Time)) UNION ALL
SELECT 89, 18, 7, CAST(N'12:00:00' AS Time), 165, dateadd(minute,165,CAST(N'12:00:00' AS Time)) UNION ALL
SELECT 90, 18, 7, CAST(N'18:00:00' AS Time), 600, dateadd(minute,600,CAST(N'18:00:00' AS Time)) UNION ALL
SELECT 91, 18, 1, CAST(N'12:00:00' AS Time), 180, dateadd(minute,180,CAST(N'12:00:00' AS Time))

SELECT  @isopen = COUNT(*)
FROM @BusinessOpeningHours
WHERE daynumber = @dayofweek
    AND businessid = @businessid
    AND @CheckDateTime BETWEEN CAST(CONCAT('1900-01-01 ',(CAST([opentime] AS TIME))) AS DATETIME2) AND DATEADD(minute,[duration],CAST(CONCAT('1900-01-01 ',(CAST([opentime] AS TIME))) AS DATETIME2))

SELECT @isopen;