Comparing rows in same group - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparing rows in same group

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2013-02-26 : 19:23:14

Hi Guys,


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?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-26 : 19:48:00

[code]SELECT * FROM Tbl a WHERE NOT EXISTS
(SELECT * FROM Tbl b WHERE b.[Room ID] = a.[Room ID]
AND b.StartTime < a.StartTime AND b.EndTime > a.EndTime)[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 22:37:51

can there be partial overlaps? like say

GR4 4 5 1100 1600

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

velvettiger
Posting Yak Master

115 Posts

Posted - 2013-02-27 : 05:29:27

Hi Visakh,

Nope there can not be partial overlaps

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 05:32:40

then James's suggestion would be sufficient

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

velvettiger
Posting Yak Master

115 Posts

Posted - 2013-02-27 : 06:12:50

Hi James,

That worked great but I would like to sum the values only returned by the query but its not working. The ro0m

GR4	2 4 0910 1300

is included in the total count for GR4.

The results should be


RoomId   TotalHours
GR4      12
GR11     15

But the below query outputs the following

RoomId   TotalHours
GR4      16
GR11     15


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
     )
)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-27 : 07:45:29

What does the inner query return? It should return only two rows for GR4. If it returns all 3 rows, that may have to do with the data types of the StartTime and EndTime columns.

velvettiger
Posting Yak Master

115 Posts

Posted - 2013-02-27 : 08:17:29

Hi James,

It only returns two rows which is correct but when the outer query sums the results from the inner query it includes the total of the three rows for GR4 instead of the two rows.

[CODE]
The inner query returns the below results:

GR4 1 9 0810 1700
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

Therefore when totaled by the outer query it should return

Posted - 02/27/2013 : 06:12:50 Show Profile Email Poster Edit Reply Reply with Quote Delete Reply
Hi James,

That worked great but I would like to sum the values only returned by the query but its not working. The ro0m

GR4 2 4 0910 1300

is included in the total count for GR4.

The results should be

RoomId TotalHours
GR4 12
GR11 15

but it doesn't
[/code]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-27 : 09:12:23

I am not seeing that - see the sample code below. You can copy it and run it from an SSMS window. The results it returns are as follows:

CREATE TABLE #tmp(Room VARCHAR(32), ID int , Hours INT, StartTime VARCHAR(4), EndTime VARCHAR(4));
INSERT INTO #tmp VALUES 
('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');


select Room,sum(Hours)TotalHours
FROM(
      SELECT * 
      FROM #tmp a 
      WHERE NOT EXISTS
     (SELECT * 
      FROM #tmp b 
      WHERE b.Room = a.Room
      AND b.StartTime < a.StartTime AND b.EndTime > a.EndTime
     )
)s
GROUP BY Room;

DROP TABLE #tmp;

------------------- RESULTS -------------------------------------------
Room	TotalHours
GR4	12
LR11	15

velvettiger
Posting Yak Master

115 Posts

Posted - 2013-02-27 : 11:56:40

I got it working but if I had the following table


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

I know the exist would handle over lapping entries but then how do you go about removing duplicates? For example row 7. Three appears twice under Monday and Tuesday at the same time. I would only want it to appear once for the room GR4. In the end I would need to produce these results for the 7 days of the week.

My draft query using the EXIST code in the previous post.


SELECT distinct done.ssrmeet_room_code,
        SUM(MONDAY)Monday,
        SUM(TUESDAY)Tuesday

