Question

  • Creator
    Topic
  • #2137742

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

    Locked

    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
    SELECT MIN(Qos), serviceid, id, TCID, EndTime from service
    GROUP BY serviceid

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

    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.

    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

All Answers

  • Author
    Replies
    • #2884507

      Clarifications

      by bbaaking ·

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

      Clarifications

    • #2884503

      sorry, I lost some comments

      by bbaaking ·

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

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

    • #2884482

      Easy enough

      by craigwilkinson ·

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

      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…..

      • #2884473

        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

      • #2884472

        Reponse To Answer

        by bbaaking ·

        In reply to Easy enough

        I really appreciate your work, and I need further

      • #2884471

        Reponse To Answer

        by bbaaking ·

        In reply to Easy enough

        I mean I need further direction. please help me.

      • #2884466

        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.

    • #2884464

      Okay…

      by craigwilkinson ·

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

      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.

      • #2884436

        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

    • #2884317

      Finally, I find a solution but….

      by bbaaking ·

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

      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.

      • #2884966

        Reponse To Answer

        by bbaaking ·

        In reply to Finally, I find a solution but….

        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.

Viewing 4 reply threads