Solved

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

Posted on 2014-01-03
5
263 Views
Last Modified: 2014-01-08
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.
0
Comment
Question by:Butterfly2
  • 3
  • 2
5 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 39753863
you may replace
   and  o.orgz_short_name = 'BOS'

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

Author Comment

by:Butterfly2
ID: 39753866
what about the date filter?
0
 
LVL 26

Expert Comment

by:tigin44
ID: 39753921
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, '-') = '-')
0
 

Accepted Solution

by:
Butterfly2 earned 0 total points
ID: 39753945
I figured it out:

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)
  and  (c.start_dtime is null
           or(year(c.start_dtime) = year(m.[Appt Date])
            and Month(c.start_dtime) = Month(m.[Appt Date])))
left join smsmir.mir_orgz as o  
     on   c.src_sys_id = o.src_sys_id
     and o.orgz_short_name = 'BOS'  

   order by [Appt Date],
                [Patient Name]
0
 

Author Closing Comment

by:Butterfly2
ID: 39764673
I figure out the answer and it wasnt even close to the sugguestion I recieved.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now