sql to Crystal

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

did you alter the join in the data source set up to make it an left outer join?

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.

you want to only include 'not closed' records but the close date is stored in another table so you need to do a select where the matching ID does not exist in that table.

First test to make sure that you router join is working. Place the discharge date into the report canvas and see if you are getting both closed and open records. placing the field on your report will enforce the join and you can make sure your joins are OK.
If the joins are OK, in the select criteria you can set it to "use default values for nulls" which will assist in how it reads things in sequence then try to use the same code your sql person did

and isnull ({V_SupplementalDataEndServiceDate.AUTHUKEY})

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