Most Recent Record - SQL Server Forums

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

Most Recent Record

Author

Topic

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-11-13 : 12:28:41

Please concider the line of code below. it doesnt quite work how i want it to. i need it to only pull records where there isnt any activity completed (actualend) after a given period. and only show the most recent activity per regardingobjecid(sales person) however it returns all the records before that period and shows me the most recent one up to that time. Please help.

here is the code

SELECT new_ratingname, regardingobjectidname, actualend, owneridname, createdbyname, activitytypecodename, count(*) AS Total
FROM (SELECT filteredcontact.new_ratingname, filteredactivitypointer.regardingobjectidname, filteredactivitypointer.actualend,
filteredactivitypointer.Owneridname, filteredactivitypointer.createdbyname, filteredactivitypointer.activitytypecodename, row_number()
OVER (Partition BY regardingobjectid
ORDER BY actualend DESC) AS recid
FROM FilteredActivityPointer INNER JOIN
FilteredContact ON FilteredContact.contactid = FilteredActivityPointer.regardingobjectid
WHERE new_ratingname NOT IN ('dead', 'archive', 'continous updates') AND filteredactivitypointer.statecodename = 'completed' AND
filteredactivitypointer.owneridname IN (@user) AND filteredactivitypointer.createdbyname NOT IN ('melvin felicien', 'suzette collymore', 'gasper ') AND
filteredactivitypointer.activitytypecodename IN ('phone call', 'e-mail', 'fax') AND filteredactivitypointer.actualend <= dateadd(d,
- CAST(@NeglectedDays AS INT), GetUTCDate())) AS d
WHERE recid = 1
GROUP BY d .actualend, d .regardingobjectidname, d .owneridname, d .createdbyname, d .activitytypecodename, new_ratingname
ORDER BY d .actualend

Compnetsyslc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 13:31:05

[code]SELECT new_ratingname,
regardingobjectidname,
actualend,
owneridname,
createdbyname,
activitytypecodename
FROM (
SELECT filteredcontact.new_ratingname,
filteredactivitypointer.regardingobjectidname,
filteredactivitypointer.actualend,
filteredactivitypointer.Owneridname,
filteredactivitypointer.createdbyname,
filteredactivitypointer.activitytypecodename,
row_number() OVER (Partition BY regardingobjectid ORDER BY actualend DESC) AS recid
FROM FilteredActivityPointer
INNER JOIN FilteredContact ON FilteredContact.contactid = FilteredActivityPointer.regardingobjectid
WHERE new_ratingname NOT IN ('dead', 'archive', 'continous updates')
AND filteredactivitypointer.statecodename = 'completed'
AND filteredactivitypointer.owneridname = @user
AND filteredactivitypointer.createdbyname NOT IN ('melvin felicien', 'suzette collymore', 'gasper ')
AND filteredactivitypointer.activitytypecodename IN ('phone call', 'e-mail', 'fax')
AND filteredactivitypointer.actualend <= dateadd(d, -CAST(@NeglectedDays AS INT), GetUTCDate())
) AS d
WHERE recid = 1
ORDER BY actualend[/code]

E 12°55'05.25"
N 56°04'39.16"

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-11-13 : 13:48:27

it doesnt give the result it may be my code to do with the dates toward the bottom. when i run this it pulls data to find which leads have not been contacted over @neglected days eg. 90 days. but it returns the last contact to the lead 90 days or more ago.

Please help

Compnetsyslc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 14:36:23

Maybe you could post some sample data and expected output?

E 12°55'05.25"
N 56°04'39.16"

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-11-13 : 14:43:36

lead name(regardingobjectidname) | last contact date| owner | type
melvin felicien 10/25/07 mckin sthilaire email
forbes flicien 10/21/07 suzanne felix phonecall

Compnetsyslc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources