Database

18-Apr-12 21:08

I have a requirement as in below

SQL

DECLARE @TEST TABLE (X VARCHAR(10),Y VARCHAR(10))

    insert into @TEST values ('A','B'),('C','D'),('E','F'),('B','A'),('D','C'),('F','E')

SELECT A.* FROM @TEST A, @TEST B WHERE A.X = B.y

gives
X Y

A B
C D
E F
B A
D C
F E

But I have to get only the combination (AB or BA) ,(CD or DC),(EF or FE) in my select statement. In my sql we have some thing called DistinctRow.. What is it in SQL ..Pelase help ..urgent..

AHSAN11118-Apr-12 21:31

AHSAN111

18-Apr-12 21:31

Try using the Distinct Keyword

SQL

SELECT Distinct(A.x), A.Y FROM @TEST A, @TEST B WHERE A.X = B.y

Blue_Boy19-Apr-12 0:57

Blue_Boy

19-Apr-12 0:57

here it is

SQL

DECLARE @TEST TABLE (X VARCHAR(10),Y VARCHAR(10))
 
insert into @TEST values ('A','B')
insert into @TEST values ('C','D')
insert into @TEST values ('E','F')
insert into @TEST values ('B','A')
insert into @TEST values ('D','C')
insert into @TEST values ('F','E')


SELECT A.X
,(
select  top 1 b.y from @test as b where b.y > a.x  and b.x<a.y
) as subY

 FROM @TEST A
where 
(
select  top 1 b.y from @test as b where b.y > a.x  and b.x<a.y
) is not null
order by a.x

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

Lima319-Apr-12 1:53

19-Apr-12 1:53

Excellent .. thanks for the reply..

Lima319-Apr-12 1:56

19-Apr-12 1:56

I was looking for some key word like distinctrow in MYSQL.. dont we have any thing like that in SQL.

Lima319-Apr-12 2:38

19-Apr-12 2:38

Finally SELECT X,Y FROM @TEST WHERE X

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

Blue_Boy19-Apr-12 2:45

Blue_Boy

19-Apr-12 2:45

There exists DISTINCT option to retrieve non-repeated values, but your case was different from using DISTINCT.

Glad to help you.

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

Traking Offence

Billa218-Apr-12 20:06

Billa2

18-Apr-12 20:06

I have a table called tbloffence where we can see list of offence which workers have done in worksite.so now i want to display the sumtotal of their offence in .aspx page based on workes userid,but the condition is like this:
a) offence since last login:
b)offence in last 1 month.

i want to write a sql query based on this condition...how can i track"offence since last login"in sql server 2005

Re: Traking Offence

Mohibur Rashid18-Apr-12 20:45

Mohibur Rashid

18-Apr-12 20:45

This is not an specific question. To get better answer, I would suggest you to generalize the question and provide more information.

Anyway
This is a very simple problem if I did understand properly.

All you need is to record log in time. that is everytime user log in the user.

And you would have to work in two different Query or may be in Procedeure, where you would send the user_id and procedure will return the result.

Re: Traking Offence

Billa218-Apr-12 22:09

Billa2

18-Apr-12 22:09

Actually i want to workin 3 tables

1)tbloffence : where we will get the sum(offence) here i track maincontractorID and sum(offence) query will be like this :
"SELECT sum(o.OffenceID) as OffenceNumber,u.UserID

from tblOffence o
inner join tblSubContractor ts on
o.MainContractorID=ts.SubContractorID"
from here i will chk the tblUSER with these subcontractorID :
"inner join tblUser u on
u.SubContractorID=ts.SubContractorID" .

and my doubt is that upto here i will get the total sum of offence and appropriate userid.,for eg
UserID : 520 TotalOffence:6000
but there is another table called tblAuditTrailSession where we will get logondate and userid which is tracking here..so i want to display offence based on userlogin(userid) ..since last login. i want a help in query format..Hope this is a generalized format question.

modified 19-Apr-12 4:26am.

bbaaking16-Apr-12 16:54

Re: How to filter duplicate data about technical support and compute rank

16-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 TenmanS14 Thumbs 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.

TenmanS1417-Apr-12 11:00

TenmanS14

17-Apr-12 11:00

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

Re: How to filter duplicate data about technical support and compute rank

bbaaking17-Apr-12 16:41

Re: How to filter duplicate data about technical support and compute rank

17-Apr-12 16:41

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:

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

TenmanS1417-Apr-12 21:59

TenmanS14

17-Apr-12 21:59

SELECT MIN(Qos), serviceid, id, TCID, EndTime from service
GROUP BY serviceid

maybe this then...

Re: How to filter duplicate data about technical support and compute rank

bbaaking17-Apr-12 22:20

Re: How to filter duplicate data about technical support and compute rank

17-Apr-12 22:20

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:

SQL

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.

Jörgen Andersson17-Apr-12 22:37

Jörgen Andersson

17-Apr-12 22:37

Have a look at this query:

SQL

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

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Re: How to filter duplicate data about technical support and compute rank

bbaaking17-Apr-12 22:54

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

17-Apr-12 22:54

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

TenmanS1418-Apr-12 11:40

TenmanS14

18-Apr-12 11:40

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

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

bbaaking18-Apr-12 21:56

I have solved it by myself

18-Apr-12 21:56

Thanks a lot!
I add filter statement( aim 1, without it the result will not be correct),like:

SQL

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 DESC

and It got correct data:

SQL

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	2

I 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

modified 19-Apr-12 4:11am.

bbaaking22-Apr-12 20:22