monster query

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)=7
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)=7
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
         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
   FROM
      (           SELECT
         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                 ,
         ROW_NUMBER() OVER (PARTITION
      BY
         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
      ORDER BY
         CASE
            WHEN IMS_DEA_NBR = 'Unkown' THEN 0
            ELSE 1
         END,
         IMS_DEA_NBR) AS RowNumberInGroup                  COUNT() OVER (PARTITION
      BY
         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)                      AS CountPerGroup,
         SUM(CASE
            WHEN deaID = 'Unkown' THEN 1
            ELSE 0
         END)                        OVER (PARTITION
      BY
         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)                       AS UnknownCountPerGroup
      FROM
         ctemain        ) T
   WHERE
      (
         T.CountPerGroup = T.UnknownCountPerGroup
         AND T.RowNumberInGroup = 1
      )
      OR T.RowNumberInGroup > T.UnknownCountPerGroup