Avatar of Butterfly2
Butterfly2
 asked on

How do I show null reconds on a left join with a filter

I know this easy, but my brain is still on vacation.  Anyway, I have the following query:

select m.[Patient Name],
         c.episode_no,
          convert(varchar(10),CAST( m.DOB as DATE), 101) as DOB,
         m.[Appt Date],
       CONVERT(varchar(10),c.start_dtime,  101)  + ' ' + CONVERT(varchar(10),c.start_dtime,  108) as DOS,
        o.orgz_short_name as Entity
     
         
from Customer.MammoGramPHSFinal as m
left outer join smsmir.mir_pms_case as c
    on m.[Patient Name] = REPLACE(c.rpt_name, ' ,', ', ')
   and CONVERT(varchar(10),c.birth_dtime,101) =  convert(varchar(10),CAST( m.DOB as DATE), 101)
Inner join smsmir.mir_orgz as o  on     c.src_sys_id = o.src_sys_id
where (year(c.start_dtime) = year(m.[Appt Date])
       and Month(c.start_dtime) = Month(m.[Appt Date]))
     o.orgz_short_name = 'BOS'
   order by [Appt Date],
                [Patient Name]

I need the filters but I need to show the nulls as well.
Microsoft SQL Server

Avatar of undefined
Last Comment
Butterfly2

8/22/2022 - Mon
tigin44

you may replace
   and  o.orgz_short_name = 'BOS'

with
    and  (o.orgz_short_name = 'BOS' OR ISNULL(o.orgz_short_name, '-') = '-')
Butterfly2

ASKER
what about the date filter?
tigin44

so try this

(year(ISNULL(c.start_dtime,GETDATE())) = year(ISNULL(m.[Appt Date], GETDATE()))
    and Month(ISNULL(c.start_dtime,GETDATE())) = Month(ISNULL(m.[Appt Date], GETDATE())))
    and  (o.orgz_short_name = 'BOS' OR ISNULL(o.orgz_short_name, '-') = '-')
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Butterfly2

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Butterfly2

ASKER
I figure out the answer and it wasnt even close to the sugguestion I recieved.