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

[bbaaking]

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:

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

TCID
Andrew
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:

ServiceID   ID  TCID    EndTime Qos
2000    2   Jacob   2011/1/1    2
2000    3   Jacob   2011/1/1    2
2001    4   Jacob   2011/1/1    2
2002    5   Jacob   2011/2/3    1
2003    6   Tyler   2011/1/4    1

Data Structure:

ID: Unique primary key of record 
ServiceID: ID of a certain service
TCID: ID of a technical support engineer 
EndTime: Ending Time of aservice 
Qos：Quality of service (1 Satisfied; 2 Average; 3Unsatisfactory/Dissatisfied).
 

DDL and insert SQL(mysql5):

CREATE TABLE `service` (
  `ServiceID` INTEGER(11) NOT NULL,
  `ID` INTEGER(11) NOT NULL ,
  `TCID` VARCHAR(40)  NOT NULL,
  `EndTime` DATE NOT NULL,
  `Qos` CHAR(1)  NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
);
COMMIT;
INSERT INTO `service` (`ServiceID`, `ID`, `TCID`, `EndTime`, `Qos`) VALUES
  (2004, 9, 'Jacob', '2011-02-04', '1'),
  (2000, 2, 'Jacob', '2011-01-01', '2'),
  (2000, 3, 'Jacob', '2011-01-01', '2'),
  (2001, 4, 'Jacob', '2011-01-01', '2'),
  (2002, 5, 'Jacob', '2011-02-03', '1'),
  (2003, 6, 'Tyler', '2011-01-04', '1'),
  (2003, 7, 'Tyler', '2011-01-04', '1'),
  (2003, 8, 'Tyler', '2011-01-03', '2'),
  (2005, 10, 'Jacob', '2011-02-05', '1'),
  (2006, 11, 'Jacob', '2011-02-04', '2'),
  (2007, 12, 'Jacob', '2011-01-08', '1'),
  (2008, 13, 'Tyler', '2011-02-06', '1'),
  (2009, 14, 'Dylan', '2011-02-08', '1'),
  (2010, 15, 'Dylan', '2011-02-09', '1'),
  (2014, 16, 'Andrew', '2011-01-01', '1'),
  (2013, 17, 'Andrew', '2011-01-01', '1'),
  (2012, 18, 'Andrew', '2011-02-19', '1'),
  (2011, 19, 'Andrew', '2011-02-02', '1'),
  (2015, 20, 'Andrew', '2011-02-01', '1'),
  (2016, 21, 'Andrew', '2011-01-19', '1'),
  (2017, 22, 'Jacob', '2011-01-01', '1'),
  (2018, 23, 'Dylan', '2011-02-03', '1'),
  (2019, 24, 'Dylan', '2011-01-09', '1'),
  (2020, 25, 'Dylan', '2011-01-01', '1'),
  (2021, 26, 'Andrew', '2011-01-03', '1'),
  (2021, 27, 'Dylan', '2011-01-11', '1'),
  (2022, 28, 'Jacob', '2011-01-09', '1'),
  (2023, 29, 'Tyler', '2011-01-19', '1'),
  (2024, 30, 'Andrew', '2011-02-01', '1'),
  (2025, 31, 'Dylan', '2011-02-03', '1'),
  (2026, 32, 'Jacob', '2011-02-04', '1'),
  (2027, 33, 'Tyler', '2011-02-09', '1'),
  (2028, 34, 'Daniel', '2011-01-06', '1'),
  (2029, 35, 'Daniel', '2011-02-01', '1');
 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:

TCID           level
Andrew         top2
Jacob          top2
Tyler          top4

[Rabbit]

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.

[bbaaking]

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

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

[Rabbit]

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.

[bbaaking]

hi, Rabbit
a nice people give me a SQLServer solution, but I still need a mysql solution. statement of sqlserver is:

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?

[Rabbit]

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.

[bbaaking]

thank you Rabbit, but how to write it, plz help me

[bbaaking]

hi, I have got the anwser of aim1:

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)

[Rabbit]

Like I said earlier, we restrict questions to one per thread. If you have additional questions, please post each in a new thread.

[bbaaking]

thank you Rabbit, I will do as what u have said

[bbaaking]

many thanks for Rabbit

[bbaaking]

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.

[Rabbit]

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.

[bbaaking]

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.

[bbaaking]

after filtering duplicate date, the TCID and cnt(Personal Total Satisfied Services this Month) and rank should be:

    month    cnt    TCID    rank
    1    4    Andrew    1
    1    1    Daniel    5
    1    2    Dyla    3
    1    3    Jacob    2
    1    2    Tyler    3
    2    4    Andrew    1
    2    1    Daniel    5
    2    4    Dyla    1
    2    4    Jacob    1
    2    2    Tyler    4
 
 

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.

[Rabbit]

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.

[bbaaking]

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

thanks for Rabbit's help.