• Status: Solved
• Priority: Medium
• Security: Public
• Views: 686

# Occupied in first hour each day

Hello,
I have a Scheduler (calendar). I would like to find all those instances when the first timeslot each day is occupied.  I do not know at what time the first timeslot starts.

I have all timeslots in 1 table:
TimeId(int), StartDate(datetime),EndDate(datetime)

And occupied timeslots in another:
OccupiedId, TimeId.

How is best to do this?
0
johnkainn
• 2
1 Solution

Commented:
What you can do is a select join  with an order.
This will give you the first occupied slots of the day.
Forgive the pseudocode

Select first(TimeId),
StartDate,
EndDate
from table1
join timeslots on table1.TimeId = timeslots.TimeId
where OccupiedId = <yes/True/1, whatever means occupied>
order by StartDate, TimeId asc;
0

Author Commented:
Let us say that I would like to select all from 1.8.2012 to 15.10.2012 where first hour is occupied.
For example if the first hour is occupied only on 20.8.2012, 25.8.2012 and 3.10.2012.
How would I select those three timeslots?
0

Commented:
I worked up the following example:

1) Data covers 2012-09-01 through 2012-09-10
2) We want to find the timeslots meeting the following criteria:

a) First time slot of its respective day
b) Is occupied
c) Occurs 2012-09-02 through 2012-09-05

Based on that, this example seems to be working:

``````CREATE TABLE timeslots (TimeId int, StartDate datetime, EndDate datetime)

INSERT INTO timeslots (TimeId, StartDate, EndDate) VALUES
(1, '2012-09-01 07:00:00', '2012-09-01 08:00:00'),
(2, '2012-09-01 09:00:00', '2012-09-01 10:00:00'),
(3, '2012-09-01 11:00:00', '2012-09-01 12:00:00'),
(4, '2012-09-02 07:00:00', '2012-09-02 08:00:00'),
(5, '2012-09-02 09:00:00', '2012-09-02 10:00:00'),
(6, '2012-09-02 11:00:00', '2012-09-02 12:00:00'),
(7, '2012-09-03 07:00:00', '2012-09-03 08:00:00'),
(8, '2012-09-03 09:00:00', '2012-09-03 10:00:00'),
(9, '2012-09-03 11:00:00', '2012-09-03 12:00:00'),
(10, '2012-09-04 07:00:00', '2012-09-04 08:00:00'),
(11, '2012-09-04 09:00:00', '2012-09-04 10:00:00'),
(12, '2012-09-04 11:00:00', '2012-09-04 12:00:00'),
(13, '2012-09-05 07:00:00', '2012-09-05 08:00:00'),
(14, '2012-09-05 09:00:00', '2012-09-05 10:00:00'),
(15, '2012-09-05 11:00:00', '2012-09-05 12:00:00'),
(16, '2012-09-06 07:00:00', '2012-09-06 08:00:00'),
(17, '2012-09-06 09:00:00', '2012-09-06 10:00:00'),
(18, '2012-09-06 11:00:00', '2012-09-06 12:00:00'),
(19, '2012-09-07 07:00:00', '2012-09-07 08:00:00'),
(20, '2012-09-07 09:00:00', '2012-09-07 10:00:00'),
(21, '2012-09-07 11:00:00', '2012-09-07 12:00:00'),
(22, '2012-09-08 07:00:00', '2012-09-08 08:00:00'),
(23, '2012-09-08 09:00:00', '2012-09-08 10:00:00'),
(24, '2012-09-08 11:00:00', '2012-09-08 12:00:00'),
(25, '2012-09-09 07:00:00', '2012-09-09 08:00:00'),
(26, '2012-09-09 09:00:00', '2012-09-09 10:00:00'),
(27, '2012-09-09 11:00:00', '2012-09-09 12:00:00'),
(28, '2012-09-10 07:00:00', '2012-09-10 08:00:00'),
(29, '2012-09-10 09:00:00', '2012-09-10 10:00:00'),
(30, '2012-09-10 11:00:00', '2012-09-10 12:00:00')

CREATE TABLE occupied (OccupiedId int, TimeId int)

INSERT INTO occupied (OccupiedId, TimeId) VALUES
(1, 1),
(2, 10),
(3, 13),
(4, 25)

SELECT t1.TimeId, t1.StartDate, t1.EndDate
FROM timeslots t1 INNER JOIN
(SELECT MIN(t2.StartDate) AS StartDate
FROM timeslots t2
GROUP BY CONVERT(date, t2.StartDate)) z ON t1.StartDate = z.StartDate LEFT JOIN
occupied o ON t1.TimeId = o.TimeId
WHERE o.TimeId IS NOT NULL AND
t1.StartDate >= '2012-09-02' AND
t1.StartDate < '2012-09-06'

DROP TABLE occupied
DROP TABLE timeslots
``````

Note how I set up the date criteria in the WHERE clause :)
0

Author Commented:
Thank you very much:-)
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.