How to find next (n) open days from SQL opening Hours Database

Question

I am using a SQL database schema similar to the one found on this link. Best way to store working hours and query it efficiently

I am storing the Opening hours for a location using this basic schema

• Shop - INTEGER
• DayOfWeek - INTEGER (0-6)
• OpenTime - TIME
• CloseTime - TIME

What i am trying to do however is for the current DateTime (i.e. today) get the NEXT (n) number of days that the shop is open. So for example if i wasnted to find the next three days that the shop was open and configured in the opening hours the shop is closed on a Sunday and todays date is 21/02/2015 (Saturday) I would like to return the days (21/02/2015)Saturday, (23/02/2015)Monday and (23/02/2015)Tuesday.

If it was Sunday i would return (23/02/2015)Monday, (24/02/2015)Tuesday and (25/02/2015)Wednesday (as its closed on sunday) and finally if it was (20/02/2015)Friday it would return (20/02/2015)Friday, (21/02/2015)Saturday, (23/02/2015)Monday.

I dont know if this is easier to do in SQL or C# but i am mentally struggling in if figuring out how to calculate this.

Any pointers, guidance would be great.

Thank you

Answer 1

This will give you up to 10 days ahead in a fairly efficient way. First test data:

DECLARE @DaysAhead TABLE (
    Delta INT
  )
INSERT INTO @DaysAhead (Delta)
SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10

DECLARE @Opening TABLE (
    Shop INT,
    DayOfWk INT,
    DayNm varchar(10),
    OpenTime TIME,
    CloseTime TIME
  )

INSERT INTO @Opening (Shop, DayOfWk, DayNm, OpenTime, CloseTime)
SELECT 1, 5, 'Fri', '09:00', '17:00' -- 
UNION ALL SELECT 1, 6, 'Sat' ,'09:00', '17:00'
--UNION ALL SELECT 0, 'Sun', '09:00', '17:00' -- Not open on Sunday
UNION ALL SELECT 1, 1, 'Mon', '09:00', '17:00'
UNION ALL SELECT 1, 2, 'Tue', '09:00', '17:00'
UNION ALL SELECT 1, 3, 'Wed', '09:00', '17:00'

Which can be queried like this:

DECLARE @dt datetime='21-Feb-2015'
DECLARE @dow int=datepart(dw, @dt)-1

SELECT TOP 3 o.Shop, o.DayOfWk, o.DayNm, o.OpenTime, o.CloseTime FROM (
  SELECT Delta, ((@dow+Delta)%7) as DayOfWk 
  FROM @DaysAhead
) daysAhead
INNER JOIN @Opening o on o.DayOfWk=daysAhead.DayOfWk
ORDER BY daysAhead.Delta

Results:

DECLARE @dt datetime='20-Feb-2015' -- Fri

1   5   Fri 09:00:00.0000000    17:00:00.0000000
1   6   Sat 09:00:00.0000000    17:00:00.0000000
1   1   Mon 09:00:00.0000000    17:00:00.0000000

DECLARE @dt datetime='21-Feb-2015' -- Sat

1   6   Sat 09:00:00.0000000    17:00:00.0000000
1   1   Mon 09:00:00.0000000    17:00:00.0000000
1   2   Tue 09:00:00.0000000    17:00:00.0000000

DECLARE @dt datetime='22-Feb-2015' -- Sun

1   1   Mon 09:00:00.0000000    17:00:00.0000000
1   2   Tue 09:00:00.0000000    17:00:00.0000000
1   3   Wed 09:00:00.0000000    17:00:00.0000000

Answer 2

First you can use a simple query like the following to get the days of the week that the shop is open

Select DayOfWeek
From OpenHours
Where ShopId = @ShopID

This assumes that there will not be entries for days that are not open. Adjust this query if instead the open hour column is null, or less than or equal to the close time for days that are not open.

After you run that query and get the results back and preferably translate them into a List<DayOfWeek> you can do the following in your code.

List<Day0fWeek> openDays = GetOpenDaysFromDB();
DateTime start = DateToStartFrom;
int n = numberOfDays;

List<DateTime> nextNOpenDays = new List<DateTime>();

while(nextNOpenDays.Count < n)
{
    if(openDays.Contains(start.DayOfWeek))
        nextNOpenDays.Add(start);
    start = start.AddDays(1);
}

Answer 3

Try this:

