All geek questions in one place

Find out if your business is open in T-SQL

I keep hours of work in this table. Businesses may have several hours on the same day . Closing time may 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])
)

Examples of data in this table include:

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 that returns if the business is 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 use COUNT () to see if any of the lines matches the condition, if 0 matches, it means it is closed, if COUNT () is greater than 0, it is open. This works when the closing time is on the same day, however it does not work when the closing time is after midnight or the current time after midnight.

Any idea how I can fix this?

: . @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;
+6
4
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
+1

SQL Server . , (, 13:00 2017-03-30 13:00). , , .

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

, .

: , . . , :

select count(*) as isopen
from
(
  select -- today 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 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);

2: ​​ , (, ). , .

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

That 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;
+2
source

Convert the line with the interval of crossing the border of the day into a couple of lines and just check the time between them. Demo

with [OpeningHours+] as (
    select oh.[openinghoursid], oh.[businessid], oh.duration, t.*
    from [OpeningHours] oh
    cross apply (
        select [daynumber], [opentime]
           ,case when datediff(minute,[opentime],'23:59:59') + 1 >= [duration] 
           then dateadd(minute,[duration],[opentime]) 
           else cast('23:59:59' as time) end [closetime]
        union all
        select case [daynumber] when 7 then 1 else [daynumber] + 1 end, cast('00:00:00' as time) 
         , dateadd(minute,[duration] - (datediff(minute,[opentime],'23:59:59') + 1), cast('00:00:00' as time)) 
        where datediff(minute,[opentime],'23:59:59') + 1 < [duration] 
        )  t       
)
select * 
from [OpeningHours+] 
where [businessid] = 18 and [daynumber] = 1 and cast('00:03:00' as time) between [opentime] and [closetime]
+1
source

Source: https://habr.com/ru/post/1016165/

More articles:


All Articles