How to find next (n) open days from SQL opening Hours Database
|
1:15 AM |
No comments |
How to find next (n) open days from SQL opening Hours Database
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 by Giorgi Nakeuri for How to find next (n) open days from SQL opening Hours Database
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 by Andomar for How to find next (n) open days from SQL opening Hours Database
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 by juharr for How to find next (n) open days from SQL opening Hours Database
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 you can do the following in your code.
List openDays = GetOpenDaysFromDB(); DateTime start = DateToStartFrom; int n = numberOfDays; List nextNOpenDays = new List(); while(nextNOpenDays.Count < n) { if(openDays.Contains(start.DayOfWeek)) nextNOpenDays.Add(start); start = start.AddDays(1); } Answer by Elliveny for How to find next (n) open days from SQL opening Hours Database
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 by kalabo for How to find next (n) open days from SQL opening Hours Database
I managed to find a solution:
public List getDaysOpen(int numberOfDays, DateTime start) { List openDays = this.getOpeningHoursDays(); List nextNOpenDays = new List(); while (nextNOpenDays.Count < numberOfDays) { if (openDays.Contains(Convert.ToByte(start.DayOfWeek))) nextNOpenDays.Add(start); start = start.AddDays(1); } return nextNOpenDays; } public List 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.
Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72
0 comments:
Post a Comment