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)
 Select highest date from columns

Author  Topic 

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-02-08 : 05:25:16
hello there

i have the following query

select top 5 c.client_ref
,max(c.Client_Changed)as 'Latest_Customer_Change'
, max(m.tlog_date) as 'latest_membership'
, max(t.transactionpurchasedate) as ' Latest_Transaction'
from Customer_Facts c
left join MemberShip_Facts m on
c.Client_Ref = m.Purchaser_Ref
left join Ticket_Facts t on
c.Client_Ref = t.Purchaseid
group by c.client_ref
order by c.Client_Ref asc

please see an example dataset below.

client_ref Latest_Customer_Change latest_membership Latest_Transaction
1 2012-10-30 10:45:00.000 2013-01-25 12:16:18.477 2008-08-08 10:01:21.330
1000 2008-02-08 12:09:00.000 NULL 2008-08-08 10:01:21.330
10000 2011-03-10 15:42:00.000 NULL 2008-08-08 10:01:21.330
100000 2008-04-12 15:06:00.000 NULL NULL
100001 2008-05-04 08:08:00.000 NULL 2008-04-08 10:31:53.420

I want to have an extra column at the end selecting highest date out of the three columns, How would i do this?

Regards

Rob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-08 : 05:33:49
use a case when expression

CASE WHEN max(c.Client_Changed)> max(m.tlog_date)
THEN CASE WHEN max(c.Client_Changed) > max(t.transactionpurchasedate)
THEN max(c.Client_Changed)
ELSE max(t.transactionpurchasedate)
END
ELSE CASE WHEN max(m.tlog_date) > max(t.transactionpurchasedate)
THEN max(m.tlog_date)
ELSE max(t.transactionpurchasedate)
END
END


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

Go to Top of Page

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-02-13 : 04:26:12
Thank you very much for this, how do i get around ignoring nulls, if there is a null in either of the field, then my last activity column becomes a null,?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 04:56:45
In case of nulls what should your last activity column value be?

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

Go to Top of Page

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-02-13 : 07:05:45
if there are three null values then the last activity will be null.

but if only one of the columns are null, the last activity should still be the highest
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 07:11:02
[code]
CASE WHEN max(COALESCE(c.Client_Changed,'17530101'))> max(COALESCE(m.tlog_date,'17530101'))
THEN CASE WHEN max(COALESCE(c.Client_Changed,'17530101')) > max(COALESCE(t.transactionpurchasedate,'17530101'))
THEN max(c.Client_Changed)
ELSE max(t.transactionpurchasedate)
END
ELSE CASE WHEN max(COALESCE(m.tlog_date,'17530101')) > max(COALESCE(t.transactionpurchasedate,'17530101'))
THEN max(m.tlog_date)
ELSE max(t.transactionpurchasedate)
END
END
[/code]

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

Go to Top of Page

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-02-13 : 07:27:26
thank you, what is the value '17530101' for
Go to Top of Page
   

- Advertisement -