complicated SQL: How to filter duplicate data about technical support and compute rank
-
I’ve got a big problem. 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. And I have got answer of this aim here (thanks for TenmanS14:thumbsup:):
SELECT MIN(Qos), serviceid, id, TCID, EndTime from service
GROUP BY serviceidaim2: 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 Jacobexplain:There are some technical service data (ServiceID, TCID, EndTime, and QoS) in a whole year, and the 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 1Data 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` D -
I’ve got a big problem. 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. And I have got answer of this aim here (thanks for TenmanS14:thumbsup:):
SELECT MIN(Qos), serviceid, id, TCID, EndTime from service
GROUP BY serviceidaim2: 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 Jacobexplain:There are some technical service data (ServiceID, TCID, EndTime, and QoS) in a whole year, and the 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 1Data 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` Dnot sure about looping in MySQL, believe its tricky, so I'd take care of that in by building a recordset in your program code, first your gonna need each distinct ServiceID... SELECT DISTINCT serviceid from service put the results of that into an array.. then your gonna have to loop through each one with something like... SELECT * FROM `service` WHERE ID IN ( SELECT id FROM service WHERE serviceid = frompreviousstatement ) ORDER BY Qos ASC LIMIT 0 , 1 and put each result from that into another array...
-
not sure about looping in MySQL, believe its tricky, so I'd take care of that in by building a recordset in your program code, first your gonna need each distinct ServiceID... SELECT DISTINCT serviceid from service put the results of that into an array.. then your gonna have to loop through each one with something like... SELECT * FROM `service` WHERE ID IN ( SELECT id FROM service WHERE serviceid = frompreviousstatement ) ORDER BY Qos ASC LIMIT 0 , 1 and put each result from that into another array...
thank you for your reply, but I need a resultset object, It's hard to write it step by step like your solution. I mean I need a single query statement my result of goal 1 should be:
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 -
thank you for your reply, but I need a resultset object, It's hard to write it step by step like your solution. I mean I need a single query statement my result of goal 1 should be:
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 -
SELECT MIN(Qos), serviceid, id, TCID, EndTime from service GROUP BY serviceid maybe this then...
Thank you. It worked just like what I want. I cant believe it because your answer is more simple than another answher that I have got few hours ago:
SELECT DISTINCT serviceid,tcid,endtime,qos FROM (SELECT * FROM service ORDER BY serviceid, qos, id) AS base GROUP BY serviceid
thank you again genius, and plz help me with aim2-4.
-
I’ve got a big problem. 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. And I have got answer of this aim here (thanks for TenmanS14:thumbsup:):
SELECT MIN(Qos), serviceid, id, TCID, EndTime from service
GROUP BY serviceidaim2: 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 Jacobexplain:There are some technical service data (ServiceID, TCID, EndTime, and QoS) in a whole year, and the 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 1Data 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` DHave a look at this query:
WITH id AS (
SELECT serviceid,tcid,Min(EndTime) endtime,Min(Qos) qos
FROM service
GROUP BY serviceid,tcid
)
SELECT s.ServiceID,s.ID,s.TCID,s.EndTime,s.Qos
FROM service s,id
WHERE s.serviceid = id.serviceid
AND s.tcid = id.tcid
AND s.endtime = id.endtime
AND s.qos = id.qosLight moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
Have a look at this query:
WITH id AS (
SELECT serviceid,tcid,Min(EndTime) endtime,Min(Qos) qos
FROM service
GROUP BY serviceid,tcid
)
SELECT s.ServiceID,s.ID,s.TCID,s.EndTime,s.Qos
FROM service s,id
WHERE s.serviceid = id.serviceid
AND s.tcid = id.tcid
AND s.endtime = id.endtime
AND s.qos = id.qosLight moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
hi,Jörgen Andersson thank you for your help. the statement you posted seemed like a sqlsvr statement, I ran it on mysql and got a error:You have an error in your SQL syntax near "with.....". I should run it on mysql. in any case, I have got the correct answer of aim 1( I have write it in the top/first post), and plz help me with aim 2-4( It's still in trouble).
-
I’ve got a big problem. 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. And I have got answer of this aim here (thanks for TenmanS14:thumbsup:):
SELECT MIN(Qos), serviceid, id, TCID, EndTime from service
GROUP BY serviceidaim2: 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 Jacobexplain:There are some technical service data (ServiceID, TCID, EndTime, and QoS) in a whole year, and the 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 1Data 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` DSELECT * FROM (SELECT COUNT(TCID) as C, TCID FROM service WHERE Qos = 1 and MONTH(EndTime) = 1 GROUP BY TCID) AS T ORDER BY C DESC This'll rank em for a month, I'll leave you to learn how to increment that month value to loop through for every one... SELECT * FROM (SELECT COUNT(TCID) as count, TCID, Month FROM (SELECT month(endtime) as Month, tcid, serviceid FROM service WHERE qos = 1 group by month(endTime),tcid, serviceid) AS T GROUP BY month, tcid) AS S order by month, count DESC alternatly, this'll give you all of the months in the statement, I'll leave you to actually learn some SQL to refine it to get exactly what you want...
-
SELECT * FROM (SELECT COUNT(TCID) as C, TCID FROM service WHERE Qos = 1 and MONTH(EndTime) = 1 GROUP BY TCID) AS T ORDER BY C DESC This'll rank em for a month, I'll leave you to learn how to increment that month value to loop through for every one... SELECT * FROM (SELECT COUNT(TCID) as count, TCID, Month FROM (SELECT month(endtime) as Month, tcid, serviceid FROM service WHERE qos = 1 group by month(endTime),tcid, serviceid) AS T GROUP BY month, tcid) AS S order by month, count DESC alternatly, this'll give you all of the months in the statement, I'll leave you to actually learn some SQL to refine it to get exactly what you want...
Thanks a lot! I add filter statement( aim 1, without it the result will not be correct),like:
SELECT * FROM (SELECT COUNT(TCID) as count, TCID, Month FROM (
SELECT month(endtime) as Month, tcid, serviceid
FROM (SELECT MIN(Qos) qos, serviceid, id, TCID, EndTime from service
GROUP BY serviceid
)T1
WHERE qos = 1
group by month(endTime),tcid, serviceid
)As T
GROUP BY month, tcid) AS S
order by month, count DESCand It got correct data:
count TCID Month
4 Andrew 1
3 Jacob 1
2 Tyler 1
2 Dylan 1
1 Daniel 1
4 Dylan 2
4 Jacob 2
4 Andrew 2
2 Tyler 2
1 Daniel 2I think It is half of aim 2, thank you. please go on ,I think Top-2-in-January(rank) is Andrew/Jacob, Top-2-in-February(rank) is Andrew/Dylan/Jacob . So the result of aim2 should be 2 reocrders: Andrew/Jacob
-
I’ve got a big problem. 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. And I have got answer of this aim here (thanks for TenmanS14:thumbsup:):
SELECT MIN(Qos), serviceid, id, TCID, EndTime from service
GROUP BY serviceidaim2: 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 Jacobexplain:There are some technical service data (ServiceID, TCID, EndTime, and QoS) in a whole year, and the 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 1Data 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` DI can not find a SQL solution to solve this problem, but finally, I found a esproc solution. It is not a traditional SQL, but It fit for me, because ESPROC can return a resultSet to my Jasper reporting tool. Anyway, I still need a SQL solution, can some body help me? ESPROC's solution is just like this: http://stackoverflow.com/questions/10184205/complicated-sql-how-to-filtrate-duplicate-data-about-technical-support-and-comp/10275434#10275434[^] or check here: http://i.stack.imgur.com/p5rp0.jpg[^] 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/Forums/en-US/transactsql/thread/e11c754d-559e-4eb7-9682-e998ea3387d9[^] Hope this helps people that meet the same trouble
