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
 General SQL Server Forums
 New to SQL Server Programming
 Max

Author  Topic 

thehandsomecode
Yak Posting Veteran

51 Posts
Posted - 2007-11-13 : 21:09:03
how can get this code to return this
regardinobjectidname| owneridname | activitytypecodename | lastcontact
bob dealan melvin charles phonecall 10\10\07
forbes felix melvin charles email 15\10\07

as apposed to this
regardinobjectidname| owneridname | activitytypecodename | lastcontact
bob dealan melvin charles phonecall 10\10\07
bob dealan melvin charles email 15\10\07

I need it to only return the most recent contact date for eas regardingobjectidname. currently, it outputs the most recent email and the most recent phonecall for each. i only need the most recent not the most recent for eas activitytype

SELECT FilteredActivityPointer.regardingobjectidname, FilteredActivityPointer.owneridname, FilteredActivityPointer.activitytypecodename
FROM FilteredActivityPointer CROSS JOIN
(SELECT MAX(actualend) AS [Last Contact Date], owneridname, regardingobjectidname, activitytypecodename
FROM FilteredActivityPointer AS FilteredActivityPointer_1
GROUP BY owneridname, regardingobjectidname, activitytypecodename, actualend) AS maxresults
WHERE (FilteredActivityPointer.statecodename = 'completed') AND (FilteredActivityPointer.activitytypecodename IN ('phone call', 'e-mail')) AND
(FilteredActivityPointer.owneridname IN ('stephanie frazier'))
GROUP BY FilteredActivityPointer.regardingobjectidname, FilteredActivityPointer.activitytypecodename, FilteredActivityPointer.owneridname


Please help




Compnetsyslc

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2007-11-13 : 21:35:31
Not sure without adequate test data..


SELECT a.regardingobjectidname, a_1.owneridname, a.activitytypecodename,a.actualend
FROM FilteredActivityPointer a INNER JOIN
	    			(SELECT MAX(actualend) AS [Last Contact Date],owneridname
				FROM FilteredActivityPointer 
				GROUP BY owneridname
				 ) AS a_1
on a.owneridname = a_1.owneridname
WHERE (a.statecodename = 'completed') 
AND (a.activitytypecodename IN ('phone call', 'e-mail')) AND
(a.owneridname IN ('stephanie frazier'))


On the subquery, you just need the max date and ownerid, don't group by the actualend date field if you want the max.. this way you grab the max date, and that date's owner. Return the ownerid name from the subquery and everything else from the main table. You won't need the group by at the end if the subquery is the only aggregating subquery.






Poor planning on your part does not constitute an emergency on my part.
Go to Top of Page

thehandsomecode
Yak Posting Veteran

51 Posts
Posted - 2007-11-13 : 21:52:18
It still didnt work. this is the query that gives me the closet result

SELECT regardingobjectidname, MAX(actualend) AS Last_Contacted_On, activitytypecodename, owneridname
FROM FilteredActivityPointer AS A
WHERE (statecodename = 'completed') AND (activitytypecodename IN ('phone call', 'e-mail')) AND (owneridname IN ('stephanie frazier'))
GROUP BY regardingobjectidname, activitytypecodename, owneridname
ORDER BY regardingobjectidname

the one you send returns multiple records for each regardingowneridname

Compnetsyslc
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2007-11-13 : 22:04:28
Explain how you got this:

regardinobjectidname| owneridname | activitytypecodename | lastcontact
bob dealan melvin charles phonecall 10\10\07
bob dealan melvin charles email 15\10\07


with this criteria:

AND (owneridname IN ('stephanie frazier'))


in your posted sample.


In the meantime, give me a couple rows of data to test with, and your desired result. Showing a query that doesn't produce the desired results, without sample data or valid desired results makes it more difficult.

"regardingowneridname" wasn't even a field in any of the queries or your sample data...so how would I know that multiple records would be returned?

quote:

I need it to only return the most recent contact date for eas regardingobjectidname. currently, it outputs the most recent email and the most recent phonecall for each. i only need the most recent not the most recent for eas activitytype



That, I misread before..so..

SELECT a.regardingobjectidname, a_1.owneridname, a.activitytypecodename,a_1.[Last Contact Date]
FROM FilteredActivityPointer a INNER JOIN
	    			(SELECT MAX(actualend) AS [Last Contact Date],regardingobjectidname
				FROM FilteredActivityPointer 
				GROUP BY owneridname
				 ) AS a_1
on  a.regardingobjectidname = a_1.regardingobjectidname and a.actualend = a_1.[Last Contact Date]
WHERE (a.statecodename = 'completed') 
AND (a.activitytypecodename IN ('phone call', 'e-mail')) AND
(a.owneridname IN ('stephanie frazier'))


Might work better.

In english that says to "get the max date per regardingobjectidname" and return joining records where the regardingobjectidname match and the actual end date matches the max(actualend) date for that regardingobjectidname.


Poor planning on your part does not constitute an emergency on my part.
Go to Top of Page

thehandsomecode
Yak Posting Veteran

51 Posts
Posted - 2007-11-13 : 22:46:41
Abel Vanhusan 11/11/2007 2:09:06 PM E-mail Stephanie Frazier
Abel Vanhusan 11/11/2007 1:52:23 PM Phone Call Stephanie Frazier
Adewole Osinubi 16/10/2007 1:33:41 AM E-mail Stephanie Frazier
Adewole Osinubi 13/11/2007 11:29:50 PM Phone Call Stephanie Frazier
Adly Thebaud 11/11/2007 2:16:18 PM E-mail Stephanie Frazier
Adly Thebaud 11/11/2007 2:17:08 PM Phone Call Stephanie Frazier
Adnan Zaman 22/10/2007 7:34:29 PM E-mail Stephanie Frazier
Adnan Zaman 08/11/2007 2:36:52 PM Phone Call Stephanie Frazier
Akinlolu Ojo 02/11/2007 7:03:06 PM E-mail Stephanie Frazier
Akinlolu Ojo 13/11/2007 11:30:45 PM Phone Call Stephanie Frazier
Al Fosha 28/08/2007 4:28:12 PM E-mail Stephanie Frazier
Alan Green 13/11/2007 8:32:49 PM E-mail Stephanie Frazier
Alan Green 13/11/2007 8:23:59 PM Phone Call Stephanie Frazier
Alan Moody 28/08/2007 4:28:12 PM E-mail Stephanie Frazier
Alan Segaloff 21/08/2007 7:50:13 PM E-mail Stephanie Frazier
Aleks Pausak 22/10/2007 8:01:47 PM E-mail Stephanie Frazier
Aleks Pausak 22/10/2007 7:59:00 PM Phone Call Stephanie Frazier
Alex Lubertozzi 03/07/2007 1:57:09 PM Phone Call Stephanie Frazi
Alex Trebeck 11/11/2007 2:23:20 PM E-mail Stephanie Frazi
Alex Trebeck 11/11/2007 2:19:12 PM Phone Call Stephanie Fraz
Alexander Abr 24/08/2007 1:52:01 PM E-mail Stephanie Frazi
Alexander Abr 08/10/2007 12:46:48 PM Phone CallStephanie Frazi
Alexander kierce 01/11/2007 7:48:01 PM E-mail Stephanie Frazier
Alexander Bie 01/11/2007 7:42:46 PM Phone Call Stephanie Fraz

sorry the code i gave you was just an example output here is a real one base on the 'stephanie fraz'

it returns two records when all i need is one. i will try out the code you just sent and see how it works. thank you for your help.

Compnetsyslc
Go to Top of Page

thehandsomecode
Yak Posting Veteran

51 Posts
Posted - 2007-11-13 : 22:59:01
The query you just sent timesout whiler tryig to retrieve the results.

Compnetsyslc
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2007-11-13 : 23:08:17
[code]
Adewole Osinubi Adewole Osinubi Phone Call 2007-11-13 23:29:50.000
Adly Thebaud Adly Thebaud Phone Call 2007-11-11 14:17:08.000
Adnan Zaman Adnan Zaman Phone Call 2007-11-08 14:36:52.000
Akinlolu Ojo Akinlolu Ojo Phone Call 2007-11-13 23:30:45.000
Alex Lubertozzi Alex Lubertozzi Phone Call 2007-07-03 13:57:09.000
Alex Trebeck Alex Trebeck E-mail 2007-11-11 14:23:20.000
Alexander Abr Alexander Abr Phone Call 2007-10-08 12:46:48.000
Alexander Bie Alexander Bie Phone Call 2007-11-01 19:42:46.000
[/code]

results from:
[code]
Create Table #filteredactivitypointer(
regardingobjectidname varchar(35),
actualend datetime not null,
activitytypecodename varchar(15) not null,
owneridname varchar(25) not null,
statecodename varchar(10) not null)

