在指定的培训师可用的时间范围内查找连续的日期

我们有一个表，用于将培训师分配给计划的客户安装。在表格中，每个培训师在一年中的每一天都有一个记录。(我们可以，有时也会在周末工作。)我正在构建一个搜索工具，允许我们的调度程序搜索日期Y和z之间可用的培训师X天。

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

这是我的表的简化版本，涵盖4个训练者在3天内。如果他们有计划，Dirty = 1。如果它们可以自由调度，则Dirty = 0。

我想构建的是一个允许以下操作的查询:

定义工作需要发生的开始和结束日期。
规定要求培训师连续培训的天数。
返回每个匹配的培训师，以及他们的第一次可用日期，至少等于请求的天数。

纯文本示例:

客户要求培训师在6月份的任何时候到现场两天。查询应该返回:

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

如果客户将请求更改为6月的三天，查询将返回:

Heather, 06/01/2013

我已经找了几天了，我找到了一些看起来很接近的东西，但最终，我无法让它们工作。在大多数情况下，失败的形式是执行时间过长。这里有一些看起来很有希望的，也许可以被比我打包的SQL-Fu更强的人改编:

如何使用SQL
Microsoft T-SQL连续记录计数
如何在具有连续日期和重复数据的记录中查找日期范围

不确定这对更大的数据集的性能如何，但它可以为所提供的数据集获得正确的结果。假设缺失的数据点是可用的。

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

假设一个缺失的时间表记录默认为"available"，类似这样的事情应该可以工作:

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

注意:我不知道性能有多好，因为它使用了运行总数。

当dirty = 0时，它会使用dirty标志来获得正数
例如:CASE WHEN B.Dirty = 0 THEN 1 ELSE -1 END

它使用查询
中两个位置的天数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

相关内容

最新更新

热门标签：