Need Max Date - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Need Max Date

Author

Topic

Lemmy44
Starting Member

20 Posts

Posted - 2011-06-29 : 10:21:24

OK, so this is one of those deals that seems simple in my mind, but for the life of me I cannot make this work.

What I have so far, is a query that pulls out client names, their case open date, and the date of contact, based on a few different criteria.

What I need to do, is list only those clients, whose LAST contact was this certain contact type (Wait List Letter). I've tried using the MAX fucntion, but I just can't get it to return any values (I have confirmed that there are several clients that should be showing up).

Here is what I have so far:

SELECT distinct person.last_name + ', ' + person.first_name, cases.open_date, contact.contact_date

FROM person

INNER JOIN collection_date ON
person.person_id = collection_date.parent_id
AND collection_date.child_id = 'CLIENTROLE'

INNER JOIN participant_collection ON
person.person_id = participant_collection.child_id

INNER JOIN contact ON
participant_collection.parent_id = contact.contact_id
AND contact.contact_type_id = '_L050001AB' /* Wait List Letter */

INNER JOIN response_choice ON
contact.contact_id = response_choice.parent_id
AND response_choice.question_id = '399L000017' /* Letter Type */
AND response_choice.answer_id IN ('399L000019', '399L00001A') /* Reminder, Still Waiting */

INNER JOIN cases ON
person.person_id = cases.parent_id
AND cases.close_date IS NULL

INNER JOIN response_choice AS response_choice1 ON
cases.case_id = response_choice1.parent_id
AND response_choice1.question_id = 'QFJD0000BR' /* Case Status */
AND response_choice1.answer_id = 'QFJD0000ON' /* Waiting */

INNER JOIN case_type ON
cases.case_type_id=case_type.case_type_id
AND (case_type.case_type_name LIKE 'YF%'
OR case_type.case_type_name LIKE 'EY%'
OR case_type.case_type_name LIKE 'ACT%'
OR case_type.case_type_name LIKE 'AO%'
OR case_type.case_type_name LIKE 'Day%'
OR case_type.case_type_name LIKE '%Triple%')

WHERE person.person_id NOT IN
(SELECT person.person_id
FROM question AS question6, response_choice AS response_choice6, cases AS cases6, answer AS answer6
WHERE person.person_id=cases6.parent_id
AND question6.question_name='Case Status'
AND response_choice6.question_id=question6.question_id
AND response_choice6.parent_id=cases6.case_id
AND response_choice6.answer_id=answer6.answer_id
AND answer6.answer_name='active')
AND DATEDIFF(dd, contact.contact_date, GETDATE()) > 90

Any suggestions would be appreciated.

Cheers.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-29 : 10:30:50

You can incorporate the RANK function, add something like this to your existing code
,rank() over(partition by person.firstName,person.lastName order by contactDate desc) as Row

and then wrap your current code in another select
SELECT *
FROM
(<your current code + the ranking function>
) t
where row = 1 and contactType = 'Wait List Letter'

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources