Union is not displaying all values

Hi everyone,

I know this was a question I asked before but after re verifying the results
My union for p02 is displaying 14 values when it should be showing 23...

example I could run

SELECT
PD.NAME_LAST + ' ' + PD.NAME_FIRST AS ACCOUNT_NAME,
PD.STATUS_CODE, PD.PATIENT_CODE , PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date_2 PD
WHERE
PD.STATUS_CODE LIKE 'P02'
AND PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2015-08-01', 120) AND CONVERT(DATETIME, '2015-08-31', 120)

and it will display 23 values... but results differ from the union...

SELECT X.ACCOUNT_NAME, X.STATUS_CODE, SUM(CASE WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2014 THEN 1 ELSE 0 END) AS FY2014, SUM(CASE WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2015 THEN 1 ELSE 0 END) AS FY2015, SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2014-08-01', 120) AND CONVERT(DATETIME, '2014-08-31', 120) THEN 1 ELSE 0 END)AS FY2015MONTH, SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2015-08-01', 120) AND CONVERT(DATETIME, '2015-08-31', 120) THEN 1 ELSE 0 END) AS FY2016MONTH, SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2014-07-01', 120) AND CONVERT(DATETIME, '2014-08-31', 120) THEN 1 ELSE 0 END) AS FY2015YTD, SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2015-07-01', 120) AND CONVERT(DATETIME, '2015-08-31', 120) THEN 1 ELSE 0 END) AS FY2016YTD FROM

(SELECT
PD.NAME_FULL AS ACCOUNT_NAME, PD.STATUS_CODE, PD.PATIENT_CODE , PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date_2 PD
WHERE
PD.STATUS_CODE LIKE 'HL1'

UNION
SELECT
PD.NAME_FULL, PD.STATUS_CODE, PD.PATIENT_CODE, PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date_2 PD
WHERE PD.STATUS_CODE LIKE 'P06' OR PD.STATUS_CODE LIKE 'P08'

UNION ALL -- union all is not helping me retrieve all values...

SELECT
PD.NAME_LAST + ' ' + PD.NAME_FIRST, PD.STATUS_CODE, PD.PATIENT_CODE , PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date_2 PD
WHERE
PD.STATUS_CODE LIKE 'P02'

UNION

SELECT
RB.ORGANIZATION_NAME, PD.STATUS_CODE, PD.PATIENT_CODE, PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date_2 PD INNER JOIN
RES_BASIC RB ON RB.RESOURCE_ID = PD.ASSOCIATED_FACILITY_ID
WHERE RB.ORGANIZATION_NAME IS NOT NULL
AND PD.STATUS_CODE LIKE 'P%'

UNION

SELECT
CASE WHEN PD.NAME_FULL IS NOT NULL THEN 'Consumer' END , PD.STATUS_CODE, PD.PATIENT_CODE , PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date_2 PD
WHERE PD.STATUS_CODE LIKE 'P01'

) X INNER JOIN PT_Prospect_Date_2 PD ON X.PROSPECT_ADMIT_DATE = PD.PROSPECT_ADMIT_DATE AND X.PATIENT_CODE = PD.PATIENT_CODE

WHERE

PD.PROSPECT_ADMIT_DATE >= '20130701' AND PD.PROSPECT_ADMIT_DATE <= '20160630'

GROUP BY
X.STATUS_CODE,
X.ACCOUNT_NAME

HAVING NOT ( SUM(CASE WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2014 THEN 1 ELSE 0 END) = 0 AND
SUM(CASE WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2015 THEN 1 ELSE 0 END) = 0 )

ORDER BY
X.STATUS_CODE

any help would be greatly appreciated
stressful problem :frowning:

Thanks in advance!

Matt

These are two completely different queries. Why the results be even remotely similar?

Please format your code!

SELECT PD.NAME_LAST + ' ' + PD.NAME_FIRST AS ACCOUNT_NAME
      , PD.STATUS_CODE
      , PD.PATIENT_CODE
      , PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date_2 PD
WHERE PD.STATUS_CODE LIKE 'P02'
      AND PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2015-08-01', 120)
            AND CONVERT(DATETIME, '2015-08-31', 120)

SELECT X.ACCOUNT_NAME
      , X.STATUS_CODE
      , SUM(CASE 
                  WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2014
                        THEN 1
                  ELSE 0
                  END) AS FY2014
      , SUM(CASE 
                  WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2015
                        THEN 1
                  ELSE 0
                  END) AS FY2015
      , SUM(CASE 
                  WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2014-08-01', 120)
                              AND CONVERT(DATETIME, '2014-08-31', 120)
                        THEN 1
                  ELSE 0
                  END) AS FY2015MONTH
      , SUM(CASE 
                  WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2015-08-01', 120)
                              AND CONVERT(DATETIME, '2015-08-31', 120)
                        THEN 1
                  ELSE 0
                  END) AS FY2016MONTH
      , SUM(CASE 
                  WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2014-07-01', 120)
                              AND CONVERT(DATETIME, '2014-08-31', 120)
                        THEN 1
                  ELSE 0
                  END) AS FY2015YTD
      , SUM(CASE 
                  WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2015-07-01', 120)
                              AND CONVERT(DATETIME, '2015-08-31', 120)
                        THEN 1
                  ELSE 0
                  END) AS FY2016YTD
