Click here to Skip to main content
15,867,568 members
Home / Discussions / Database
   

Database

 
GeneralRe: Getting unique combination of rows in SQL?? Pin
Lima319-Apr-12 1:53
Lima319-Apr-12 1:53 
GeneralRe: Getting unique combination of rows in SQL?? Pin
Lima319-Apr-12 1:56
Lima319-Apr-12 1:56 
GeneralRe: Getting unique combination of rows in SQL?? Pin
Lima319-Apr-12 2:38
Lima319-Apr-12 2:38 
GeneralRe: Getting unique combination of rows in SQL?? Pin
Blue_Boy19-Apr-12 2:45
Blue_Boy19-Apr-12 2:45 
QuestionTraking Offence Pin
Billa218-Apr-12 20:06
Billa218-Apr-12 20:06 
AnswerRe: Traking Offence Pin
Mohibur Rashid18-Apr-12 20:45
professionalMohibur Rashid18-Apr-12 20:45 
GeneralRe: Traking Offence Pin
Billa218-Apr-12 22:09
Billa218-Apr-12 22:09 
Questioncomplicated SQL: How to filter duplicate data about technical support and compute rank Pin
bbaaking16-Apr-12 16:54
bbaaking16-Apr-12 16:54 
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 TenmanS14Thumbs Up | :thumbsup: ):
SQL
SELECT MIN(Qos), serviceid, id, TCID, EndTime from service
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
SQL
TCID
Andrew
Jacob




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


modified 18-Apr-12 5:05am.

AnswerRe: How to filter duplicate data about technical support and compute rank Pin
TenmanS1417-Apr-12 11:00
TenmanS1417-Apr-12 11:00 
GeneralRe: How to filter duplicate data about technical support and compute rank Pin
bbaaking17-Apr-12 16:41
bbaaking17-Apr-12 16:41 
GeneralRe: How to filter duplicate data about technical support and compute rank Pin
TenmanS1417-Apr-12 21:59
TenmanS1417-Apr-12 21:59 
GeneralRe: How to filter duplicate data about technical support and compute rank Pin
bbaaking17-Apr-12 22:20
bbaaking17-Apr-12 22:20 
AnswerRe: How to filter duplicate data about technical support and compute rank Pin
Jörgen Andersson17-Apr-12 22:37
professionalJörgen Andersson17-Apr-12 22:37 
GeneralRe: How to filter duplicate data about technical support and compute rank Pin
bbaaking17-Apr-12 22:54
bbaaking17-Apr-12 22:54 
AnswerRe: complicated SQL: How to filter duplicate data about technical support and compute rank Pin
TenmanS1418-Apr-12 11:40
TenmanS1418-Apr-12 11:40 
GeneralRe: complicated SQL: How to filter duplicate data about technical support and compute rank Pin
bbaaking18-Apr-12 21:56
bbaaking18-Apr-12 21:56 
AnswerI have solved it by myself Pin
bbaaking22-Apr-12 20:22
bbaaking22-Apr-12 20:22 
SuggestionSync data Bi-Directional Automatically- Sql Server 2008 Pin
dsrao16-Apr-12 11:49
dsrao16-Apr-12 11:49 
GeneralRe: Sync data Bi-Directional Automatically- Sql Server 2008 Pin
kalaisw17-Apr-12 2:06
kalaisw17-Apr-12 2:06 
GeneralRe: Sync data Bi-Directional Automatically- Sql Server 2008 Pin
dsrao17-Apr-12 6:48
dsrao17-Apr-12 6:48 
GeneralRe: Sync data Bi-Directional Automatically- Sql Server 2008 Pin
TenmanS1419-Apr-12 4:44
TenmanS1419-Apr-12 4:44 
GeneralRe: Sync data Bi-Directional Automatically- Sql Server 2008 Pin
JosephvObrien19-Apr-12 22:14
JosephvObrien19-Apr-12 22:14 
QuestionT-SQL Developer needed for hire. Pin
SQL Ed15-Apr-12 5:13
SQL Ed15-Apr-12 5:13 
AnswerRe: T-SQL Developer needed for hire. Pin
Mycroft Holmes15-Apr-12 13:54
professionalMycroft Holmes15-Apr-12 13:54 
GeneralRe: T-SQL Developer needed for hire. Pin
SQL Ed16-Apr-12 0:32
SQL Ed16-Apr-12 0:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.