title: Find out if a business is currently open in T-SQL
body: <p>I am storing business opening hours in this table. Businesses can have <strong>multiple</strong> opening hours in the same day. <strong>The closing time can be after midnight of the current day.</strong></p>
  
  <pre><code>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])
  )
  </code></pre>
  
  <p>Sample data in this table includes:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>Now I want to create a function which returns if a business is currently open or closed.</p>
  
  <pre><code>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 &gt;= @currentdate + CONVERT(DATETIME, opentime)
  
          AND
  
          @currentdatetime &lt;= 
              CASE 
                  WHEN closetime &lt; '00:00:00' THEN @currentdate + CONVERT(DATETIME, closetime)
                  ELSE DATEADD(DAY,1,@currentdate) + CONVERT(DATETIME, closetime)
              END
      );
  
      RETURN @isopen; 
  
  END;  
  
  GO
  </code></pre>
  
  <p>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.</p>
  
  <p>Any idea how I can fix it?</p>
  
  <p><strong><em>EDIT:</em></strong> Thank you for all the responses. Finally I went ahead with @DenisRubashkin's solution. For anyone interested this is the final function I used:</p>
  
  <pre><code>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;  
  </code></pre>