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
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
16 2344
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.
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
-
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.
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?
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.
thank you Rabbit, but how to write it, plz help me
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)
Like I said earlier, we restrict questions to one per thread. If you have additional questions, please post each in a new thread.
thank you Rabbit, I will do as what u have said
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.
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.
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: shaun.mostashari |
last post by:
Hello all, I am working on a trade study and one of the criterias is
how easy it is to get DB2 technical support from IBM. I appreciate if
you guys share your experience with me. Does DB2 have a...
|
by: Charles |
last post by:
Hi MS Access & MS SQL Server Gurus !
I am trying to delete duplicate data from a LINKED SQL Server 2000
table (LinkedTable) and insert the result into a local (in Access)
table.
The following...
|
by: craig.brenizer |
last post by:
I have a table of data that has duplicate values in the pagenumber
field.
How can I combine the data so that the page numbers of duplicate part
numbers are on one record for that part number?
...
|
by: tom |
last post by:
Hi,
I have a datagrid which display the data like this:
bookName Author
------------------
book1 Bob
book1 Nancy
book2 Tim
------------------
|
by: Mike Charney |
last post by:
I have a temp table that has data I am inserting into a SQL DB table.
There is a column in both tables called PAT_ID. I need to check the SQL
table against the temp table for duplicate data in...
|
by: anjanareddy |
last post by:
Hai,
i am unable to inserting duplicate data into UNIQUE index column,
Is there any other process, to insert duplicate data into UNIQUE index column?
plz let me know..
Thanks & Regards...
|
by: oaklander |
last post by:
I would like to make sure there are no duplicate data entries in my Oracle 9i table (called MainTable) which has an Id field that is the primary key, ValData with a varchar data type, Fid and Fid2...
|
by: killswitch101 |
last post by:
I need to get all of the non-duplicate data out of 2 tables (ci and hi)
i can't seem to work it out
i think i need to use the minus command but that will only display the data for 1 table
can...
|
by: AccessBeetle |
last post by:
I am working with SQL server 2005 backend and Access 2003 frond end.
I have a form bound to a table tblReportDetails. There are several(5) tabs on the same form. Each tab has its own subform. Every...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |