I received some help from a peer, that gave me this sql code to make sure I knew what I wanted to pull back on a new Crystal report we are designing.

from AUTHS a
       left join [dbo].[V_SupplementalDataEndServiceDate] e on e.AUTHUKEY = a.AUTHUKEY
where
       e.AUTHUKEY IS NULL

I went to try and put that into my report selection formula as the line below with ******* pointed out. But this is not working.

(({Members.CGRPID} like {?Health Plan} and
{Members.CPLNTP} like {?LOB}) or
isnull({Members.MBRNO})) and

{Members.CIPACD} like {?IPA} and

{AUTHS.Status} <> "Cancelled" and

({AUTHS.ExpirationDate} < {@StartDefaultDate} or
not isnull({Members.TermDate})) and

{AUTHS.ReferralType} like ["HPC","DIA"]
******and isnull ({V_SupplementalDataEndServiceDate.EndServiceDate})

Everything works on the report except that line. It brings back nothing. Is this enough information for anyone to help/add some suggestions to my formula? I am trying to bring in all records that do not have an EndServiceDate and it's not working correctly. It brings back nothing.

Edited by BoltzGirl - 29 Aug 2017 at 6:30am

I did alter the join. One of the most confusing things for me on this is; that each record actually does not use the EndServiceDate at all. Therefore, I would think I would be pulling back quite a few records as "Null". But what I have found is; that if the field isn't used at all...it's not 'recorded' within the table. The field is only recorded when used, if that makes sense. So I am trying to pull a specific type of records with my other formula, to then only pull those types with the EndServiceDate as null....Does this make any sense? I might not be explaining clearly at all, as it is a bit confusing.

I GOT IT!!!! Makes sense now!!! THANK YOU SO MUCH!!!