SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Select highest date from columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
548 Posts

Posted - 02/08/2013 :  05:25:16  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/08/2013 :  05:33:49  Show Profile  Reply with Quote
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

United Kingdom
548 Posts

Posted - 02/13/2013 :  04:26:12  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/13/2013 :  04:56:45  Show Profile  Reply with Quote
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

United Kingdom
548 Posts

Posted - 02/13/2013 :  07:05:45  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/13/2013 :  07:11:02  Show Profile  Reply with Quote

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


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

Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
548 Posts

Posted - 02/13/2013 :  07:27:26  Show Profile  Reply with Quote
thank you, what is the value '17530101' for
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000