如何从SQL开放时间数据库中查找next (n)开放日

Question

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

我使用以下基本模式存储某个位置的开放时间。

• 商店整数
• DayOfWeek -整数(0-6)
• OpenTime -时间
• CloseTime -时间

然而，我要做的是，对于当前的DateTime (即今天)，获得商店营业的下一个(n)天数。因此，例如，如果我发现商店在营业时间内营业和配置的接下来三天，商店在周日关门，而今天的日期是2015年2月21日()，那么我想把( 21/02/2015 )星期六、(23/02/2015)星期一和(23/02/2015)星期二还给你。

如果是Sunday I将于周一(23/02/2015)、(24/02/2015)星期二和(25/02/2015)周三(星期日截止)返回(23/02/2015)，最后如果是(20/02/2015)星期五，将返回(20/02/2015)星期五(21/02/2015)星期六(23/02/2015)星期一。

我不知道在SQL或C#中这样做是否更容易，但是如果我想出如何计算这个问题，我会在心里挣扎。

任何指点，指导都是很好的。

谢谢

Answer 1

这将给你一个相当有效率的提前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

Answer 2

首先，您可以使用下面这样的简单查询来获取商店营业的一周中的天数

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

Answer 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()。