Solved

# Occupied in first hour each day

Posted on 2012-09-05
586 Views
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
Question by:johnkainn

LVL 9

Expert Comment

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 Comment

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

LVL 92

Accepted Solution

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 Closing Comment

Thank you very much:-)
0

## Featured Post

### Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even withinâ€¦
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE sâ€¦