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
 Removing Duplicates		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

velvettiger
Posting Yak Master

115 Posts
Posted - 02/27/2013 :  18:11:47  Show Profile  Reply with Quote
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

India
48097 Posts
Posted - 02/27/2013 :  22:47:37  Show Profile  Reply with Quote
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 - 02/28/2013 :  15:09:39  Show Profile  Reply with Quote
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 - 02/28/2013 :  18:15:58  Show Profile  Reply with Quote
Hi Guys,

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

James K
Flowing Fount of Yak Knowledge

1744 Posts
Posted - 02/28/2013 :  18:48:23  Show Profile  Reply with Quote
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

India
48097 Posts
Posted - 02/28/2013 :  23:12:19  Show Profile  Reply with Quote
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 - 03/01/2013 :  12:59:59  Show Profile  Reply with Quote
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

India
48097 Posts
Posted - 03/01/2013 :  13:36:13  Show Profile  Reply with Quote
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

14 Posts
Posted - 03/01/2013 :  17:00:16  Show Profile  Reply with Quote
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

India
48097 Posts
Posted - 03/02/2013 :  02:17:24  Show Profile  Reply with Quote
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
  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.12 seconds. Powered By: Snitz Forums 2000