FROM(
select distinct ssrmeet_room_code,
      IDS,
      MONDAY,
      TUESDAY

from(
      SELECT distinct ssrmeet_room_code,
      IDS,
      MONDAY,
      TUESDAY,

      ssrmeet_begin_time,
      ssrmeet_end_time
      FROM(
                       select DISTINCT ssrmeet_room_code,
                            row_number() OVER (partition by ssrmeet_room_code ORDER BY ssrmeet_room_code )IDS,
                           ssrmeet_begin_time,
                            ssrmeet_end_time,
                            (CASE WHEN ssrmeet_MON_DAY = 'M'AND ssrmeet_room_code is not null THEN (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END) MONDAY,
                            (CASE WHEN ssrmeet_TUE_DAY = 'T'AND ssrmeet_room_code is not null THEN (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END) TUESDAY
                          
                       
                          
            from ssbsect s inner join ssrmeet M on s.ssbsect_term_code=M.ssrmeet_term_code and s.ssbsect_crn=M.ssrmeet_crn  
            where ssbsect_term_code = '201220' AND 
                  ssrmeet_term_code = '201220' and
                   ssrmeet_room_code is not null AND
                  ((ssrmeet_room_code = 'GR4') OR (ssrmeet_room_code = 'LR11'))  

           group by ssrmeet_room_code, 
           ssrmeet_begin_time, 
           ssrmeet_end_time,
           (CASE WHEN ssrmeet_TUE_DAY = 'T'AND ssrmeet_room_code is not null THEN (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END),         
           (CASE WHEN ssrmeet_MON_DAY = 'M'AND ssrmeet_room_code is not null THEN (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END)         
           HAVING (
                     ((CASE WHEN ssrmeet_MON_DAY = 'M' AND ssrmeet_room_code is not null  THEN (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END) is not null) or
                     ((CASE WHEN ssrmeet_TUE_DAY = 'T' AND ssrmeet_room_code is not null THEN  (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END) IS NOT NULL)  
                  )
           )w 
)tbla
where not exists
(
    select *
    from(
          SELECT distinct ssrmeet_room_code,
                          IDS,
                          MONDAY,
                          TUESDAY,

                          ssrmeet_begin_time,
                           ssrmeet_end_time
          FROM(
                 

                       select DISTINCT ssrmeet_room_code,
                            row_number() OVER (partition by ssrmeet_room_code ORDER BY ssrmeet_room_code )IDS,
                            ssrmeet_begin_time,
                            ssrmeet_end_time,
                            (CASE WHEN ssrmeet_MON_DAY = 'M'AND ssrmeet_room_code is not null THEN (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END) MONDAY,
                            (CASE WHEN ssrmeet_TUE_DAY = 'T'AND ssrmeet_room_code is not null THEN (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END) TUESDAY
                          
                      
                          
            from ssbsect s inner join ssrmeet M on s.ssbsect_term_code=M.ssrmeet_term_code and s.ssbsect_crn=M.ssrmeet_crn  
            where ssbsect_term_code = '201220' AND 
                  ssrmeet_term_code = '201220' and
                   ssrmeet_room_code is not null AND
                  ((ssrmeet_room_code = 'GR4') OR (ssrmeet_room_code = 'LR11'))  

           GROUP BY ssrmeet_room_code, ssrmeet_begin_time, ssrmeet_end_time,
           (CASE WHEN ssrmeet_MON_DAY = 'M' AND ssrmeet_room_code is not null  THEN (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END),
           (CASE WHEN ssrmeet_TUE_DAY = 'T' AND ssrmeet_room_code is not null THEN  (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END)
           HAVING (
                    ((CASE WHEN ssrmeet_MON_DAY = 'M' AND ssrmeet_room_code is not null  THEN (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END) is not null) or
                    ((CASE WHEN ssrmeet_TUE_DAY = 'T' AND ssrmeet_room_code is not null THEN  (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 ELSE 0 END) IS NOT NULL)  
                  )

          )w
        )tblb
WHERE tblb.ssrmeet_room_code = tbla.ssrmeet_room_code
AND (tblb.SSRMEET_BEGIN_TIME < tbla.SSRMEET_BEGIN_TIME AND tblb.SSRMEET_end_TIME > tbla.SSRMEET_end_TIME)


)
group by IDS, 
         ssrmeet_room_code,
         MONDAY,
         TUESDAY

)done
GROUP BY done.ssrmeet_room_code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources