Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 combining tables

Author  Topic 

tjonas
Starting Member

17 Posts

Posted - 2014-12-17 : 10:44:21
How would I combine the following two tables? I was thinking union but each table has a count field that is different.

I would like it to say smith in a row then have completed and no_show as the columns.


COMPLETED AS
(
select 'SMITH' "PROVIDER", count (*) AS COMPLETED
from
(
SELECT PATIENT.PAT_ID, PATIENT.PAT_MRN_ID, PATIENT.PAT_NAME, PATIENT.BIRTH_DATE, PAT_ENC.CONTACT_DATE, ZC_APPT_STATUS.TITLE, CLARITY_SER.PROV_NAME
FROM PAT_ENC
INNER JOIN PATIENT ON PAT_ENC.PAT_ID = PATIENT.PAT_ID
LEFT JOIN PATIENT_3 ON PATIENT.PAT_ID = PATIENT_3.PAT_ID
LEFT JOIN CLARITY_SER ON PAT_ENC.VISIT_PROV_ID = CLARITY_SER.PROV_ID
LEFT JOIN ZC_APPT_STATUS ON PAT_ENC.APPT_STATUS_C = ZC_APPT_STATUS.APPT_STATUS_C
WHERE PAT_ENC.VISIT_PROV_ID = '40818'
AND PAT_ENC.APPT_STATUS_C = '2' --COMPLETED
AND ((PATIENT_3.IS_TEST_PAT_YN IS NULL) OR NOT(PATIENT_3.IS_TEST_PAT_YN = 'Y'))
AND PAT_ENC.ENC_TYPE_C in ('50','101','200','227','280','300','334','335','369','5376','701','702','703','706','708')
AND ((PAT_ENC.CONTACT_DATE >= TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, -3), 'MM') || '/01/' || TO_CHAR(ADD_MONTHS(SYSDATE,-3), 'YYYY'), 'MM/DD/YYYY'))
AND (PAT_ENC.CONTACT_DATE <= TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'MM') || '/' || TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,-1)), 'DD') || '/' || TO_CHAR(ADD_MONTHS(SYSDATE,-1), 'YYYY'), 'MM/DD/YYYY')))
)
),
NO_SHOW AS
(
SELECT 'SMITH' "PROVIDER", COUNT (*) AS NO_SHOW
FROM
(
SELECT PATIENT.PAT_ID, PATIENT.PAT_MRN_ID, PATIENT.PAT_NAME, PATIENT.BIRTH_DATE, PAT_ENC.CONTACT_DATE, ZC_APPT_STATUS.TITLE, CLARITY_SER.PROV_NAME
FROM PAT_ENC
INNER JOIN PATIENT ON PAT_ENC.PAT_ID = PATIENT.PAT_ID
LEFT JOIN PATIENT_3 ON PATIENT.PAT_ID = PATIENT_3.PAT_ID
LEFT JOIN CLARITY_SER ON PAT_ENC.VISIT_PROV_ID = CLARITY_SER.PROV_ID
LEFT JOIN ZC_APPT_STATUS ON PAT_ENC.APPT_STATUS_C = ZC_APPT_STATUS.APPT_STATUS_C
WHERE PAT_ENC.VISIT_PROV_ID = '40818'
AND PAT_ENC.APPT_STATUS_C = '4' --NO SHOW
AND ((PATIENT_3.IS_TEST_PAT_YN IS NULL) OR NOT(PATIENT_3.IS_TEST_PAT_YN = 'Y'))
AND PAT_ENC.ENC_TYPE_C in ('50','101','200','227','280','300','334','335','369','5376','701','702','703','706','708')
AND ((PAT_ENC.CONTACT_DATE >= TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, -3), 'MM') || '/01/' || TO_CHAR(ADD_MONTHS(SYSDATE,-3), 'YYYY'), 'MM/DD/YYYY'))
AND (PAT_ENC.CONTACT_DATE <= TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'MM') || '/' || TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,-1)), 'DD') || '/' || TO_CHAR(ADD_MONTHS(SYSDATE,-1), 'YYYY'), 'MM/DD/YYYY')))
)
)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-17 : 11:18:02
From what I see, COMPLETED and NO_SHOW have two columns (PROVIDER and count(*)) each. You should be able to UNION them. Be sure to give the derived column count(*) an alias.
Go to Top of Page

tjonas
Starting Member

17 Posts

Posted - 2014-12-17 : 11:38:29
quote:
Originally posted by gbritton

From what I see, COMPLETED and NO_SHOW have two columns (PROVIDER and count(*)) each. You should be able to UNION them. Be sure to give the derived column count(*) an alias.



I am getting just a '1' in the completed column and don't have a no_show column. What am I doing incorrectly?

select 'smith' "PROVIDER", count (*) AS COMPLETED
from completed
union all
select 'smith' "PROVIDER", COUNT (*) AS NO_SHOW
from no_show
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-17 : 11:41:27
When you UNION, the column names for the first query are used for the other queries in the UNION. Do you want:


PROVIDER COMPLETED NO_SHOW


Then you'll need a JOIN, not a UNION
Go to Top of Page

tjonas
Starting Member

17 Posts

Posted - 2014-12-17 : 11:45:45
quote:
Originally posted by gbritton

When you UNION, the column names for the first query are used for the other queries in the UNION. Do you want:


PROVIDER COMPLETED NO_SHOW


Then you'll need a JOIN, not a UNION



Yes that is what I want. Is that an outer join? What do I join to?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-18 : 09:49:52
Could be outer or inner join. What do you join on? Since you only have one line coming from each query, you can join on anything, even "ON 1=1". This is nice to do with CTEs:


with q1 as (first query),
q2 as (second query)
select 'smith' "PROVIDER"
, (select count (*) from q1) AS COMPLETED
, (select count (*) from q2) AS NO_SHOW
Go to Top of Page
   

- Advertisement -