| Author |
 Topic  |
|
|
velvettiger
Posting Yak Master
115 Posts |
 Posted - 02/26/2013 : 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
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 02/26/2013 : 19:48:00
|
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) |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 02/26/2013 : 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 - 02/27/2013 : 05:29:27
|
Hi Visakh,
Nope there can not be partial overlaps |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 02/27/2013 : 05:32:40
|
then James's suggestion would be sufficient
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
velvettiger
Posting Yak Master
115 Posts |
Posted - 02/27/2013 : 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
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 02/27/2013 : 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 - 02/27/2013 : 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
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 02/27/2013 : 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 - 02/27/2013 : 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
|
|
|
| |
Topic |
|
|
|
Comparing rows in same group
