首页 文章

如何从SQL开放时间数据库中查找下一(n)个开放日期

提问于
浏览
2

我正在使用类似于此链接上找到的SQL数据库架构 . Best way to store working hours and query it efficiently

我正在使用此基本架构存储位置的营业时间

  • 商店 - INTEGER

  • DayOfWeek - INTEGER(0-6)

  • OpenTime - TIME

  • 关闭时间 - 时间

然而,我想要做的是当前的DateTime(即今天)获得商店开放的NEXT(n)天数 . 因此,例如,如果我在接下来的三天发现该商店在营业时间开放和配置,商店在周日关闭,今天的日期是2015年2月21日( Saturday )我希望返回日期 (21/02/2015)Saturday, (23/02/2015)Monday and (23/02/2015)Tuesday .

如果它是 Sunday 我将返回 (23/02/2015)Monday, (24/02/2015)Tuesday and (25/02/2015)Wednesday (因为它在周日休息),最后如果是(2015年2月20日)星期五将返回 (20/02/2015)Friday, (21/02/2015)Saturday, (23/02/2015)Monday .

我不知道在SQL或C#中这样做是否更容易,但如果弄清楚如何计算,我会在精神上苦苦挣扎 .

任何指针,指导都会很棒 .

谢谢

5 回答

  • 0

    这将以相当有效的方式提前10天 . 第一个测试数据:

    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'
    

    哪个可以像这样查询:

    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
    

    结果:

    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
    
  • 0

    首先,您可以使用如下所示的简单查询来获取商店开放的一周中的日期

    Select DayOfWeek
    From OpenHours
    Where ShopId = @ShopID
    

    这假设没有未开放日期的条目 . 如果相反,打开小时列为空,或者小于或等于未打开的天数的关闭时间,则调整此查询 .

    运行该查询并返回结果后,最好将它们转换为 List<DayOfWeek> ,您可以在代码中执行以下操作 .

    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);
    }
    
  • 2

    您可以使用 case 在本周提前一天看起来像下周的那一天 . 这是查看下一个开放日的示例:

    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
    

    然后你可以递归以找到一天以上 . 如果我们将上述代码段存储在名为 get_next_open_day 的函数中,则递归公用表表达式可能如下所示:

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

    这是一个完整的工作示例:

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

    多个商店的实施留给读者练习 .

  • 3

    试试这个:

    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
    

    输出:

    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:您可以将 GETDATE() 替换为任意日期 .

  • 0

    我设法找到了解决方案:

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

    在我看来,这是找到解决方案最简单的方法 . 谢谢你的帮助 .

相关问题