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

    Unanswered: Comparing Rows in the same group

    Hi Guys,

    Code:
    Room ID   Hours StartTime EndTime
    GR4	1	9	0810	1700
    GR4	2	4	0910	1300
    GR4	3	3	1710	2000
    LR11	1	1	0810	0900
    LR11	2	1	0910	1000
    LR11	3	2	1010	1200
    LR11	4	1	1210	1300
    LR11	5	3	1310	1600
    LR11	6	2	1610	1800
    LR11	7	1	1810	1900
    LR11	8	1	1910	2000
    LR11	9	3	2010	2300
    As you can see each room is assigned and id and once the room changes the id starts from 1 again. I don't want the 2nd row to appear because 9am - 1pm is in 8am - 5pm. How do I remove this row?
    Last edited by bajanstar; 02-26-13 at 20:37.

  2. #2
    Join Date
    Oct 2009
    Posts
    27
    Hi,

    Check the following query,


    SELECT DISTINCT
    a.room, a.id, a.HOURS1, b.starttime, b.endtime
    FROM room a JOIN room b ON
    b.starttime < a.starttime AND b.endtime > a.endtime



  3. #3
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    select t.* 
    from MyTable as t
    left join MyTable as p
        on p.RoomId = t.RoomId and
           p.Id = t.Id - 1
    where 
        t.Id = 1 or
        t.StartTime > p.EndTime
    Hope this helps.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Here's a slightly less convoluted way to acheive the same effect.
    Code:
    DROP TABLE #rooms
    GO
    CREATE TABLE #rooms (
       Room         VARCHAR(9)  NOT NULL
    ,  ID           INT         NOT NULL
    ,  [Hours]      INT         NOT NULL
    ,  StartTime    TIME        NOT NULL
    ,  EndTime      TIME        NOT NULL
       )
    
    INSERT INTO #rooms
       VALUES
       ('GR4',  1, 9, '08:10', '17:00'),  ('GR4',  2, 4, '09:10', '13:00')
    ,  ('GR4',  3, 3, '17:10', '20:00'),  ('LR11', 1, 1, '08:10', '09:00')
    ,  ('LR11', 2, 1, '09:10', '10:00'),  ('LR11', 3, 2, '10:10', '12:00')
    ,  ('LR11', 4, 1, '12:10', '13:00'),  ('LR11', 5, 3, '13:10', '16:00')
    ,  ('LR11', 6, 2, '16:10', '18:00'),  ('LR11', 7, 1, '18:10', '19:00')
    ,  ('LR11', 8, 1, '19:10', '20:00'),  ('LR11', 9, 3, '20:10', '23:00')
    
    SELECT *
       FROM #rooms AS a
       LEFT JOIN #rooms AS b
          ON (b.Room = a.Room
    	  AND b.StartTime < a.StartTime
    	  AND a.StartTime < b.EndTime)
       WHERE  b.Room IS	NULL
    -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
  •