display available 1 hour time slots

[beth4530]

I need to display a count of 1 hour time blocks available from a block of time minus the duration of transactions scheduled within the block of time.
For example, a staff member has 2.5 hours blocked off (2:30pm to 5:00pm) as available time in the dbo.Schedule_Staff table. However, in the dbo.Trans table they have
a 2:30 to 3:00 transaction scheduled and a 4:30 to 5:00 transaction scheduled. So there is a 1 hour block of time still available within the 2:30 to 5:00 block.

I need a query that will count the number of available 1 hour blocks of time for each staff member within a specified date range. (>=getdate() and <= end date)

 

[kjv1611]

Sounds like using DATEPART and/or DATEADD/DATEDIFF and/or stripping out just the time portions would be ideal.

Are you wanting to query according to the exact hours or get a count of hours? If just a count of hours, then just use DATEDIFF for each piece. So..

SELECT DATEDIFF(HOUR,StartTime,StopTime) AS HoursAvail FROM dbo.Shedule_Staff
SELECT DATEDIFF(HOUR,StartTime,StopTime) AS TransSched FROM dbo.Trans

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Olaf Doschke]

Let me reflect what I understand about this: dbo.Schedule_Staff is storng the shift times, where an employee is available and dbo.Trans is storing work scheduled to an employee. Is that right?
And now you introduce a third time span, >=getdate() and <end date, which always goes from now to some time in the future.

So looking at this geometrically you'd subtract all time intervals in dbo.Trans from all time intervals in dbo.Schedula_Staff and would have new time intervals. Let's call this intermediat result unplannedschedule. And finally you'd want the count of full hours (not necessarily starting at the full hour timewise) in all these intervals.

An I right with this, so far? ThatÄll not be an easy query, of course, so I don't want to start working on a wrong interpretation of your problem.

Bye, Olaf.