Как найти следующие (n) дни открытых дверей из базы данных SQL
Я использую схему базы данных SQL, аналогичную той, которая найдена в этой ссылке. Лучший способ хранить рабочее время и эффективно его обрабатывать
Я храню часы работы для местоположения, используя эту основную схему
- Магазин – INTEGER
- DayOfWeek – INTEGER (0-6)
- OpenTime – TIME
- CloseTime – TIME
Однако я пытаюсь сделать это для текущего DateTime (т.е. сегодня) получить NEXT (n) количество дней, когда магазин открыт. Так, например, если я хотел найти следующие три дня, когда магазин был открыт и настроен в часы работы, магазин закрыт в воскресенье, а сегодняшняя дата – 21/02/2015 ( суббота ). Я хотел бы вернуть дни ( 21/02/2015) Суббота, (23/02/2015) понедельник и (23/02/2015) вторник .
- Как получить следующие три месяца в оракуле, используя sysdate
- SQL Show Число месяцев и лет между двумя датами
- SQL: Как найти уникальные диапазоны дат из заданного набора диапазонов дат?
- SQL Преобразование каждого диапазона дат в каждую строку дня
- Предыдущие данные
Если бы было воскресенье, я вернусь (23/02/2015) понедельник, (24/02/2015) вторник и (25/02/2015) среда (как закрыта в воскресенье) и, наконец, если бы это было (20/02 / 2015) В пятницу он вернется (20/02/2015) Пятница, (21/02/2015) Суббота, (23/02/2015) понедельник .
Я не знаю, легче ли это делать в SQL или C #, но я мысленно боюсь, если выясню, как вычислить это.
Любые указатели, руководство было бы здорово.
спасибо
- Как я могу получить формат MMYYYY в SQL Server?
- Ошибка SQL Server 2014 с функцией преобразования
- Рассчитать рабочее время между двумя датами в sql-сервере, исключая выходные
- Как получать ежемесячные данные из трех отдельных таблиц
- Как извлечь даты с datatye DATETIME из colum A в таблицу X и поместить их в таблицу Y при изменении типа данных в DATE
- получить только максимальную дату создания из той же таблицы
- Возврат обновленного значения столбца на основе условий другого значения столбца в SQL Server
- Используя SQL-запрос, как я могу выбрать каждую дату в пределах диапазона?
Это даст вам до 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
Сначала вы можете использовать простой запрос следующего типа, чтобы получить дни недели, когда магазин открыт
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); }
Вы можете использовать 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) ;
Реализация нескольких магазинов остается в качестве упражнения для читателя.
Попробуй это:
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, tn, GETDATE()) FROM Tally t JOIN @ts ON DATEPART(w, DATEADD(dd, tn, 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() на любую дату, чтобы посмотреть.
Мне удалось найти решение:
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(); }
Это, на мой взгляд, самый простой способ найти решение. Спасибо за твою помощь.