FROM (
      SELECT PD.NAME_FULL AS ACCOUNT_NAME
            , PD.STATUS_CODE
            , PD.PATIENT_CODE
            , PD.PROSPECT_ADMIT_DATE
      FROM PT_Prospect_Date_2 PD
      WHERE PD.STATUS_CODE LIKE 'HL1'
      
      UNION
      
      SELECT PD.NAME_FULL
            , PD.STATUS_CODE
            , PD.PATIENT_CODE
            , PD.PROSPECT_ADMIT_DATE
      FROM PT_Prospect_Date_2 PD
      WHERE PD.STATUS_CODE LIKE 'P06'
            OR PD.STATUS_CODE LIKE 'P08'
      
      UNION ALL -- union all is not helping me retrieve all values...
      
      SELECT PD.NAME_LAST + ' ' + PD.NAME_FIRST
            , PD.STATUS_CODE
            , PD.PATIENT_CODE
            , PD.PROSPECT_ADMIT_DATE
      FROM PT_Prospect_Date_2 PD
      WHERE PD.STATUS_CODE LIKE 'P02'
      
      UNION
      
      SELECT RB.ORGANIZATION_NAME
            , PD.STATUS_CODE
            , PD.PATIENT_CODE
            , PD.PROSPECT_ADMIT_DATE
      FROM PT_Prospect_Date_2 PD
      INNER JOIN RES_BASIC RB
            ON RB.RESOURCE_ID = PD.ASSOCIATED_FACILITY_ID
      WHERE RB.ORGANIZATION_NAME IS NOT NULL
            AND PD.STATUS_CODE LIKE 'P%'
      
      UNION
      
      SELECT CASE 
                  WHEN PD.NAME_FULL IS NOT NULL
                        THEN 'Consumer'
                  END
            , PD.STATUS_CODE
            , PD.PATIENT_CODE
            , PD.PROSPECT_ADMIT_DATE
      FROM PT_Prospect_Date_2 PD
      WHERE PD.STATUS_CODE LIKE 'P01'
      ) X
INNER JOIN PT_Prospect_Date_2 PD
      ON X.PROSPECT_ADMIT_DATE = PD.PROSPECT_ADMIT_DATE
            AND X.PATIENT_CODE = PD.PATIENT_CODE
WHERE PD.PROSPECT_ADMIT_DATE >= '20130701'
      AND PD.PROSPECT_ADMIT_DATE <= '20160630'
GROUP BY X.STATUS_CODE
      , X.ACCOUNT_NAME
HAVING NOT (
            SUM(CASE 
                        WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2014
                              THEN 1
                        ELSE 0
                        END) = 0
            AND SUM(CASE 
                        WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2015
                              THEN 1
                        ELSE 0
                        END) = 0
            )
ORDER BY X.STATUS_CODE

Thanks for the reply

this portion of the union query

UNION ALL -- union all is not helping me retrieve all values...

  SELECT PD.NAME_LAST + ' ' + PD.NAME_FIRST
        , PD.STATUS_CODE
        , PD.PATIENT_CODE
        , PD.PROSPECT_ADMIT_DATE
  FROM PT_Prospect_Date_2 PD
  WHERE PD.STATUS_CODE LIKE 'P02'

was supposed to pull the same results
they are similar its just that I added the Date in the WHERE Clause

PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2015-08-01', 120) AND CONVERT(DATETIME, '2015-08-31', 120)

In the union query I have the dates on the top which are supposed to pull the same values but within a separate column
the FY2016MONTH column

SUM(CASE WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2014 THEN 1 ELSE 0 END) AS FY2014,
SUM(CASE WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2015 THEN 1 ELSE 0 END) AS FY2015,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2014-08-01', 120) AND CONVERT(DATETIME, '2014-08-31', 120) THEN 1 ELSE 0 END)AS FY2015MONTH,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2015-08-01', 120) AND CONVERT(DATETIME, '2015-08-31', 120) THEN 1 ELSE 0 END) AS FY2016MONTH,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2014-07-01', 120) AND CONVERT(DATETIME, '2014-08-31', 120) THEN 1 ELSE 0 END) AS FY2015YTD,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2015-07-01', 120) AND CONVERT(DATETIME, '2015-08-31', 120) THEN 1 ELSE 0 END) AS FY2016YTD

I should get a 1 for every record pulled but its only marking 14 records instead of the 23..

hopefully this helps

Thanks again

Matt

you're mixing union and union all. Union by itself will remove duplicates. maybe that's your problem. execute each portion by hand and check the results. Do the Union/Union all by hand to see the differences.

1 Like

rechecking

Thanks gbritton!
You motivated me and I found the problem
took it apart piece by piece like you said.

ended up being the where and having not clause

Thanks again,

Matt