1. Registered User
Join Date
Mar 2005
Posts
15

Hi Guys,

I have the following table

Code:
```Room   ID       StartTime EndTime Monday Tuesday
GR4	1	0810	1700	9	9
GR4	2	0910	1300	4	4
GR4	3	0910	1700	0	0
GR4	4	1710	1800	0	0
GR4	5	1710	2000	0	3
GR4	6	1710	2000	3	0
GR4	7	1710	2000	3	3
GR4	8	1810	2100	0	0
LR11	1	0810	0900	0	1
LR11	2	0810	1000	0	0
LR11	3	0910	1000	0	1
LR11	4	0910	1100	2	0
LR11	5	1010	1100	0	0
LR11	6	1010	1200	0	0
LR11	7	1010	1200	0	2
LR11	8	1110	1200	0	0
LR11	9	1110	1200	1	0
LR11	10	1110	1300	0	0
LR11	11	1210	1300	0	0
LR11	12	1210	1300	0	1
LR11	13	1210	1400	2	0
LR11	14	1310	1400	0	0
LR11	15	1310	1500	0	0
LR11	16	1310	1600	0	3
LR11	17	1410	1600	0	0
LR11	18	1410	1600	2	0
LR11	19	1410	1700	0	0
LR11	20	1610	1800	0	2
LR11	21	1710	1800	0	0
LR11	22	1710	1900	2	0
LR11	23	1810	1900	0	0
LR11	24	1810	1900	0	1
LR11	25	1810	2000	0	0
LR11	26	1910	2000	0	0
LR11	27	1910	2000	0	1
LR11	28	2010	2100	1	0
LR11	29	2010	2300	0	3```

To remove the overlapping rows like row GR4 2. The below query was used.

Code:
```select roomID,sum(Hours)TotalHours
FROM(
SELECT *
FROM Tbl a
WHERE NOT EXISTS
(SELECT *
FROM Tbl b
WHERE b.RoomID = a.RoomID
AND b.StartTime < a.StartTime AND b.EndTime > a.EndTime
)
)```

How do you go about removing one of the 2 3-hour entries under monday with a start time of 1710 and an end time of 2000? And likewise for tuesday where they are 2 3-hour entries for 1710 to 2000?

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579