Most recent date record across three date fields

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)

Most recent date record across three date fields

Author

Topic

brad_x81
Starting Member

6 Posts

Posted - 2014-12-03 : 06:04:57

Morning,

Another day in SQL paradise.

I'm trying to return the most recent date record across three date fields in two different tables.

I'm not sure CASE statement was the way to go I get an error for END AS, any pointers - or some other code that will do what I'm trying to achieve?

Code
******

select distinct

a.unique_id__c as [Account ID],
a.id as [GDW Acc ID],
t.ActivityDate as [Task Table Activity Date],
a.LastActivityDate as [Account Last Ativity Date],
t.LastModifiedDate as [Task Table Last Modified Date],
MostRecentDate

from vwaccount a

inner join vwtask t on a.id = t.accountid

left join vwuser u1 on t.createdbyid = u1.id
left join vwuser u2 on t.lastmodifiedbyid = u2.id
left join vwprofile pr1 on u1.profileid = pr1.id
left join vwprofile pr2 on u2.profileid = pr2.id

(CASE
WHEN t.ActivityDate >= COALESCE(a.LastActivityDate,'') AND t.ActivityDate >= COALESCE(t.LastModifiedDate,'') THEN t.ActivityDate
WHEN a.LastActivityDate >= COALESCE(t.ActivityDate,'') AND a.LastActivityDate >= COALESCE(t.LastModifiedDate,'') THEN a.LastActivityDate
WHEN t.LastModifiedDate >= COALESCE(t.ActivityDate,'') AND t.LastModifiedDate >= COALESCE(a.LastActivityDate,'') THEN t.LastModifiedDate
ELSE t.ActivityDate
END as MostRecentDate)

where

(pr1.name in ('Channel/Alliance Operations') or pr2.name in ('Channel/Alliance Operations'))

and

t.AccountId in ('001F0000019LtMiIAK',
'001F000001DboqRIAR',
'001a000001EDmDJAA1',
'001a000001EDmEpAAL',
'001a000001EDsayAAD',
'001a000001EDsb3AAD',
'001a000001EDuxRAAT',
'001a000001EDv5xAAD')

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-03 : 09:01:52

You can't put a case expression after a from or join clause (that's why you get an error)

FWIW I sometimes solve similar problems like this:

select ...other columns
(select max(d) from (values (t.ActivityDate),(a.LastActivityDate),(t.LastModifiedDate)) v(d) as MostRecentDate

from ...
join ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources