Getting Unique Values From a Resultset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Getting Unique Values From a Resultset

Author

Topic

ArunPhilip
Starting Member

21 Posts

Posted - 2012-02-17 : 14:10:58

Hi Everyone,

I am trying to find the total time spent by a person on a ticket. I was able to fetch the records with the time difference for each of the comment entered by the person, but the result that I get is having duplicates in it. I would like to know if there is a way that I can use to pull the unique values along with the total time spent by the person on the ticket?

SELECT icc.RequestID,icc.Submittedby,
DATEDIFF(day,(SELECT TOP 1 DateSubmitted FROM Comments 
WHERE RequestID = icc.RequestID AND DateSubmitted < icc.DateSubmitted ORDER BY DateSubmitted DESC),icc.DateSubmitted) AS TimeDiff
FROM Comments icc WHERE icc.RequestID IN (SELECT RequestID FROM Requests 
WHERE Status = 'Closed' and DateClosed between '2012-02-09' and '2012-02-11') and icc.Submittedby like '%a@b.com%'

The result that I am getting is
RequestID SubmittedBy TimeDiff
1 a 0
1 a 1
1 a 2
2 b 0
2 b 3
2 b 3
The result that I am looking for is
RequestID SubmittedBy TimeDiff
1 a 3
2 b 6

Is it okay to use Cursors to get the desired results? Or is there a better alternative to Cursors?

Thanks in advance,
Arun

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 14:17:26

its simple GROUP BY you want


SELECT icc.RequestID,icc.Submittedby,
SUM(DATEDIFF(day,(SELECT TOP 1 DateSubmitted FROM Comments 
WHERE RequestID = icc.RequestID AND DateSubmitted < icc.DateSubmitted ORDER BY DateSubmitted DESC),icc.DateSubmitted)) AS TimeDiff
FROM Comments icc WHERE icc.RequestID IN (SELECT RequestID FROM Requests 
WHERE Status = 'Closed' and DateClosed between '2012-02-09' and '2012-02-11') and icc.Submittedby like '%a@b.com%'
GROUP BY icc.RequestID,icc.Submittedby

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ArunPhilip
Starting Member

21 Posts

Posted - 2012-02-17 : 14:21:12

I had tried Group By but didnt work. Getting an error saying "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 14:22:39

[code]
SELECT RequestID,Submittedby,SUM(TimeDiff) AS TimeDiff
FROM
(
SELECT icc.RequestID,icc.Submittedby,
DATEDIFF(day,(SELECT TOP 1 DateSubmitted FROM Comments
WHERE RequestID = icc.RequestID AND DateSubmitted < icc.DateSubmitted ORDER BY DateSubmitted DESC),icc.DateSubmitted) AS TimeDiff
FROM Comments icc WHERE icc.RequestID IN (SELECT RequestID FROM Requests
WHERE Status = 'Closed' and DateClosed between '2012-02-09' and '2012-02-11') and icc.Submittedby like '%a@b.com%'
)t
GROUP BY RequestID,Submittedby
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ArunPhilip
Starting Member

21 Posts

Posted - 2012-02-17 : 14:31:55

That worked just perfect. Thanks Visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 14:33:28

welcome

that approach is called derived table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ArunPhilip
Starting Member

21 Posts

Posted - 2012-02-17 : 14:36:27

Thanks once again Visakh. Learnt a new concept :)

ArunPhilip
Starting Member

21 Posts

Posted - 2012-02-17 : 16:54:14

There is a mistake in the way i had explained the result that I getting.
After executing the query I am getting the result as,
1 a 0
1 a 1
1 a 2
2 b 0
2 b 3
2 b 3

here the prblem comes when add few more fields like Comments and DateSubmitted. The DATEDIFF needs to be performed only on the tickets that were worked upon by a(in the example). Right now the datediff is not been calculated for the time that only a has worked upon.
I hope I am not confusing anyone

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 16:56:53

you're. as per sample data posted the given suggestion should work. didnt understand the part
when add few more fields like Comments and DateSubmitted.

please elaborate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ArunPhilip
Starting Member

21 Posts

Posted - 2012-02-17 : 17:04:04

In the table Comments I have fields called Comments, DateSubmitted and SubmittedBy. When a ticket is entered to the Request table there will be an entry in the comments table too, but at this moment non of the analyst would hve picked this ticket up. The Ticket can be either be assigned by someone or can be self assigned. When ticket is assigned by someone in the comments the email id is entered and when it is self assigned the submittedby will have the email id. I need to calculate the date only after the ticket has been assigned(by someone or by Self).

I hope this helps.

ArunPhilip
Starting Member

21 Posts

Posted - 2012-02-21 : 12:23:52

Below is a sample data on how the data looks when i query the Comments table,
ID Datesubmitted RequestID SubmittedBy Comments
1 2012-02-03 13:26:35.283 230983 a@eco.com asdasdfasdf
2 2012-02-03 15:11:55.670 230983 b@eco.com asdfasdfasd
3 2012-02-06 12:01:02.197 230983 c@eco.com defasfdgasd
4 2012-02-06 15:52:19.430 230983 c@eco.com defasfdgasd
5 2012-02-07 15:48:25.127 230983 c@eco.com defasfdgasd
6 2012-02-10 16:02:16.450 230983 c@eco.com defasfdgasd

When I execute the above query for c@eco.com I am getting 7 days as the difference, but the actual differnce for c@eco should be 4 days. I am getting 7 days because the query is taking into account the DateSubimmited for a@eco.com and b@eco.com too. The date diff should only count the number of days for c@eco.com. Any suggestions are welcome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-21 : 14:45:45

sounds like what you need is this small addition


SELECT  RequestID,Submittedby,SUM(TimeDiff) AS TimeDiff
FROM
(
SELECT icc.RequestID,icc.Submittedby,
DATEDIFF(day,(SELECT TOP 1 DateSubmitted FROM Comments 
WHERE RequestID = icc.RequestID AND DateSubmitted < icc.DateSubmitted AND SubmittedBy =icc.SubmittedBy  ORDER BY DateSubmitted DESC),icc.DateSubmitted) AS TimeDiff
FROM Comments icc WHERE icc.RequestID IN (SELECT RequestID FROM Requests 
WHERE Status = 'Closed' and DateClosed between '2012-02-09' and '2012-02-11') and icc.Submittedby like '%a@b.com%'
)t
GROUP BY RequestID,Submittedby

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ArunPhilip
Starting Member

21 Posts

Posted - 2012-02-22 : 10:58:15

Thanks Visakh....That just works perfectly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources