Find first avilable time slot size

[dynamictiger]

I have the following query.

SELECT qryServFindNextAvailableTime.JobDate, qryServFindNextAvailableTime.ServPerson, First(qryServAllStartFinish.Start) AS FirstOfStart, qryServFindNextAvailableTime.TimePromised, CSng(DateDiff("n",[Start],[TimePromised])) AS AvailableMinutes, [AvailableMinutes]/60 AS Expr1
FROM qryServFindNextAvailableTime INNER JOIN qryServAllStartFinish ON qryServFindNextAvailableTime.ServPerson = qryServAllStartFinish.ServicePersonID
GROUP BY qryServFindNextAvailableTime.JobDate, qryServFindNextAvailableTime.ServPerson, qryServFindNextAvailableTime.TimePromised, CSng(DateDiff("n",[Start],[TimePromised])), [AvailableMinutes]/60
WITH OWNERACCESS OPTION;

I am trying to get the gap between the first booked time and the start time. For example if the first booked job is at 12.40 pm and the employee starts at 8.00 am there are 4.666666666 hours available. The object being that if another job books in then we know if it is 3.5 hours there is enough time to do it before the 12.40 job.

This sort of works but it is returning all the jobs for the day. I only want to return one job, being the first of the day. I know I can get there with another query I am just trying to avoid another query if I can as I have so many queries in this application it is getting out of hand - not to say my imagination for unique names is limited.

 

[ritchieroo]

This may help

 

[dynamictiger]

Yep, that'll sure help. Thanks a lot.

 

[ritchieroo]

Here's Query3 from the database presented a little more cleary, also with GapMins column added.

SELECT a.WorkerID
     , a.WorkDate
     , DateAdd("n", 1, a.EndTime) AS GapStart
     , Nz(DateAdd("n", -1, min(c.StartTime)), b.LatestEndTime) AS GapEnd
     , DateDiff("n", DateAdd("n", 1, a.EndTime), Nz(DateAdd("n", -1, min(c.StartTime)), b.LatestEndTime)) as GapMins
FROM tblWorker AS b 
INNER JOIN (tblJobBookings AS a 
            LEFT JOIN tblJobBookings AS c 
              ON (a.StartTime < c.StartTime) 
              AND (a.WorkDate = c.WorkDate) 
              AND (a.WorkerID = c.WorkerID)) 
  ON b.WorkerID = a.WorkerID
WHERE DateAdd("n",1,a.EndTime) < DateAdd("n",-b.ShortestBookablePeriod,b.LatestEndTime)
GROUP BY a.WorkerID, a.WorkDate, DateAdd("n",1,a.EndTime), b.LatestEndTime

UNION ALL 

SELECT a.WorkerID
     , a.WorkDate
     , b.EarliestStartTime
     , Min(DateAdd("n", -1, a.StartTime))
     , DateDiff("n", b.EarliestStartTime, Min(DateAdd("n", -1, a.StartTime)))
FROM tblJobBookings AS a 
INNER JOIN tblWorker AS b 
  ON a.WorkerID = b.WorkerID
WHERE a.StartTime > DateAdd("n", b.ShortestBookablePeriod, b.EarliestStartTime)
GROUP BY a.WorkerID, a.WorkDate, b.EarliestStartTime

ORDER BY 1, 2, 3;

Also, to select a single row from a sorted query you can use the TOP predicate. Unfortunately, it can't be made to work directly with the UNION query above as you have 2 selects, but you could do something like this...

SELECT TOP 1 *
FROM Query3
WHERE WorkerID=1
ORDER BY WorkDate, GapStart;

 

[dynamictiger]

I found DateDiff works well for calculating the gaptime in minutes. Is there a problem with that approach instead of DateAdd?

 

[ritchieroo]

I used DateDiff to calculate duration of Gaps as well.

I was using DateAdd to add 1 minute onto my recorded EndTime, in order to generate the GapStartTime etc.