SQLTeam.com | Weblogs | Forums

Union is not displaying all values


#1

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


#2

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

#3

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


#4

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.


#5

rechecking


#6

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