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.
Author |
Topic |
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2013-02-08 : 05:25:16
|
hello therei have the following queryselect 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 cleft join MemberShip_Facts m onc.Client_Ref = m.Purchaser_Ref left join Ticket_Facts t onc.Client_Ref = t.Purchaseid group by c.client_reforder by c.Client_Ref ascplease see an example dataset below.client_ref Latest_Customer_Change latest_membership Latest_Transaction1 2012-10-30 10:45:00.000 2013-01-25 12:16:18.477 2008-08-08 10:01:21.3301000 2008-02-08 12:09:00.000 NULL 2008-08-08 10:01:21.33010000 2011-03-10 15:42:00.000 NULL 2008-08-08 10:01:21.330100000 2008-04-12 15:06:00.000 NULL NULL100001 2008-05-04 08:08:00.000 NULL 2008-04-08 10:31:53.420I want to have an extra column at the end selecting highest date out of the three columns, How would i do this?RegardsRob |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-08 : 05:33:49
|
use a case when expressionCASE 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 MVPhttp://visakhm.blogspot.com/ |
|
|
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,? |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2013-02-13 : 07:27:26
|
thank you, what is the value '17530101' for |
|
|
|
|
|
|
|