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
 Removing Duplicates

Author  Topic 

velvettiger
Posting Yak Master

115 Posts

Posted - 2013-02-27 : 18:11:47
Hi Guys,

I have 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


To remove the overlapping rows like row GR4 2. The below query was used.

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 22:47:37
when you remove those duplicates which one you need to retain? they've different values under Monday,Tuesday etc values so you should determine which one under them needs to be retained

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

Go to Top of Page

velvettiger
Posting Yak Master

115 Posts

Posted - 2013-02-28 : 15:09:39
Hi Visakh,

I would only like to retain one of the duplicate values. Duplicate meaning if the room is being used on the same day with the same start and end time. Therefore in the below list row number 7 would be removed.



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


Go to Top of Page

velvettiger
Posting Yak Master

115 Posts

Posted - 2013-02-28 : 18:15:58
Hi Guys,

Does any one know how to pull the unique records by coloumn?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-28 : 18:48:23
What makes it unfriendly to work with is that the data is not normalized. I am trying to fix that by unpivoting in the following. Also, why did you choose to remove row 7 rather than 5 and 6? In any case, here is my attempt - not sure that will do it for you though. But the sample data I created should help someone else who may want to post a solution:
CREATE TABLE #tmp(Room varchar(32), ID INT, starttime VARCHAR(4), endtime VARCHAR(4),
Monday INT, Tuesday INT);

INSERT INTO #tmp VALUES
('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')



SELECT
Room, starttime,endtime,
ISNULL(Monday,0) AS Monday,
ISNULL(Tuesday,0) AS Tuesday
FROM
(
SELECT Room, starttime,endtime, hours, day FROM #tmp UNPIVOT
(Hours FOR Day IN ([Monday],[Tuesday]))U
WHERE Hours <> 0
GROUP BY Room, starttime,endtime, hours, DAY
)s PIVOT (MAX(Hours) FOR DAY IN ([Monday],[Tuesday]))P

DROP TABLE #tmp;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 23:12:19
I'm still not sure on what basis you decide a record to be retained/removed. is it first,last or random one in each group?

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

Go to Top of Page

velvettiger
Posting Yak Master

115 Posts

Posted - 2013-03-01 : 12:59:59
Hi visakh,

It doesn't matter which one would have been removed. I say this because when you look at the entries for Monday, there are three 1710-2000 times appearing and two of those times slots are occupied for 3hours in GR4. So I would have just liked 1710-2000 3hours to appear only once under the monday column.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 13:36:13
you're missing my point. You've different values coming for Monday,Tuesday columns. So you need to determine which of those set of values you want in output.

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

Go to Top of Page

erikhaselhofer
Starting Member

30 Posts

Posted - 2013-03-01 : 17:00:16
Velvet, your data is confusing in two places.

SELECT room, starttime, endtime, monday
FROM #tmp
WHERE room = 'GR4'


room starttime endtime monday
GR4 0810 1700 9
GR4 0910 1300 4

GR4 0910 1700 0
GR4 1710 1800 0
GR4 1710 2000 0
GR4 1710 2000 3
GR4 1710 2000 3

GR4 1810 2100 0


In the green section, should both records appear? If so, and I think they should, it implies that you have two classes scheduled at the same time.

In the red section, you have two records scheduled for exactly the same time. Which one wins the SQL lottery? If the answer is neither then you could do something like this, assuming I didn't miss any landmines further on.

SELECT DISTINCT room, starttime, endtime, monday
FROM #tmp
WHERE room = 'GR4' AND monday <> 0


room starttime endtime monday
GR4 0810 1700 9
GR4 0910 1300 4
GR4 1710 2000 3


Not sure if that will help but I think it's kind of what you are trying to do.

As far as setting up the query to just show when the room is busy, thereby eliminating the second record, that's a much different problem.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-02 : 02:17:24
unless OP explains what exact rules are, we can only play guessing game

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

Go to Top of Page
   

- Advertisement -