Query is Pulling Nulls

[Stangleboy]

I wrote a query to:
1. Do not use "Nulls" for End Date, and
#2 Give me "Max" EndDate.
But I am still get records that the has "Nulls" in the EndDate field, which needs to be excluded. Can somone please look at my code and advise me where I went wrong? Thank you all in advance for your time and patience.
SELECT
CC.C_CASE_EXTID As 'CaseID',
PD.PN_LAST_NM As 'LastName',
PD.PN_FIRST_NM As 'FirstName',
PPAC.PPAC_AID_CD As 'AIDCode',
PAC.PAC_END_DT As 'CaseEndDate',
MAX(PPAC.PPAC_END_DT) As 'EndDate'
FROM
CASE_CAS As CC INNER JOIN
CASE_PARTICIPANT As CP On CC.C_CASE_ID = CP.CP_CASE_ID INNER JOIN
PARTICIPANT_PUBLIC_ASSISTANCE_CASE As PPAC On CP.CP_PRTCP_ID = PPAC.PPAC_PRTCP_ID INNER JOIN
PUBLIC_ASSISTANCE_CASE As PAC on PAC.PAC_PUB_ASST_CASE_ID = PPAC.PPAC_PUB_ASST_CASE_ID INNER JOIN
PARTICIPANT_DEMOGRAPHIC As PD On PPAC.PPAC_PRTCP_ID = PD.P_PRTCP_ID INNER JOIN
CASE_ACCOUNT As CA On CC.CAS_CASE_ACCT_SUM_ID = CA.CA_CASE_ACCT_SUM_ID INNER JOIN
CHARGING_INSTRUCTION As CI On CA.CA_CASE_ACCT_ID = CI.CI_CASE_ACCT_ID And
PD.P_PRTCP_ID = CI.CI_CHLD_ID
WHERE
CC.C_MNG_CNTY_FIPS_CD = '071'
AND CC.C_PND_CLS_STAT_CD <> 'ACT'
AND CC.C_CRNT_STAT_CD = 'OPN'
AND CP.CP_ROLE_CD = 'DEP'
AND CP.CP_STAT_CD = 'ACT'
AND CC.C_CRNT_CASE_FUNC_CD IN ('EFA','EFL','EFM','EFO','ENF','ENL')
AND PPAC.PPAC_ROLE_CD = 'DEP'
AND PPAC.PPAC_AID_STAT_CD = 'ACT'
AND PPAC.PPAC_AID_CD In ('40','42','5K')

AND CI.CI_OBLG_TYPE_CD = 'C'
AND CI.CI_SUPT_AMT > 0
AND (CI.CI_END_DT > '2010-06-01'
Or CI.CI_END_DT Is Null)
AND CI.CI_INACTIVTN_DT Is Null
AND CC.C_CASE_EXTID =
(SELECT
CC1.C_CASE_EXTID
FROM
CASE_CAS As CC1 INNER JOIN
CASE_PARTICIPANT As CP1 On CC1.C_CASE_ID = CP1.CP_CASE_ID INNER JOIN
PARTICIPANT_DEMOGRAPHIC As PD1 On CP1.CP_PRTCP_ID = PD1.P_PRTCP_ID
WHERE
CP1.CP_ROLE_CD = 'CUS'
AND CP1.CP_STAT_CD = 'ACT'
AND CC1.C_MNG_CNTY_FIPS_CD = '071'
AND CC1.C_PND_CLS_STAT_CD <> 'ACT'
AND CC1.C_CRNT_STAT_CD = 'OPN'
AND PD1.P_AGENCY_NM Is Not Null
AND CC1.C_CASE_EXTID = CC.C_CASE_EXTID)
GROUP BY
CC.C_CASE_EXTID,
PD.PN_LAST_NM,
PD.PN_FIRST_NM,
PPAC.PPAC_AID_CD,
PAC.PAC_END_DT

 

[gmmastros]

You are grouping on a lot of columns, so the MAX end date applies to rows in the group. But... if all of the rows in a group have NULL in the table for end date, the max value will be NULL.

Take a look at this example by copy/pasting it to a query window and running it.

Declare @Temp Table(Id Int, EndDate DateTime)

Insert Into @Temp Values(1, '2010-07-01')
Insert Into @Temp Values(1, NULL)
Insert Into @Temp Values(1, '2010-03-01')

Insert Into @Temp Values(2, NULL)
Insert Into @Temp Values(2, NULL)
Insert Into @Temp Values(2, NULL)

Select Id, Max(EndDate) As MaxEndDate
From   @Temp
Group By ID

Since all of the id=2 rows have NULL for end EndDate column, that becomes the result of the Max aggregate. One thing you may want to consider is including another where clause filter, like this:

Declare @Temp Table(Id Int, EndDate DateTime)

Insert Into @Temp Values(1, '2010-07-01')
Insert Into @Temp Values(1, NULL)
Insert Into @Temp Values(1, '2010-03-01')

Insert Into @Temp Values(2, NULL)
Insert Into @Temp Values(2, NULL)
Insert Into @Temp Values(2, NULL)

Select Id, Max(EndDate) As MaxEndDate
From   @Temp
[!]Where  EndDate Is Not NULL[/!]
Group By ID

In this case, you will not get any rows for ID = 2, which seems like what you want based on your questions.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Stangleboy]

The dates listed above:
'2010-07-01' and '2010-03-01',
are they fluid where I need to change them every month? My data always houses previous months data never current data, I am currently working with data up to 05/31/2010? Thank you again for your time and patience.

 

[gmmastros]

No. The actual query I posted earlier has got nothing (specifically) to do with your query. I used it as an example only. The point is, MAX can return NULL only if every row that is included in the group contains NULL.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Stangleboy]

So should I be looking for another way to code this query to get rid of records with EndDates as null? Would it be better to create a temp table or sub_Query?

Also I copy and pasted the wrong code, I did have the WHERE statement with "EndDate Is Not Null", but the query still showed records where the current EndDate was "Null".

 

[markros]

It could not give you this result unless you had OR conditions in your query. Can you post just the where condition of your query so we can try to figure out the problem?

PluralSight Learning Library

 

[Stangleboy]

Definitely. It is the same as above but I copied the one with the IS Not Null.

AND PPAC.PPAC_ROLE_CD = 'DEP'
AND PPAC.PPAC_AID_STAT_CD = 'ACT'
AND PPAC.PPAC_AID_CD In ('40','42','5K')
AND PPAC.PPAC_END_DT Is Not Null
AND CI.CI_OBLG_TYPE_CD = 'C'
AND CI.CI_SUPT_AMT > 0
AND (CI.CI_END_DT > '2010-06-01'
Or CI.CI_END_DT Is Null)
AND CI.CI_INACTIVTN_DT Is Null

Thank you.

 

[markros]

You have this condition here
AND (CI.CI_END_DT > '2010-06-01'
Or CI.CI_END_DT Is Null)

so it's going to return records with NULL in the end date.

PluralSight Learning Library

 

[Stangleboy]

Oops sorry, I copied to much, no. It is:

AND PPAC.PPAC_ROLE_CD = 'DEP'
AND PPAC.PPAC_AID_STAT_CD = 'ACT'
AND PPAC.PPAC_AID_CD In ('40','42','5K')
AND PPAC.PPAC_END_DT Is Not Null

And the Select statement with:

MAX(PPAC.PPAC_END_DT) As 'EndDate'