Question

Locked

complicated SQL: How to filter duplicate data about technical support and compute rank?

By bbaaking ·
hi,
I have got a big problem. Could anyone give me a hand?

My first aim is:Remove the duplicate data, 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. And I have got answer of this aim here
<code>SELECT MIN(Qos), serviceid, id, TCID, EndTime from service
GROUP BY serviceid</code>

or<code> SELECT DISTINCT serviceid,tcid,endtime,qos FROM (SELECT * FROM service ORDER BY serviceid, qos, id) AS base GROUP BY serviceid</code>

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<code>
TCID
Andrew
Jacob</code>


explain:There are some technical service data (ServiceID, TCID, EndTime, and QoS) submitted by client in a whole year, and I have added a unique primary key (ID by name) 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):
<code>
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;
</code>
Almost forget to say that, just a reminder, the client only accept the SQL instead of stored procedure in database to implement it.

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:<code>
TCID level
Andrew top2
Jacob top2
Tyler top4</code>

This conversation is currently closed to new comments.

11 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Answers

Collapse -

sorry, I lost some comments

by bbaaking In reply to complicated SQL: How to f ...

sorry, here are some comments:
I only need query statement because customer did not allow us to write database. thank you every nice people.

Collapse -

Easy enough

by craigwilkinson In reply to complicated SQL: How to f ...

There's a couple of ways you could do this, but it involves thinking outside the SQL box; consider using the table multiple times to narrow down your results. I'd using the following to make a new table:

select s1.ServiceID, s1.ID, s1.TCID, s1.EndTime, s1.Qos
from service s1 inner join
(select s4.SID
from (select TCID, max(QOS) as mQOS
from service
group by ID) s3 inner join (select TCID, min(ID) as SID, QOS
from service
group by TCID, QOS) s4
on s3.ID = s4.SID
and s3.mQOS = s4.QOS) s2 on s1.ID = s2.SID

It's a three stage process; first, find all the records with the lowest QOS, and the relevant TCID, then attribute lowest ID that matches the QOS.

Hopefully I haven't misunderstood, and that helps you out of your bind - sorry, I haven't tested it out, but I can't see any errors.....

Collapse -

Reponse To Answer

by bbaaking In reply to Easy enough

Thank you for your reply, I have run your sql and got a error:Unknown column 's3.ID' in 'on clause'. So I think it must miss a ID, and I change it to: select s1.ServiceID, s1.ID, s1.TCID, s1.EndTime, s1.Qos
from service s1 inner join
(select s4.SID
from (select TCID,ID,max(QOS) as mQOS
from service
group by ID) s3 inner join (select TCID, min(ID) as SID, QOS
from service
group by TCID, QOS) s4
on s3.ID = s4.SID
and s3.mQOS = s4.QOS) s2 on s1.ID = s2.SID------------I got the result( not correct):
ServiceID ID TCID EndTime Qos
2000 2 Jacob 2011/1/1 2
2002 5 Jacob 2011/2/3 1
2003 6 Tyler 2011/1/4 1
2003 8 Tyler 2011/1/3 2
2009 14 Dylan 2011/2/8 1
2014 16 Andrew 2011/1/1 1
2028 34 Daniel 2011/1/6 1

what I want is like this:
ServiceID ID TCID EndTime Qos
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

Collapse -

Reponse To Answer

by bbaaking In reply to Easy enough

I really appreciate your work, and I need further

Collapse -

Reponse To Answer

by bbaaking In reply to Easy enough

I mean I need further direction. please help me.

Collapse -

Reponse To Answer

by bbaaking In reply to Easy enough

hi, I have got 2 answers(they are all correct):
1:SELECT MIN(Qos), serviceid, id, TCID, EndTime from service
GROUP BY serviceid
2:SELECT DISTINCT serviceid,tcid,endtime,qos FROM (SELECT * FROM service ORDER BY serviceid, qos, id) AS base GROUP BY serviceid .

And plz help me with aim 2-4 that should go with aim 1,I think.

Collapse -

Okay...

by craigwilkinson In reply to complicated SQL: How to f ...

Hi bbaaking,

Apologies for the error in my code... Have you managed to clear this up yet? If not, please let me know if I can be of any help.

Collapse -

Reponse To Answer

by bbaaking In reply to Okay...

thank you. I have got the answer of aim 1:SELECT MIN(Qos), serviceid, id, TCID, EndTime from service
GROUP BY serviceid
but I still introuble ,because I must reach the final goal ( I have divided it up 4 aims for It seemed too complicated), please help me with aim 2-4, and the aim 4 is my final goal

Collapse -

Finally, I find a solution but....

by bbaaking In reply to complicated SQL: How to f ...

Finally, I find a solution 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 is something about ESPROC: http://www.esproc.com/tour/what-is-esproc.html

here is the detail answer: http://bytes.com/topic/mysql/answers/939281-how-filtrate-duplicate-data-about-technical-support-compute-rank-order#post3715953

Hope this helps people that meet the same trouble.

Collapse -

Reponse To Answer

by bbaaking In reply to Finally, I find a solutio ...

I will take ESPROC as my final solution in the project at this friday if I can't make a MySQL statement out.
It seemed more easy to understand than MYSQL syntax even MSSQL, and It is in my ability.

Back to Desktop Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Hardware Forums