查找具有定义范围的连续日期，在该范围内可以安排培训师 [英] Fnd consecutive dates withing a defined span where a Trainer is available to schedule
问题描述
我们有一个表格，用于将培训师分配给计划的客户安装。在表格中，每年的每一天都有每位培训师的记录。 （我们可以，有时也可以在周末工作。）我正在构建一个搜索工具，使我们的计划员可以搜索在日期Y和Z之间的X天数可用的教练员。
We have a table used for assigning trainers to planned customer installations. In the table, there is a record for each trainer, for each day of the year. (We can, and sometimes do, work weekends.) I am building a search tool that allows our schedulers to search for a trainer that is available X 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
这是我的简化版本桌子，涵盖了3天的四位培训师。如果他们有预定的东西，Dirty =1。如果他们有自由预定的东西，Dirty = 0。
This is a simplified version of my table, covering four trainers over 3 days. If they have something scheduled, Dirty = 1. If they are free to schedule, Dirty = 0.
我要构建的查询是允许以下内容的查询：
What I would like to build is a query that allows for the following:
-
定义开始工作的开始日期和结束日期。
Define a Beginning and Ending Date that the work needs to occur.
定义需要培训师的连续天数。
Define the number of consecutive days that the trainer will be required.
返回每个匹配的培训师以及他们的第一个日期，其可用时间至少等于所请求的天数。
Return each Trainer that matches, along with the first date they are available for a period of time at least equal to the number of days being requested.
纯文本示例：
客户要求在6月的任何时候在现场培训2天。查询应该返回：
The customer asks for a trainer to be onsite for two days anytime in June. The query should return:
Alan, 06/01/2013
Heather, 06/01/2013
如果客户在6月将请求更改为三天，则查询将返回：
If the customer changed the request to three days in June, the query would return:
Heather, 06/01/2013
我已经搜索了几天，但发现有些事情似乎很接近，但最终，我无法使它们正常工作。在大多数情况下，故障的执行时间非常长。以下是一些看似有希望的方法，也许可以由比我打包的SQL-Fu强的人来加以改编：
I've been searching for a few days now, and I've found some things that seemed close, but ultimately, I couldn't get them to work. In most cases, the failure has been in the form of insanely long execution times. Here are a few that seemed promising, and perhaps can be adapted by someone with stronger SQL-Fu than I am packing:
- How to find N Consecutive records in a table using SQL
- Microsoft T-SQL Counting Consecutive Records
- How to find date ranges in records with consecutive dates and duplicate data
推荐答案
不确定如何对较大的数据集执行此操作，但是对于提供的数据集，它会得到正确的结果。假定缺少数据点。
not sure how this will perform against a larger data set, but it gets the right results for the data set provided. 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
这篇关于查找具有定义范围的连续日期，在该范围内可以安排培训师的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
