Need to select MAX(Date) BUT I want NULL be to the max if found

Last Post 19 Oct 2007 12:31 PM by Maxer. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Maxer
New Member
New Member

--
17 Oct 2007 12:02 PM
This is a bit odd, but here goes.

Let's say I have a table that contains a list of subscribers to a magazine.

People tend to start subscriptions, then stop them... maybe even have a year or so go by, and then start the subscription again. If the EndDt is NULL then that means the customer is currently subscribed
so I have:

CustomerID__StartDt_________EndDt_______otherjunk
1___________01-01-2002_____12-15-2005
1___________12-28-2005_____NULL
2___________06-15-2000_____05-01-2005
2___________07-15-2005_____11-25-2006

Now I want to get the most recent or current subscription for all the customers in the table.

Assuming I can NOT use StartDt (maybe subscriptions overlapp or something really weird, who knows) how could I do this?

If I say
SELECT CustomerID, MAX(EndDt)
FROM tblCustomerSubs
GROUP BY CustomerID

Then that would work great for customer 2 as I'd get 11-25-2006.

HOWEVER, customer 1 would miss that they have an active subscription and instead I'd get 12-15-2005.

How can I deal with the NULL value?

Is there any good way to get that information?
Maxer
New Member
New Member

--
18 Oct 2007 03:56 AM
Makes sense, thanks!

Question:

Any reason to use ISNULL() over a CASE statement?

Is one more efficient than the other?
Maxer
New Member
New Member

--
18 Oct 2007 07:19 AM
Ok, but what if I then need to join that MAX(EndDt) to another table once I get it (or well to the same table again so I can go back in for the PK and other fields).

SELECT *
FROM tblCustomerSubs AS CS INNER JOIN
(SELECT CustomerID, MAX(isnull(EndDt, '2020001') AS MaxEndDt)
FROM tblCustomerSubs
GROUP BY CustomerID ) AS MaxCust ON
CS.CustomerID = MaxCust.CustomerID AND
CS.EndDt = MaxCust.MaxEndDt

In this case it won't work because tblCustomerSubs has EndDt as NULL for all active customers and I just flipped it to 2020.

Anyway I can easily flip that back?

I was thinking of wrapping the whole thing in yet ANOTHER derived outter table and using a CASE in the SELECT statement... which just seems like the whole thing is becoming a huge nasty mess.
SQLUSA
New Member
New Member

--
19 Oct 2007 12:20 PM
You can use the CASE function in the ORDER BY clause to achieve desired sort order.


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
The Best SQL Server 2005 Training in the World:
http://www.sqlusa.com/order2005grandslam/
Maxer
New Member
New Member

--
19 Oct 2007 12:31 PM
Interesting, I hadn't thought of that.

Are you saying instead of MAX(EndDt) I'd say TOP 1 and use ORDER BY then with CASE flip the NULL values to whatever datetime worked for my needs?

SQLUSA
New Member
New Member

--
23 Oct 2007 12:37 AM
Precisely.

Here is an example: http://www.sqlusa.com/bestpractices...derbycase/

Maxer
New Member
New Member

--
24 Oct 2007 03:48 AM
Interesting, I'll play around with that as well, thanks.
You are not authorized to post a reply.

Acceptable Use Policy