• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

19. Some Common Data Manipulation Problems > 19.2. Overlapping Intervals I

Overlapping Intervals I

In Table 19-2, the rows denote intervals of time. The problem is determining, for each hour of the day, the number of intervals that contain this hour.

Table 19-2. Overlap table: Rows denote time intervals

Interval

StartTime

EndTime

1

4:00:00 PM

7:00:00 PM

2

5:00:00 PM

9:00:00 PM

3

2:00:00 PM

6:00:00 PM

4

8:00:00 PM

11:59:00 PM

5

12:00:00 PM

4:00:00 PM

For this, we also use an HOURS table (see Table 19-3).

Table 19-3. Hours table

Hours

12:00:00 PM

1:00:00 PM

2:00:00 PM

3:00:00 PM

4:00:00 PM

5:00:00 PM

6:00:00 PM

7:00:00 PM

8:00:00 PM

9:00:00 PM

10:00:00 PM

11:00:00 PM

11:59:00 PM

Solution

This problem can be solved using a nested SELECT statement (that is, a subquery). Here is the code:


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint