monster

with ctebiggie  as (    select
   distinct  p.[IMS_PRESCRIBER_ID],
   p.PHYSICIAN_NPI as MLISNPI,
   a.CLIENT_ID,
   p.MLIS_FIRSTNAME,
   p.MLIS_LASTNAME,
   p_address.IMS_DEA_NBR,
   p.IMS_PROFESSIONAL_ID_NBR,
   p.IMS_PROFESSIONAL_ID_NBR_src,
   p.IMS_CERTIFICATION_CODE,
   datepart(mm,
   a.RECEIVED_DATE) as [Month],
   datepart(yyyy,
   a.RECEIVED_DATE) as [Year]
from
   MILLENNIUM_DW_dev..D_PHYSICIAN p
left outer join
   MILLENNIUM_DW_dev..F_ACCESSION_DAILY a
      on a.REQUESTOR_NPI=p.PHYSICIAN_NPI
left outer join
   MILLENNIUM_DW_dev..D_PHYSICIAN_ADDRESS p_address
      on p.PHYSICIAN_NPI=p_address.PHYSICIAN_NPI
where
   a.RECEIVED_DATE is not null  --
   and p.IMS_PRESCRIBER_ID is not null  --
   and p_address.IMS_DEA_NBR !='UNKNOWN'
   and p.REC_ACTIVE_FLG=1
   and p_address.REC_ACTIVE_FLG=1
   and DATEPART(yyyy,received_date)=2012
   and DATEPART(mm,received_date)=6
group by
   p.[IMS_PRESCRIBER_ID],
   p.PHYSICIAN_NPI,
   p.IMS_PROFESSIONAL_ID_NBR,
   p.MLIS_FIRSTNAME,
   p.MLIS_LASTNAME,
   p_address.IMS_DEA_NBR,
   p.IMS_PROFESSIONAL_ID_NBR,
   p.IMS_PROFESSIONAL_ID_NBR_src,
   p.IMS_CERTIFICATION_CODE,
   datepart(mm,
   a.RECEIVED_DATE),
   datepart(yyyy,
   a.RECEIVED_DATE),
   a.CLIENT_ID    )  ,  ctecount as   (select
   COUNT (Distinct f.ACCESSION_ID) [count],
   f.REQUESTOR_NPI,
   f.CLIENT_ID,
   datepart(mm,
   f.RECEIVED_DATE) mm,
   datepart(yyyy,
   f.RECEIVED_DATE)yyyy
from
   MILLENNIUM_DW_dev..F_ACCESSION_DAILY f
where
   f.CLIENT_ID not in (select
      *
   from
      SalesDWH..TestPractices)
   and DATEPART(yyyy,f.received_date)=2012
   and DATEPART(mm,f.received_date)=6
group by
   f.REQUESTOR_NPI,
   f.CLIENT_ID,
   datepart(mm,
   f.RECEIVED_DATE),
   datepart(yyyy,
   f.RECEIVED_DATE)  )    ,
   ctemain (IMS_PRESCRIBER_ID   ,
   MLISNPI  ,
   CLIENT_ID1  ,
   MLIS_FIRSTNAME   ,
   MLIS_LASTNAME   ,
   IMS_DEA_NBR   ,
   IMS_PROFESSIONAL_ID_NBR   ,
   IMS_PROFESSIONAL_ID_NBR_src   ,
   IMS_CERTIFICATION_CODE   ,
   [Month]   ,
   [Year]   ,
   [count]   ,
   REQUESTOR_NPI   ,
   CLIENT_ID2   ,
   mm   ,
   yyyy  ) as(    select
      ctebiggie.*,
      c.*
   from
      ctebiggie  full
   outer join
      ctecount c
         on c.REQUESTOR_NPI=ctebiggie.MLISNPI
         and c.mm=ctebiggie.[Month]
         and c.yyyy=ctebiggie.[Year]
         and c.CLIENT_ID=ctebiggie.CLIENT_ID    )    select
         distinct    IMS_PRESCRIBER_ID   ,
         MLISNPI  ,
         CLIENT_ID1  ,
         MLIS_FIRSTNAME   ,
         MLIS_LASTNAME   ,
         IMS_PROFESSIONAL_ID_NBR   ,
         IMS_PROFESSIONAL_ID_NBR_src   ,
         IMS_CERTIFICATION_CODE   ,
         [Month]   ,
         [Year]   ,
         [count]   ,
         REQUESTOR_NPI   ,
         CLIENT_ID2   ,
         mm   ,
         yyyy    ,
         isnull(( select
            IMS_DEA_NBR
      from
         ctemain
      where
         ctemain.IMS_PRESCRIBER_ID=main.IMS_PRESCRIBER_ID
         and  ctemain.MLISNPI  =main.MLISNPI
         and  ctemain.CLIENT_ID1  =main.CLIENT_ID1
         and  ctemain.MLIS_FIRSTNAME=main.MLIS_FIRSTNAME
         and  ctemain.MLIS_LASTNAME=main.MLIS_LASTNAME
         and  ctemain.IMS_PROFESSIONAL_ID_NBR=main.IMS_PROFESSIONAL_ID_NBR
         and  ctemain.IMS_PROFESSIONAL_ID_NBR_src=main.IMS_PROFESSIONAL_ID_NBR_src
         and  ctemain.IMS_CERTIFICATION_CODE=main.IMS_CERTIFICATION_CODE
         and  ctemain.[Month]=main.[Month]
         and  ctemain.[Year]=main.[Year]
         and  ctemain.[count]=main.[count]
         and  ctemain.REQUESTOR_NPI=main.REQUESTOR_NPI
         and  ctemain.CLIENT_ID2=main.CLIENT_ID2
         and  ctemain.mm=main.mm
         and  ctemain.yyyy =main.yyyy
         and IMS_DEA_NBR <>'UNKNOWN'),
      'UNKNOWN')
   from
      ctemain main