All geek questions in one place

Fnd consecutive dates with a specific interval in which the coach is available for the schedule

We have a table used to assign trainers to planned client settings. In the table for each trainer there is an entry for each day of the year. (We can and sometimes do work weekends.) I create a search tool that allows our planners to look for a trainer who is available X the number of days between dates Y and Z.

Table mySchedule Trainer Date Dirty (Bit) ------------------------------------------------ Joe 06/01/2013 0 Jessica 06/01/2013 0 Alan 06/01/2013 0 Heather 06/01/2013 0 Joe 06/02/2013 1 Jessica 06/02/2013 1 Alan 06/02/2013 0 Heather 06/02/2013 0 Joe 06/03/2013 1 Jessica 06/03/2013 1 Alan 06/03/2013 1 Heather 06/03/2013 0

This is a simplified version of my table, covering four trainers over 3 days. If they have something planned, Dirty = 1. If they are free for the schedule, Dirty = 0.

What I would like to build is a query that allows the following:

  • Determine the start and end dates at which work should be performed.

  • Determine the number of consecutive days during which the trainer will be needed.

  • Return every trainer that matches the first date available over a period of time at least equal to the number of days requested.

Simple text example:

The client asks the trainer to be in place for two days at any time in June. The request should return:

 Alan, 06/01/2013 Heather, 06/01/2013

If the client changed the request for three days in June, the request will return:

 Heather, 06/01/2013

I searched for a few days and I found some things that seemed close, but in the end I could not get them to work. In most cases, the denial was in the form of an insanely long lead time. Here are some of them that seem promising and perhaps could be adapted by someone with stronger SQL-Fu than I am packaging:

+3
source share
3 answers

I’m not sure how this will be done against a larger data set, but it will get the correct results for the provided data set. Missing data points are assumed to be available.

 declare @startDate datetime, @endDate datetime, @days int select @startDate = '6/1/2013', @endDate='6/3/2013', @days=2 select trainer, min(date) from ( select trainer,date, (select top 1 date from mySchedule sInner where sInner.date > sOuter.date and sInner.trainer = sOuter.trainer and sInner.Dirty = 1 and sInner.date between @startDate and @endDate order by sInner.date) as nextDirtyDate from mySchedule sOuter where sOuter.dirty=0 and sOuter.date between @startDate and @endDate ) sub group by trainer, nextDirtyDate having dateDiff(d, min(date), isNull(nextDirtyDate,dateAdd(d,1,@endDate))) >= @days
0
source

Assuming the missing schedule entry is "available" by default, something like this should work:

 WITH cteRawData As ( SELECT S.Trainer, S.Date, -- If there are no later busy dates within the period, -- assume they're free until the end of the period: IsNull(ED.EndDate, DateAdd(day, 1, @EndDate)) As EndDate FROM mySchedule As S CROSS APPLY ( SELECT Min(Date) As EndDate FROM mySchedule As S2 WHERE S2.Trainer = S.Trainer And S2.Date > S.Date And S2.Date <= @EndDate And S2.Dirty = 1 ) As ED WHERE S.Date Between @StartDate And @EndDate And S.Dirty = 0 ), cteData As ( SELECT Trainer, Date, DateDiff(day, Date, EndDate) As NumberOfDays FROM cteRawData ) SELECT Trainer, Min(Date) As EarliestStartDate FROM cteData WHERE NumberOfDays >= @NumberOfDays GROUP BY Trainer ;

http://www.sqlfiddle.com/#!3/7b3e2/17

0
source

Note. I don't know how good the performance is as it uses the total.

He also plays with a dirty flag to get a positive number when dirty = 0
e.g. CASE WHEN B.Dirty = 0 THEN 1 ELSE -1 END

It uses the number of days in 2 places in the request
S.Date - NNN + 1 AS StartDate
HAVING Sum(CASE WHEN B.Dirty = 0 THEN 1 ELSE -1 END) = NNN

 -- Note the need to put the value of 3 to be put at 2 places SELECT S.Trainer, S.Date - 3 + 1 AS StartDate, Sum(CASE WHEN B.Dirty = 0 THEN 1 ELSE -1 END) AS RunningAvl FROM Schedule S INNER JOIN Schedule B ON S.Trainer = B.Trainer WHERE B.Date <= S.Date AND S.Date BETWEEN '2013-06-01' AND '2013-06-30' AND B.Date BETWEEN '2013-06-01' AND '2013-06-30' GROUP BY S.Trainer, S.Date HAVING Sum(CASE WHEN B.Dirty = 0 THEN 1 ELSE -1 END) = 3 ORDER BY S.Trainer, S.Date ; -- Note the need to put the value of 2 to be put at 2 places SELECT S.Trainer, S.Date - 2 + 1 AS StartDate, Sum(CASE WHEN B.Dirty = 0 THEN 1 ELSE -1 END) AS RunningAvl FROM Schedule S INNER JOIN Schedule B ON S.Trainer = B.Trainer WHERE B.Date <= S.Date AND S.Date BETWEEN '2013-06-01' AND '2013-06-30' AND B.Date BETWEEN '2013-06-01' AND '2013-06-30' GROUP BY S.Trainer, S.Date HAVING Sum(CASE WHEN B.Dirty = 0 THEN 1 ELSE -1 END) = 2 ORDER BY S.Trainer, S.Date

http://www.sqlfiddle.com/#!3/99f2d/1

0
source

Source: https://habr.com/ru/post/977350/

More articles:


All Articles