Insert Into #filteredactivitypointer (
regardingobjectidname, actualend, activitytypecodename, owneridname,statecodename)
Select 'Abel Vanhusan', '11/11/2007 2:09:06 PM', 'Email', 'Stephanie Frazier','completed' UNION ALL
Select 'Abel Vanhusan', '11/11/2007 1:52:23 PM', 'Phone Call', 'Stephanie Frazier','completed' UNION ALL
Select 'Adewole Osinubi', '10/16/2007 1:33:41 AM', 'Email', 'Stephanie Frazier','completed' UNION ALL
Select 'Adewole Osinubi', '11/13/2007 11:29:50 PM', 'Phone Call', 'Stephanie Frazier','completed' UNION ALL
Select 'Adly Thebaud', '11/11/2007 2:16:18 PM', 'Email', 'Stephanie Frazier','completed' UNION ALL
Select 'Adly Thebaud', '11/11/2007 2:17:08 PM', 'Phone Call', 'Stephanie Frazier','completed' UNION ALL
Select 'Adnan Zaman', '10/22/2007 7:34:29 PM', 'Email', 'Stephanie Frazier','completed' UNION ALL
Select 'Adnan Zaman', '11/08/2007 2:36:52 PM', 'Phone Call', 'Stephanie Frazier','completed' UNION ALL
Select 'Akinlolu Ojo', '11/02/2007 7:03:06 PM', 'Email', 'Stephanie Frazier','completed' UNION ALL
Select 'Akinlolu Ojo', '11/13/2007 11:30:45 PM', 'Phone Call', 'Stephanie Frazier','completed' UNION ALL
Select 'Al Fosha', '08/28/2007 4:28:12 PM', 'Email', 'Stephanie Frazier','completed' UNION ALL
Select 'Alan Green', '11/13/2007 8:32:49 PM', 'Email', 'Stephanie Frazier','completed' UNION ALL
Select 'Alan Green', '11/13/2007 8:23:59 PM', 'Phone Call', 'Stephanie Frazier','completed' UNION ALL
Select 'Alan Moody', '08/28/2007 4:28:12 PM', 'Email', 'Stephanie Frazier','completed' UNION ALL
Select 'Alan Segaloff', '08/21/2007 7:50:13 PM', 'Email', 'Stephanie Frazier','completed' UNION ALL
Select 'Aleks Pausak', '10/22/2007 8:01:47 PM', 'Email', 'Stephanie Frazier','completed' UNION ALL
Select 'Aleks Pausak', '10/22/2007 7:59:00 PM', 'Phone Call', 'Stephanie Frazier','completed' UNION ALL
Select 'Alex Lubertozzi', '07/03/2007 1:57:09 PM', 'Phone Call', 'Stephanie Frazier','completed' UNION ALL
Select 'Alex Trebeck', '11/11/2007 2:23:20 PM', 'E-mail', 'Stephanie Frazier','completed' UNION ALL
Select 'Alex Trebeck', '11/11/2007 2:19:12 PM', 'Phone Call', 'Stephanie Frazier','completed' UNION ALL
Select 'Alexander Abr', '08/24/2007 1:52:01 PM', 'E-mail', 'Stephanie Frazier','completed' UNION ALL
Select 'Alexander Abr', '10/08/2007 12:46:48 PM', 'Phone Call','Stephanie Frazier','completed' UNION ALL
Select 'Alexander kierce', '11/01/2007 7:48:01 PM', 'Email', 'Stephanie Frazier','completed' UNION ALL
Select 'Alexander Bie', '11/01/2007 7:42:46 PM', 'Phone Call', 'Stephanie Frazier','completed'

SELECT a.regardingobjectidname, a_1.regardingobjectidname, a.activitytypecodename,a_1.[Last Contact Date]
FROM #FilteredActivityPointer a INNER JOIN
(SELECT MAX(actualend) AS [Last Contact Date],regardingobjectidname
FROM #FilteredActivityPointer
GROUP BY regardingobjectidname
) AS a_1
on a.regardingobjectidname = a_1.regardingobjectidname and a.actualend = a_1.[Last Contact Date]
WHERE (a.statecodename = 'completed')
AND (a.activitytypecodename IN ('phone call', 'e-mail')) AND
(a.owneridname IN ('stephanie frazier'))
order by a.regardingobjectidname

drop table #filteredactivitypointer
[/code]


My last query had a few issues (too little coffee). see bolded query above for syntax.


Poor planning on your part does not constitute an emergency on my part.
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2007-11-13 : 23:09:37
quote:
Originally posted by thehandsomecode

The query you just sent timesout whiler tryig to retrieve the results.

Compnetsyslc


Yah..sorry 'bout that. My last post at same time has results you need I believe. Helped having data to use..


Poor planning on your part does not constitute an emergency on my part.
Go to Top of Page

thehandsomecode
Yak Posting Veteran

51 Posts
Posted - 2007-11-13 : 23:30:23
I get an error invalid object name #filteredactivitypointer

Compnetsyslc
Go to Top of Page

thehandsomecode
Yak Posting Veteran

51 Posts
Posted - 2007-11-13 : 23:36:32
Sorry my Bad i took of the # But it sttill times out.

Compnetsyslc
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2007-11-13 : 23:39:51
I can't help if it times out, based on what you have posted, were my results what you would have expected?

How big is the table you are running this against? It shouldn't time out, that is not the query's fault. Check your timeout settings...



Poor planning on your part does not constitute an emergency on my part.
Go to Top of Page

thehandsomecode
Yak Posting Veteran

51 Posts
Posted - 2007-11-14 : 11:31:22
Okat i got that to work. it was the e-mail attribute messing up the whole thing.
how do i now join all of this with another table called
filteredcontact to pull out data like new_rating

Compnetsyslc
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2007-11-14 : 11:45:01
Do another inner join on the table using the linking field..


Poor planning on your part does not constitute an emergency on my part.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site