How do I Using MAX (DATE) and still include nulls??

Question

Post reply

How do I Using MAX (DATE) and still include nulls??

Ramona Roman

Old Hand

Points: 399
More actions
December 13, 2004 at 3:26 pm

#87121

I am attempting to write a query that will give me the most recent contract for each account in the database. Some accounts may have several contracts over the years, one for each year. I have used the following query to get the most recent contract for each account:
SELECT distinct accountnumber, contract begindate, contract enddate
FROM FLAccounts fa
WHERE fa.EffEndDate=(SELECT MAX(fa2.EffendDate) FROM #LAccounts fa2 WHERE Fa2.accountnumber=fa.accountnumber)
I notice, however, that those accounts that have nulls for their contract terms are excluded from the results.
How can i modify this query to return the most recent contract and those accounts that have never had a contract as well.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6084 More actions · Answer 1

one way is to use isnull:

select MAX( ISNULL( checkdate, '1/1/2050') ) ...

then use a case statement to translate that date literal back to a null.

Yoda-193403 SSC Eights! Points: 988 More actions · Answer 2

In this case, simple logic in the WHERE will suffice:

WHERE fa.EffEndDate=(SELECT MAX(fa2.EffendDate) FROM #LAccounts fa2 WHERE Fa2.accountnumber=fa.accountnumber)

or fa.EffEndDate is null

Ramona Roman Old Hand Points: 399 More actions · Answer 3

WHERE fa.EffEndDate=(SELECT MAX(fa2.EffendDate) FROM #LAccounts fa2 WHERE Fa2.accountnumber=fa.accountnumber)

or fa.EffEndDate is null

hm...i tired this and it pulled both the max date and null dates for some accounts. It seems at though some accounts may have null entries for a contract date in addition to valid dates

Norman Neil Grasshopper Points: 21 More actions · Answer 4

hm...i tired this and it pulled both the max date and null dates for some accounts. It seems at though some accounts may have null entries for a contract date in addition to valid dates

His solution should work. Contract date should have nothing to with EffEndDate. It may be easier for us to help you debug this problem if you post some DDL and sample data...

cheers,

Norm

Ramona Roman Old Hand Points: 399 More actions · Answer 5

AccountNum	EffEndDate	EffBeginDate
4000331	8/1/2004	7/31/2005
4000331	8/1/2003	7/31/2004
4000331	8/1/2002	7/31/2003
4000432	NULL	NULL
4000836	8/1/2004	7/31/2005
4000836	8/1/2003	7/31/2004
4000836	8/1/2002	7/31/2003
4000836	NULL	NULL
4000937	8/1/2004	7/31/2005

I guess what i am trying to say is that there are some accounts that have several contracts as well as nulls and other contracts have no contracts and all nulls.

How do i select the max contract date and include those that have null contract dates as well.

Farrell Keough SSCoach Points: 17414 More actions · Answer 6

Try this, (I used your data):

CREATE TABLE #Account( AccountNum int,

EffEndDate datetime,

EffBeginDate datetime)

INSERT INTO #Account

SELECT 4000331, '8/1/2004', '7/31/2005'

INSERT INTO #Account

SELECT 4000331, '8/1/2003', '7/31/2004'

INSERT INTO #Account

SELECT 4000331, '8/1/2002', '7/31/2003'

INSERT INTO #Account

SELECT 4000432, NULL, NULL

INSERT INTO #Account

SELECT 4000836, '8/1/2004', '7/31/2005'

INSERT INTO #Account

SELECT 4000836, '8/1/2003', '7/31/2004'

INSERT INTO #Account

SELECT 4000836, '8/1/2002', '7/31/2003'

INSERT INTO #Account

SELECT 4000836, NULL, NULL

INSERT INTO #Account

SELECT 4000937, '8/1/2004', '7/31/2005'

SELECT DISTINCT AccountNum INTO #AccountNum FROM #Account

SELECT AN.AccountNum, CONVERT( varchar, MAX( ISNULL( A.EffBeginDate, '01/01/1900')), 101) AS ContractDate

INTO #OutPut

FROM #AccountNum AN

INNER JOIN #Account A ON( AN.AccountNum = A.AccountNum)

GROUP BY AN.AccountNum

SELECT AccountNum,

CASE

WHEN ContractDate = '01/01/1900'

THEN ' '

ELSE ContractDate

END AS ContractDate

FROM #OutPut

I imagine there is better way to handle the output than putting this into the #OutPut table, but I had trouble aggregating the NULL dates and outputting an empty string. One of these guru's can probably show you how to handle that.

I wasn't born stupid - I had to study.

Donald Sawford SSCrazy Points: 2778 More actions · Answer 7

Using Farrell's table, I think that this variation of some of the previous solutions will do what you want:

SELECT DISTINCT ac.AccountNum, ac.EffBeginDate, ac.EffEndDate

FROM #Account ac

JOIN

(SELECT AccountNum,

MAX(COALESCE(EffEndDate, '01/01/1900')) AS MaxDate

FROM #Account GROUP BY AccountNum) an

ON an.MaxDate = COALESCE(ac.EffEndDate, '01/01/1900')

AND an.AccountNum = ac.AccountNum

The distinct probably is not needed unless there could be multiple NULLs.

See if it does give the results you are looking for.