Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2005
    Posts
    15

    Unanswered: Removing Duplicates

    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. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    DISTINCT.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •