By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,903 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

How to filtrate duplicate data about technical support and compute rank of order

P: 19
I’ve got a big problem with this complicated SQL. Could anyone give me a hand?

My first aim(not the final goal) is:fliter the duplicate data with query statment( select not delete) , that is, the various entries of EndTime and Qos for a same ServiceID due to the repeated feedback. The rule is to only keep the records with the lowest Qos for a same ServiceID. If there are several records having the lowest Qos, then only keep whatever one of these records. The client accepts and prefers to this scheme. In this example, for the ID=6,7, and 8, just keep 6 or 7.I have got answer of this aim here:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT serviceid,tcid,endtime,qos FROM (SELECT * FROM service ORDER BY serviceid, qos, id) AS base GROUP BY serviceid
aim2: Qos=1 represents the Satisfied Service. The monthly total Satisfied Services achieved by each technical support engineer is referred to as “Personal Total Satisfied Services this Month”. Those technical support engineers whose “Personal Total Satisfied Service this Month” ranking among the top 2 in that month are referred to as the “Top 2 since this Month”. If they are outstanding enough to be entitled to the “Top 2 since this Month” every month, then they can be referred to as “Outstanding of Class 1”. All in all, this step is to compute the “Outstanding of Class 1”. In this example, “Top 2 since this Month” for January is Andrew and Jacob, and that for February are Andrew, Dlyan, and Jacob. Therefore, the honor title of “Outstanding of Class 1” is awarded to Andrew and Jacob.

please help me with aim2 (finally I should reach the aim 4, the final goal), result of aim 2 should be
Expand|Select|Wrap|Line Numbers
  1. TCID
  2. Andrew
  3. Jacob
explain:There are some technical service data (ServiceID, TCID, EndTime, and QoS) in a whole year, and field ID is a unique primary key because there are some duplicates.

some data:
Expand|Select|Wrap|Line Numbers
  1. ServiceID   ID  TCID    EndTime Qos
  2. 2000    2   Jacob   2011/1/1    2
  3. 2000    3   Jacob   2011/1/1    2
  4. 2001    4   Jacob   2011/1/1    2
  5. 2002    5   Jacob   2011/2/3    1
  6. 2003    6   Tyler   2011/1/4    1
Data Structure:
Expand|Select|Wrap|Line Numbers
  1. ID: Unique primary key of record 
  2. ServiceID: ID of a certain service
  3. TCID: ID of a technical support engineer 
  4. EndTime: Ending Time of aservice 
  5. Qos:Quality of service (1 Satisfied; 2 Average; 3Unsatisfactory/Dissatisfied).
  6.  
DDL and insert SQL(mysql5):
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE `service` (
  2.   `ServiceID` INTEGER(11) NOT NULL,
  3.   `ID` INTEGER(11) NOT NULL ,
  4.   `TCID` VARCHAR(40)  NOT NULL,
  5.   `EndTime` DATE NOT NULL,
  6.   `Qos` CHAR(1)  NOT NULL,
  7.   PRIMARY KEY (`ID`),
  8.   UNIQUE KEY `ID` (`ID`)
  9. );
  10. COMMIT;
  11. INSERT INTO `service` (`ServiceID`, `ID`, `TCID`, `EndTime`, `Qos`) VALUES
  12.   (2004, 9, 'Jacob', '2011-02-04', '1'),
  13.   (2000, 2, 'Jacob', '2011-01-01', '2'),
  14.   (2000, 3, 'Jacob', '2011-01-01', '2'),
  15.   (2001, 4, 'Jacob', '2011-01-01', '2'),
  16.   (2002, 5, 'Jacob', '2011-02-03', '1'),
  17.   (2003, 6, 'Tyler', '2011-01-04', '1'),
  18.   (2003, 7, 'Tyler', '2011-01-04', '1'),
  19.   (2003, 8, 'Tyler', '2011-01-03', '2'),
  20.   (2005, 10, 'Jacob', '2011-02-05', '1'),
  21.   (2006, 11, 'Jacob', '2011-02-04', '2'),
  22.   (2007, 12, 'Jacob', '2011-01-08', '1'),
  23.   (2008, 13, 'Tyler', '2011-02-06', '1'),
  24.   (2009, 14, 'Dylan', '2011-02-08', '1'),
  25.   (2010, 15, 'Dylan', '2011-02-09', '1'),
  26.   (2014, 16, 'Andrew', '2011-01-01', '1'),
  27.   (2013, 17, 'Andrew', '2011-01-01', '1'),
  28.   (2012, 18, 'Andrew', '2011-02-19', '1'),
  29.   (2011, 19, 'Andrew', '2011-02-02', '1'),
  30.   (2015, 20, 'Andrew', '2011-02-01', '1'),
  31.   (2016, 21, 'Andrew', '2011-01-19', '1'),
  32.   (2017, 22, 'Jacob', '2011-01-01', '1'),
  33.   (2018, 23, 'Dylan', '2011-02-03', '1'),
  34.   (2019, 24, 'Dylan', '2011-01-09', '1'),
  35.   (2020, 25, 'Dylan', '2011-01-01', '1'),
  36.   (2021, 26, 'Andrew', '2011-01-03', '1'),
  37.   (2021, 27, 'Dylan', '2011-01-11', '1'),
  38.   (2022, 28, 'Jacob', '2011-01-09', '1'),
  39.   (2023, 29, 'Tyler', '2011-01-19', '1'),
  40.   (2024, 30, 'Andrew', '2011-02-01', '1'),
  41.   (2025, 31, 'Dylan', '2011-02-03', '1'),
  42.   (2026, 32, 'Jacob', '2011-02-04', '1'),
  43.   (2027, 33, 'Tyler', '2011-02-09', '1'),
  44.   (2028, 34, 'Daniel', '2011-01-06', '1'),
  45.   (2029, 35, 'Daniel', '2011-02-01', '1');
  46.  COMMIT;
Almost forget to say that, just a reminder, the client only accept the SQL instead of stored procedure in database to implement it. And I only need query statement because customer did not allow us to write database.

this is my first 2 aims, there are 2 more, I am intended to complete all these steps one after another. Could anyone help achieve the first goal? I know it is quite complicated and many thanks to you in advance.

aim3:Then, to compute the “Outstanding of Class 2” (the engineers of “Top 2 since this Month” are not included) equals to compute those ranking the third and the forth places. In this example, the “Outstanding of Class 2” is the Tyler.

aim4:The final goal is to combine the “Outstanding of Class 1” with “Outstanding of Class 2”. The result will be ultimately transferred to report for rendering. My dataSet is just like:
Expand|Select|Wrap|Line Numbers
  1. TCID           level
  2. Andrew         top2
  3. Jacob          top2
  4. Tyler          top4
Apr 17 '12 #1
Share this Question
Share on Google+
16 Replies


Rabbit
Expert Mod 10K+
P: 12,366
Instead, you should put a unique constraint on the service ID so that there can never be more than one. And then you can just update the qos when needed. Then you won't have this problem.

As for your other questions, please create a different thread for each question.
Apr 17 '12 #2

P: 19
hi,Rabbit
thank you for your answer. but I only need query statement because customer did not allow us to write database. actually, I had suggested to add a field "ID", It is a big concession to the client. We are BPO and working on the business online database(so I cant add a stored procedure to the database).

I think the correct answer is below (but I cant write it with SQL):

Expand|Select|Wrap|Line Numbers
  1. 2000    2    Jacob    2011/1/1    2
  2. 2001    4    Jacob    2011/1/1    2
  3. 2002    5    Jacob    2011/2/3    1
  4. 2003    6    Tyler    2011/1/4    1
  5. 2004    9    Jacob    2011/2/4    1
  6. 2005    10    Jacob    2011/2/5    1
  7. 2006    11    Jacob    2011/2/4    2
  8. 2007    12    Jacob    2011/1/8    1
  9. 2008    13    Tyler    2011/2/6    1
  10. 2009    14    Dyla    2011/2/8    1
  11. 2010    15    Dyla    2011/2/9    1
  12. 2011    19    Andrew    2011/2/2    1
  13. 2012    18    Andrew    2011/2/19    1
  14. 2013    17    Andrew    2011/1/1    1
  15. 2014    16    Andrew    2011/1/1    1
  16. 2015    20    Andrew    2011/2/1    1
  17. 2016    21    Andrew    2011/1/19    1
  18. 2017    22    Jacob    2011/1/1    1
  19. 2018    23    Dyla    2011/2/3    1
  20. 2019    24    Dyla    2011/1/9    1
  21. 2020    25    Dyla    2011/1/1    1
  22. 2021    26    Andrew    2011/1/3    1
  23. 2022    28    Jacob    2011/1/9    1
  24. 2023    29    Tyler    2011/1/19    1
  25. 2024    30    Andrew    2011/2/1    1
  26. 2025    31    Dyla    2011/2/3    1
  27. 2026    32    Jacob    2011/2/4    1
  28. 2027    33    Tyler    2011/2/9    1
  29. 2028    34    Daniel    2011/1/6    1
  30. 2029    35    Daniel    2011/2/1    1
  31.  
Apr 17 '12 #3

Rabbit
Expert Mod 10K+
P: 12,366
Create an aggregate subquery that returns the MIN() qos by service id joined to the table. Take that query and use it to return the MIN() id by service id. Then join that back to the table.
Apr 17 '12 #4

P: 19
hi, Rabbit
a nice people give me a SQLServer solution, but I still need a mysql solution. statement of sqlserver is:
Expand|Select|Wrap|Line Numbers
  1. select * from ( select *, row_number() over (partition by [ServiceID] order by Qos) RN from [Service] ) T where RN = 1 order by [ServiceID]
I tested it and I got the correct answer, but I still need a mysql statement, plz give me some advice, how to change it to mysql style?
Apr 18 '12 #5

Rabbit
Expert Mod 10K+
P: 12,366
MySQL has no row number function. As such, you can use a ranking query to simulate the row number or you can use the alternative algorithm proposed in post 4.
Apr 18 '12 #6

P: 19
thank you Rabbit, but how to write it, plz help me
Apr 18 '12 #7

P: 19
hi, I have got the anwser of aim1:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT serviceid,tcid,endtime,qos FROM (SELECT * FROM service ORDER BY serviceid, qos, id) AS base GROUP BY serviceid
please help me with aim2, 3 and 4(my final goal)
Apr 18 '12 #8

Rabbit
Expert Mod 10K+
P: 12,366
Like I said earlier, we restrict questions to one per thread. If you have additional questions, please post each in a new thread.
Apr 18 '12 #9

P: 19
thank you Rabbit, I will do as what u have said
Apr 19 '12 #10

P: 19
many thanks for Rabbit
Apr 19 '12 #11

P: 19
Finally,I find a solution(http://stackoverflow.com/questions/1...75434#10275434).
that is using esProc to solve this problem. esProc is the only solution I found. Yes, It can return a resultSet object and invoked by report Tool ( I'm using Jasper). But I still need a SQL solution. so, please told me If there are any SQL solution. Thanks for your reply.



Here are some information about this solution: http://www.esproc.com/tour/what-is-esproc.html

Or here is a SQL Server Solution(but not fit for mysql):http://social.msdn.microsoft.com/For...2-e998ea3387d9

Hope this helps people that meet the same trouble.
Apr 24 '12 #12

Rabbit
Expert Mod 10K+
P: 12,366
I just looked at your so called solution for aim 1. I had assumed you tested it because you said you solved aim 1. But that SQL won't even run. It is syntactically wrong.

What you need to do is to return the MIN Qos with grouping on the appropriate fields.

You also don't give nearly enough information on how to handle the different variations in your data. For example, you don't say how to handle the situation where the same ServiceID has different TCIDs with the same Qos. You also don't say how to handle the situation where the same ServiceID can span different months.
Apr 24 '12 #13

P: 19
hi Rabbit,
Thank you for your reply. Maybe I didn't give the clear information, I'm sorry for that.

I have run that SQL using EMS SQL Manger just now and few days befor, It can run correctly without any syntax wrong. I have taken a screenshot as below:


Let me explain:
1. this statement was not written out by myself. It was from another net friends.
2. Actually, I do not understand this statement very well. I treat it as a correct answer because It give me the same result as my handwork (in the first reply post to you).
3. I think this statement is only mysql-styled, not a ansi syntax, because the same statment can not run on the SQL Server.


then, I agree I didn't make it clear. Let me make more explain:
1. the same ServiceID has different TCIDs with the same Qos.----Yes ,It may be. we only need a single ServiceID left that has the minimum Qos.
2.the same ServiceID can span different months.
----Yes, There may be 2 record with the same ServiceID and the same minimum Qos but 2 diffrent months. just keep one left (whatever one of the 2 records)

at last, thank you for your help, and for your future help.
Attached Images
File Type: jpg sample.jpg (73.5 KB, 346 views)
Apr 24 '12 #14

P: 19
after filtering duplicate date, the TCID and cnt(Personal Total Satisfied Services this Month) and rank should be:
Expand|Select|Wrap|Line Numbers
  1.     month    cnt    TCID    rank
  2.     1    4    Andrew    1
  3.     1    1    Daniel    5
  4.     1    2    Dyla    3
  5.     1    3    Jacob    2
  6.     1    2    Tyler    3
  7.     2    4    Andrew    1
  8.     2    1    Daniel    5
  9.     2    4    Dyla    1
  10.     2    4    Jacob    1
  11.     2    2    Tyler    4
  12.  
  13.  
according to data above:
Top 2 since January is: Andrew, Jacob
Top 2 since February is: Andrew, Dyla, Jacob
so Outstanding of Class 1 is: Andrew, Jacob

Top 4 since January is: Dyla, Tyler
Top 4 since February is: Tyler
so Outstanding of Class 2 is: Tyler

please continue to help me.
Apr 24 '12 #15

Rabbit
Expert Mod 10K+
P: 12,366
MySQL is very close to ANSI. The way you want to handle the data is a bit arbitrary but that's fine as long as the rules are defined.

As for your second post. Again that's a different question, it needs to stay with the other thread. I'm not answering that here.
Apr 24 '12 #16

P: 19
the further discuss has moved to:http://bytes.com/topic/mysql/answers...-a#post3716261

thanks for Rabbit's help.
Apr 26 '12 #17

Post your reply

Sign in to post your reply or Sign up for a free account.