title: Duplicate Records showing SQL Server 2008 R2
body: <p>I have a query that I am working with and I want to change the criteria of how a person is selected, instead of being selected by the type of doctor a person gets an order written by, I want to see results come back if a person has had activity performed by a physician of a certain type. </p>
  
  <p>In some cases the same order will show up 1000 times or more. </p>
  
  <p>Here is an example of some output from my query:</p>
  
  <pre><code>ROW_NUM | VISIT ID | ORD DESC | ORD NUM | ORD STS | ORD DTIME
  1       | 123456   | INSERT F | 987654  | ACTIVE  | 2013-01-01 10:01:00.000
  2       | 123456   | INSERT F | 987654  | ACTIVE  | 2013-01-01 10:01:00.000
  3       | 123456   | INSERT F | 987654  | ACTIVE  | 2013-01-01 10:01:00.000
  .
  .
  .
  568     | 123456   | INSERT F | 987654  | ACTIVE  | 2013-01-01 10:01:00.000
  </code></pre>
  
  <p>As shown the order number is the same, so I only need the one instance of the order where it is Active, In Progress, Complete, etc. This query is very similar to another one, the only difference is the in how I am asking the question. I feel this newer version would be more accurate provided I get it working.  Here is the query providing the results above:</p>
  
  <pre><code>DECLARE @SD DATETIME
  DECLARE @ED DATETIME
  SET @SD = '2013-06-01';
  SET @ED = '2013-06-30';
  
  WITH [SX FLAG] AS (
      SELECT 
      PV.PtNo_Num AS [VISIT ID]
      , PV.Med_Rec_No AS MRN
      , PV.vst_start_dtime AS ADM
      , PV.vst_end_dtime AS DISCH
      , PV.Days_Stay AS LOS
      , PV.pt_type AS [PT TYPE]
      , PV.hosp_svc AS [HOSP SVC]
      , SO.ord_no AS [ORD NUM]
      , X.[ORD DESC]
      , SO.pty_name AS [PARTY NAME]
      , OSM.ord_sts AS [ORD STS]
      , SOS.prcs_dtime AS [ORD STS TIME]
      , DATEDIFF(DAY,PV.vst_start_dtime,SOS.prcs_dtime) AS [ADM TO ORD STS IN DAYS]
      , MAX(CASE WHEN [ORD DESC] IN ('INSERT FOLEY', 'REMOVE FOLEY') THEN 1 ELSE 0 END)
          OVER (PARTITION BY PV.PTNO_NUM) AS HasInsertRemoveFoley
      , MAX(CASE WHEN ACDV.actv_group = 'OR' THEN 1 ELSE 0 END)
          OVER (PARTITION BY PV.PTNO_NUM) AS HasORTime
  
      FROM smsdss.BMH_PLM_PtAcct_V PV
      JOIN smsmir.sr_ord SO
      ON PV.PtNo_Num = SO.episode_no
      JOIN smsmir.sr_ord_sts_hist SOS
      ON SO.ord_no = SOS.ord_no
      JOIN smsmir.ord_sts_modf_mstr OSM
      ON SOS.hist_sts = OSM.ord_sts_modf_cd
      JOIN smsdss.actv_fct_v AFV
      ON PV.Pt_No = AFV.pt_id
      JOIN smsdss.actv_cd_dim_v ACDV
      ON AFV.actv_cd = ACDV.actv_cd
  
      CROSS APPLY (
          SELECT
              CASE
                  WHEN SO.svc_desc = 'INSERT FOLEY CATHETER' THEN 'INSERT FOLEY'
                  WHEN SO.svc_desc = 'INSERT INDWELLING URINARY CATHETER TO GRAVITY DRAINAGE' THEN 'INSERT FOLEY'
                  WHEN SO.svc_desc = 'REMOVE INDWELLING URINARY CATHETER' THEN 'REMOVE FOLEY'
                  ELSE SO.svc_desc
              END AS [ORD DESC]
              ) X
  
  
      WHERE PV.Adm_Date BETWEEN @SD AND @ED
      AND SO.svc_cd IN ('PCO_REMFOLEY' -- &lt;-- The orders I am looking for
          ,'PCO_INSRTFOLEY'       -- &lt;-- for patients who have had a
          ,'PCO_INSTFOLEY'        -- &lt;-- surgical procedure
          ,'PCO_URIMETER'
          )
       -- I don't want patients who fall into these pt types
      AND PV.hosp_svc NOT IN (
          'DIA'
          ,'DMS'
          ,'EME'
          )
       -- This is supposed to kick out orders that were 'Discontinued'
       -- or orders that were 'Canceled'
      AND SO.ord_no NOT IN (
          SELECT SO.ord_no
  
          FROM smsdss.BMH_PLM_PtAcct_V PV
          JOIN smsmir.sr_ord SO
          ON PV.PtNo_Num = SO.episode_no
          JOIN smsmir.sr_ord_sts_hist SOS
          ON SO.ord_no = SOS.ord_no
          JOIN smsmir.ord_sts_modf_mstr OSM
          ON SOS.hist_sts = OSM.ord_sts_modf_cd
          JOIN smsdss.actv_fct_v AFV -- &lt;-- gets pt activity
          ON PV.Pt_No = AFV.pt_id
          JOIN smsdss.actv_cd_dim_v ACDV -- &lt;-- tells me if pt had OR Time
          ON AFV.actv_cd = ACDV.actv_cd
  
          WHERE OSM.ord_sts = 'DISCONTINUE' -- &lt;-- don't want these orders
          AND SO.svc_cd IN ('PCO_REMFOLEY'  -- &lt;-- to show if they were
          ,'PCO_INSRTFOLEY'                 -- &lt;-- canned / discontinued
          ,'PCO_INSTFOLEY'
          ,'PCO_URIMETER'
          )
      )
  )
  SELECT *
  FROM [SX FLAG]
  WHERE HasInsertRemoveFoley = 1
  AND HasORTime = 1
  </code></pre>
  
  <p>Thank you,</p>