SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Comparing rows in same group
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

velvettiger
Posting Yak Master

115 Posts

Posted - 02/26/2013 :  19:23:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3322 Posts

Posted - 02/26/2013 :  19:48:00  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/26/2013 :  22:37:51  Show Profile  Reply with Quote
can there be partial overlaps? like say

GR4 4 5 1100 1600

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

Go to Top of Page

velvettiger
Posting Yak Master

115 Posts

Posted - 02/27/2013 :  05:29:27  Show Profile  Reply with Quote
Hi Visakh,

Nope there can not be partial overlaps
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/27/2013 :  05:32:40  Show Profile  Reply with Quote
then James's suggestion would be sufficient

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

Go to Top of Page

velvettiger
Posting Yak Master

115 Posts

Posted - 02/27/2013 :  06:12:50  Show Profile  Reply with Quote
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
     )
)

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3322 Posts

Posted - 02/27/2013 :  07:45:29  Show Profile  Reply with Quote
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.
Go to Top of Page

velvettiger
Posting Yak Master

115 Posts

Posted - 02/27/2013 :  08:17:29  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3322 Posts

Posted - 02/27/2013 :  09:12:23  Show Profile  Reply with Quote
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

Go to Top of Page

velvettiger
Posting Yak Master

115 Posts

Posted - 02/27/2013 :  11:56:40  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000