DECLARE @t TABLE(WeekID INT, OpenTime time)
DECLARE @c INT = 10

INSERT INTO @t VALUES
(1, '10:00'),--sunday
(2, '10:00'),--monday
(4, '10:00'),--wednsday
(5, '10:00')--thursday


;WITH Tally (n) AS
(
    -- 1000 rows
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
)
SELECT TOP (@c) DATEADD(dd, t.n, GETDATE())
FROM Tally t
JOIN @t s ON DATEPART(w, DATEADD(dd, t.n, GETDATE())) = s.WeekID

Output:

Date
2015-02-22 --sunday
2015-02-23 --monday
2015-02-25 --wednsday
2015-02-26 --thursday
2015-03-01 --sunday
2015-03-02 --monday
2015-03-04 --wednsday
2015-03-05 --thursday
2015-03-08 --sunday
2015-03-09 --monday

PS: You can replace GETDATE() with any date to look from.

Answer 4

You can use a case to make a day earlier in this week look like that day next week. Here's an example to look up the next open day:

select  top 1 dateadd(day, day_diff, @dt) as dt
from    (
        select  case 
                when dayofweek <= datepart(dw, @dt) then dayofweek + 7
                else dayofweek
                end - datepart(dw, @dt) as day_diff
        ,       *
        from    dbo.OpeningHours
        ) sub1
order by
        day_diff

You can then recurse to find more than one day. If we store the above snippet in a function called get_next_open_day, the recursive common table expression could look like:

; with  cte as
        (
        select  dbo.get_next_open_day(@dt) as open_day
        ,       1 as day_number
        union all
        select  dbo.get_next_open_day(prev_day.open_day)
        ,       prev_day.day_number + 1
        from    cte as prev_day
        where   prev_day.day_number < @number_of_days
        )
select  cte.open_day
,       datename(dw, cte.open_day)
from    cte
option  (maxrecursion 100)
;

Here's a full working example:

use Test

if object_id('OpeningHours') is not null
    drop table OpeningHours;
if object_id('dbo.get_next_open_day') is not null
    drop function dbo.get_next_open_day;

create table OpeningHours (dayofweek int, opentime time, closetime time);
insert dbo.OpeningHours values 
    (2, '9:00', '17:00'),
    (3, '9:00', '17:00'),
    (4, '9:00', '17:00'),
    (5, '9:00', '17:00'),
    (6, '9:00', '21:00'),
    (7, '10:00', '17:00')
    ;
go
create function dbo.get_next_open_day(
    @dt date) 
    returns date
as begin return
    (
    select  top 1 dateadd(day, day_diff, @dt) as dt
    from    (
            select  case 
                    when dayofweek <= datepart(dw, @dt) then dayofweek + 7
                    else dayofweek
                    end - datepart(dw, @dt) as day_diff
            ,       *
            from    dbo.OpeningHours
            ) sub1
    order by
            day_diff
    )
end
go

--declare @dt date = '2015-02-18' -- Wed
--declare @dt date = '2015-02-20' -- Fri
declare @dt date = '2015-02-22' -- Sun
declare @number_of_days int = 10

; with  cte as
        (
        select  dbo.get_next_open_day(@dt) as open_day
        ,       1 as day_number
        union all
        select  dbo.get_next_open_day(prev_day.open_day)
        ,       prev_day.day_number + 1
        from    cte as prev_day
        where   prev_day.day_number < @number_of_days
        )
select  cte.open_day
,       datename(dw, cte.open_day)
from    cte
option  (maxrecursion 100)
;

The implementation of multiple shops is left as an exercise for the reader.

Answer 5

I managed to find a solution:

        public List<DateTime> getDaysOpen(int numberOfDays, DateTime start)
    {
        List<byte> openDays = this.getOpeningHoursDays();
        List<DateTime> nextNOpenDays = new List<DateTime>();

        while (nextNOpenDays.Count < numberOfDays)
        {
            if (openDays.Contains(Convert.ToByte(start.DayOfWeek)))
                nextNOpenDays.Add(start);
            start = start.AddDays(1);
        }
        return nextNOpenDays;
    }

    public List<byte> getOpeningHoursDays()
    {
        return db.OpeningHours.Where(oh => oh.LocationId == this.Id).Select(oh => oh.DateOfWeek).ToList();
    }

This was in my opinion the easiest method to find a solution. Thank you for all your help.