combining tables - SQL Server Forums

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.

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

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

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?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources