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

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
Asked:
johnkainn
  • 2
1 Solution
 
Evan CutlerCommented:
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
 
johnkainnAuthor 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
 
Patrick MatthewsCommented:
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

Open in new window


Note how I set up the date criteria in the WHERE clause :)
0
 
johnkainnAuthor Commented:
Thank you very much:-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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