Hello, I need assistance with combining these separate queries into one. It may be simplistic, but they work. Goal is to replace all 3 with a single query. I suspect the solution may involve nested queries, or subqueries. Or probably even a "join" of some sort.
SELECT [PatName]
,[MTMRN]
,[IDX6]
,[ServiceDate]
,[ExamName]
,[Exam]
FROM [DataWarehouse].[dbo].[BHMammColPats2]
WHERE Exam='COL'
SELECT * FROM dbo.formatIDX_PastAppts
WHERE SchedProv='WEINSTEIN M.D.,ROBERT E.'
AND STATUS='ARR'
AND (VisitType='OVT' OR VisitType='AWV' OR VisitType='SWV')
AND CAST(ApptDt as datetime) > = '2012-01-01 00:00:00.000'
AND CAST(ApptDt as datetime) < = '2012-12-31 00:00:00.000'
SELECT * FROM [dbo].[Full_roster]
WHERE datediff(year,dob,getdate()) between 50 and 75
I get 3 different sections in the query screen when this is run, maybe because of the 3 different select statements. I would prefer to see only one.
[EDIT]
The first query
SELECT [PatName]
,[MTMRN]
,[IDX6]
,[ServiceDate]
,[ExamName]
,[Exam]
FROM [DataWarehouse].[dbo].[BHMammColPats2]
WHERE Exam='COL'
produces this data below:
GOERES,CLAUDIA 274156 54642 8/21/07 14:08 SCREENING MAMMOGRAM MAM
GOERES,CLAUDIA 274156 54642 9/6/07 10:05 MAMMOGRAM LEFT MAM
GOERES,CLAUDIA 274156 54642 10/9/07 2:36 MAMMOGRAM LEFT MAM
GOURDIN,DOROTHY L 720360 187427 4/1/11 11:47 DIGITAL SCREENING MAM W/CAD MAM
GOURDIN,DOROTHY L 720360 187427 10/22/08 13:26 SCREENING MAMMOGRAM MAM
GOURDIN,DOROTHY L 720360 187427 10/22/08 13:26 CAD, MAMMO SCREENING MAM
GOURDIN,DOROTHY L 720360 187427 3/16/10 13:55 DIGITAL SCREENING MAM W/CAD MAM
ALSEN,EILEEN 711519 240290 4/23/07 3:16 DIAGNOSTIC COLONOSCOPY COL
PETRONELLI,DONNA M 80263 113950 2/6/12 10:23 COLONOSCOPY AND BIOPSY COL
GORDON,JAMES 362306 160313 3/14/08 4:22 DIAGNOSTIC COLONOSCOPY COL
GARDNER,JANICE 704849 115117 5/9/03 3:45 SIGMOIDOSCOPY AND BIOPSY COL
GARDNER,JANICE 704849 115117 5/11/04 1:06 DIAGNOSTIC COLONOSCOPY COL
with this column headers:
PatName IDX6 MTMRN ServiceDate ExamName Exam
Please take note of the MTMRN values. They will be the link, or the primary key for each patient.
The second query:
SELECT [MRN]
,[SchedProv]
,[SchProvNPI]
,[ApptDt]
,[ApptDt2]
,[ApptTm]
,[ApptTm2]
,[VisitType]
,[STATUS]
,[PCPNPI]
,[Referring]
,[RefNPI]
,[Deceased]
FROM [DataWarehouse].[dbo].[formatIDX_PastAppts]
WHERE SchedProv='WEINSTEIN M.D.,ROBERT E.'
AND STATUS='ARR'
AND (VisitType='OVT' OR VisitType='AWV' OR VisitType='SWV')
AND CAST(ApptDt as datetime) > = '2012-01-01 00:00:00.000'
AND CAST(ApptDt as datetime) < = '2012-12-31 00:00:00.000'
produces the following data when executed:
534645 WEINSTEIN M.D.,ROBERT E. 188 1/3/2012 2012-01-03 08:15AM 1/3/12 8:15 PHY ARR 188 N
113298 WEINSTEIN M.D.,ROBERT E. 188 1/3/2012 2012-01-03 08:30AM 1/3/12 8:30 OVT ARR
188
578251 WEINSTEIN M.D.,ROBERT E. 188 1/3/2012 2012-01-03 08:30AM 1/3/12 8:30 OVT ARR 188 N
751847 WEINSTEIN M.D.,ROBERT E. 188 1/3/2012 2012-01-03 09:00AM 1/3/12 9:00 PHY ARR 188 N
88919 WEINSTEIN M.D.,ROBERT E. 188 1/3/2012 2012-01-03 09:15AM 1/3/12 9:15 OVT ARR 188 N
638098 WEINSTEIN M.D.,ROBERT E. 188 1/3/2012 2012-01-03 09:15AM 1/3/12 9:15 OVT ARR 188 N
371643 WEINSTEIN M.D.,ROBERT E. 188 1/3/2012 2012-01-03 09:30AM 1/3/12 9:30 OVT ARR 188 N
757291 WEINSTEIN M.D.,ROBERT E. 1881645679 1/3/2012 2012-01-03 09:30AM 1/3/12 9:30 OVT ARR 188 N
with column headers:
MRN SchedProv SchProvNPI ApptDt ApptDt2 ApptTm ApptTm2 VisitType STATUS PCPNPI Referring RefNPI Deceased<br />
And the final quesry from the
Full_roster
:
SELECT [source]
,[payer]
,[patient]
,[dob]
,[gender]
,[addr1]
,[addr2]
,[city]
,[state]
,[zip]
,[phone]
,[mrn]
,[mbrid]
,[pcp]
,[pastprov]
,[pastappt]
,[cd]
,[futprov]
,[futappt]
FROM [DataWarehouse].[dbo].[Full_roster]
WHERE datediff(year,dob,getdate()) between 50 and 75
when executed, produces the following data:
PAYER BC AARON CLAIRE Mar 9 1963 12:00AM F 46 EVERETT ST MIDDLEBORO MA 23460000 508-946-1970 403815 98240450000 WEINSTEIN R NULL NULL NULL NULL NULL
IDX MEDICARE ABAIRE JANET Mar 22 1948 12:00AM F 988 CENTRE ST BROCKTON MA 2302 508-584-0450 330847 HANNA A HANNA A 15:00.0 Kenn Donna HANNA A 30:00.0
IDX MASSHEALTH ABANTO DE ROJAS MARIA Sep 24 1936 12:00AM F 293 LYNN RD BROCKTON MA 2302 508-933-4249 854051 MAYERS F MAYERS F 45:00.0 Jefferson Lydia GEORGAKIS A 15:00.0
PAYER NHP ABATE JR JAMES Jan 21 1995 12:00AM M 5.0-823-1e+0 671179 NHP0171458 WEINSTEIN R BARRETT K 30:00.0 Piecewicz Julie WEINSTEIN R 45:00.0
PAYER NHP ABATE CLAIRE Jan 27 1961 12:00AM F 5.0-823-1e+0 549515 NHP0072156 WEINSTEIN R WEINSTEIN R 15:00.0 NULL NULL NULL
PAYER NHP ABATE JAMES Jan 24 1956 12:00AM M 5.0-823-1e+0 666396 NHP0148059 WEINSTEIN R WEINSTEIN R 00:00.0 NULL NULL NULL
with column headers:
source payer patient dob gender addr1 addr2 city state zip phone mrn mbrid pcp PastProv PastAppt cd FutProv FutAppt<br />
All 3 select statements (and queries) work. I will like to have it all in one, as one query. A subquery or join may do the trick, but I believe the solution lies in creation of a subquery.
The common column or unique identifier in all 3 datasets is the values in [MTMRN] of the first query (you will find these in the 2nd column), the [MRN] of the second query (you will find these in the 1st column), and the [mrn] of the third query (you will find these in the 12th column). All those are unique patient numbers.
Thank you kindly and let me know if you have any questions. Regards, Sikaman