title: Is CROSS APPLY the best way or possibly WHEN EXISTS or a subquery?
body: <p>I am relatively inexperienced when it comes to SQL. I an wondering if using <code>CROSS APPLY</code> is the best option in the SQL below? </p>
  
  <p>I also think I am duplicating work. This works and takes a few minutes but my feeling is it can be done better. </p>
  
  <p>The three main tables I'm looking at are quite large and on their own we are looking at a couple of million rows each. The individual insert statements are pulling back around 7-15K rows each.</p>
  
  <hr>
  
  <pre><code>DECLARE @Master TABLE
    (
       heyno              NVARCHAR(12),
       postcodestartdttm  DATE,
       postcodeenddttm    DATE,
       lzohistorypostcode NVARCHAR(25),
       biactivitypostcode NVARCHAR(25),
       activityenddttm    DATE
    );
  
  INSERT INTO @Master
  SELECT p.pasid,
         par.startdttm,
         par.enddttm,
         pa.postcode AS LZOHistoryPostcode,
         t.postcode  AS BIActivityPostcode,
         t.admitdate
  FROM   HEALTHBI.DBO.[lzo_patientaddressrole] AS par
         INNER JOIN HEALTHBI.DBO.[lzo_patientaddress] AS pa
                 ON par.[addressoid] = pa.[oid]
                    AND pa.[status] = 'A'
         INNER JOIN HEALTHBI.DBO.lzo_patient AS p
                 ON par.identifyingoid = p.oid
         --- 
         CROSS APPLY (SELECT i.admitdate,
                             i.postcode
                      FROM   HEALTHBI_VIEWS.DBO.ip_admission i
                      WHERE  i.patientoid = p.oid
                             AND i.admitdate &gt; par.startdttm
                             AND i.admitdate &gt;= '01 APRIL 2010'
                             AND i.admitdate &lt; ISNULL(par.enddttm, '31-dec-4712')
                             AND par.[status] = 'A'
                             AND par.[identifyingtype] = 'Patient'
                             AND par.[rotypcode] = 'CC_USUALADD'
                             AND pa.[adtypcode] = 'Address'
                             AND pa.postcode &lt;&gt; i.postcode) AS t
  
  INSERT INTO @Master
  SELECT p.pasid,
         par.startdttm,
         par.enddttm,
         pa.postcode AS LZOHistoryPostcode,
         t.postcode  AS BIActivityPostcode,
         t.apptstartdate
  FROM   HEALTHBI.DBO.[lzo_patientaddressrole] AS par
         INNER JOIN HEALTHBI.DBO.[lzo_patientaddress] AS pa
                 ON par.[addressoid] = pa.[oid]
                    AND pa.[status] = 'A'
         INNER JOIN HEALTHBI.DBO.lzo_patient AS p
                 ON par.identifyingoid = p.oid
         CROSS APPLY (SELECT i.apptstartdate,
                             i.postcode
                      FROM   HEALTHBI_VIEWS.DBO.op_appointment i
                      WHERE  i.patientoid = p.oid
                             AND i.apptstartdate &gt; par.startdttm
                             AND i.apptstartdate &gt;= '01 APRIL 2010'
                             AND i.apptstartdate &lt; ISNULL(par.enddttm, '31-dec-4712')
                             AND par.[status] = 'A'
                             AND par.[identifyingtype] = 'Patient'
                             AND par.[rotypcode] = 'CC_USUALADD'
                             AND pa.[adtypcode] = 'Address'
                             AND pa.postcode &lt;&gt; i.postcode) AS t
  
  INSERT INTO @Master
  SELECT p.pasid,
         par.startdttm,
         par.enddttm,
         pa.postcode AS LZOHistoryPostcode,
         t.postcode  AS BIActivityPostcode,
         t.attenddate
  FROM   HEALTHBI.DBO.[lzo_patientaddressrole] AS par
         INNER JOIN HEALTHBI.DBO.[lzo_patientaddress] AS pa
                 ON par.[addressoid] = pa.[oid]
                    AND pa.[status] = 'A'
         INNER JOIN HEALTHBI.DBO.lzo_patient AS p
                 ON par.identifyingoid = p.oid
         CROSS APPLY (SELECT i.attenddate,
                             i.postcode
                      FROM   HEALTHBI_VIEWS.DBO.ed_attendance i
                      WHERE  i.patientoid = p.oid
                             AND i.attenddate &gt; par.startdttm
                             AND i.attenddate &gt;= '01 APRIL 2010'
                             AND i.attenddate &lt; ISNULL(par.enddttm, '31-dec-4712')
                             AND par.[status] = 'A'
                             AND par.[identifyingtype] = 'Patient'
                             AND par.[rotypcode] = 'CC_USUALADD'
                             AND pa.[adtypcode] = 'Address'
                             AND pa.postcode &lt;&gt; i.postcode) AS t
  
  SELECT m.heyno,
         m.lzohistorypostcode,
         m.biactivitypostcode,
         d.startofweek
  FROM   @Master m
         INNER JOIN HEALTHBI_VIEWS.DBO.date_reference AS d
                 ON m.activityenddttm = d.datevalue
  GROUP  BY m.heyno,
            m.lzohistorypostcode,
            m.biactivitypostcode,
            d.startofweek
  ORDER  BY m.heyno 
  </code></pre>