3 queries into one - subquery help required

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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.

SQL

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

SQL

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.

SQL

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:

SQL

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

Posted 9-Apr-13 6:33am

Sikaman

Updated 9-Apr-13 21:01pm

v9

Add a Solution

Comments

Maciej Los 9-Apr-13 12:43pm

Are these tables related? On which key?

Sikaman 9-Apr-13 13:13pm

Yes, these tables are related through the [IDX6], [MRN], AND [mrn] columns. However, the [MRN] and [mrn] columns have leading zeros that have to be trimmed down to 6 digits to have all 3 columns be the same. Joining could then be done after that. Thanks for asking.

Maciej Los 9-Apr-13 14:36pm

This information is not useful at all...
Please, provide more details: the structure of tables, datatype od fields, and relationships between them. Provide example data.
Please, use "Improve question" widget to update your question.

Sikaman 10-Apr-13 1:17am

Hello Maciej, I have improved the question, now titled "3 queries into one, subquery help required", and with some data examples. Please see if it makes sense to you. Thanks for your assistance. Sikaman

Sikaman 10-Apr-13 2:41am

Hello Maciej, it seems you've done some formatting work on the question. Well done! And thank you. You have clearly broken down the question into its component 3 queries and results. My main question is this - can I get just one query to replace all 3. I suspect nested queries, or subqueries as it is sometimes called, will be the answer. Either that or a "join" of some sort.
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

Sikaman 9-Apr-13 13:01pm

Yes, these tables are related through the [IDX6], [MRN], AND [mrn] columns. However, the [MRN] and [mrn] columns have leading zeros that have to be trimmed down to 6 digits to have all 3 columns be the same. Joining could then be done after that. Thanks for asking.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nick Fisher (Consultant) · Accepted Answer · 2013-04-09T22:30:00

Solution 1

If the leading zeros need trimming, then why not do a basic join and trim the key values in the WHERE statement?

SQL

select a.MTMRN, a.PatName, a.IDX6, a.ServiceDate, a.ExamName, a.Exam, 
       b.SchedProv, b.SchProvNPI, b.ApptDt, b.ApptDt2, b.ApptTm, b.ApptTm2, 
       b.VisitType, b.STATUS, 
       b.PCPNPI, b.Referring, b.RefNPI, b.Deceased, 
       c.source, c.payer, c.patient, c.dob, c.gender, c.addr1, c.addr2, c.city, 
       c.state, c.zip, c.phone, c.mbrid, c.pcp, 
       c.PastProv, c.PastAppt, c.cd, c.FutProv, c.FutAppt
from   [DataWarehouse].[dbo].[BHMammColPats2] a, 
       INNER JOIN [dbo].formatIDX_PastAppts b 
on     SUBSTRING(a.MTMRN, PATINDEX('%[^0 ]%', a.MTMRN + ' '), LEN(a.MTMRN)) = 
       SUBSTRING(b.MRN , PATINDEX('%[^0 ]%', b.MRN  + ' '), LEN(b.MRN))
       INNER JOIN [dbo].[Full_roster] c
on     SUBSTRING(b.MRN, PATINDEX('%[^0 ]%', b.MRN + ' '), LEN(b.MRN)) = 
       SUBSTRING(c.MRN , PATINDEX('%[^0 ]%', c.MRN  + ' '), LEN(c.MRN))
where  a.Exam='COL' 
and    b.SchedProv='WEINSTEIN M.D.,ROBERT E.'
and    b.STATUS='ARR'
and    (b.VisitType='OVT' OR b.VisitType='AWV' OR b.VisitType='SWV')
and    CAST(b.ApptDt as datetime) > = '2012-01-01 00:00:00.000'
and    CAST(b.ApptDt as datetime) < = '2012-12-31 00:00:00.000'
and    c.datediff(year,dob,getdate()) between 50 and 75

But if these columns contain unique identifiers for your patients, then surely all these columns should be of the SAME type and length, and it would make much more sense to trim the values to the right length in your code before they are inserted into the database.

Posted 9-Apr-13 22:30pm

Nick Fisher (Consultant)

Updated 9-Apr-13 23:40pm

v3

Comments

Sikaman 10-Apr-13 4:52am

Nick, thanks for your assistance. Wouldn't know the result until some 4 hours later when I get in the office. By the way, not to push things so much, how would the required "join" statements look like. I must admit I am a beginner. Thanks, Sikaman

Nick Fisher (Consultant) 10-Apr-13 5:46am

The results should return a long series of about 36 columns - just imagine combining the results of your previous 3 statements side by side.

Maciej Los 10-Apr-13 4:53am

I'm afraid that above query will produce duplicates, because of no join specification (LEFT, RIGHT, INNER, OUTER).

There are more "where"'s:
1) WHERE Exam='COL'
2) 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'
3) WHERE datediff(year,dob,getdate()) between 50 and 75

Nick Fisher (Consultant) 10-Apr-13 5:43am

Thanks for pointing that out Marciej. I was so focused on doing the join I forgot about the rest of the where's! I've updated it now to a solution which I think should work.

Sikaman 10-Apr-13 6:12am

Nick and Marciej, gentlemen, sincere thanks! Nick just by looking at the solution presented, I can tell it will work. I wouldn't be able to run it till some 3 hours later when I get in the office. You've captured all the requirements for Dr. Weinstein's patients to have had a colonoscopy, been seen last year 2010, and for those patients only between ages 50 to 75. Excellent! I work in a large hospital setting and assistance may be required every now and then. ok if I may cbonsu2@verizon.net may be worth your while. Thank you.

Nick Fisher (Consultant) 10-Apr-13 6:47am

ok, glad to help. I worked in a hopital myself for many years so I'm used to these sorts of queries. :)

Maciej Los 10-Apr-13 6:31am

Good job, my 5!

Sikaman 10-Apr-13 6:15am

Marciej, greetings! Just to say thanks for your assistance. Much appreciated! Sikaman

Maciej Los 10-Apr-13 6:31am

You're welcome ;)
Call again!
See my answer.

Sikaman 10-Apr-13 6:57am

Marciej, sincere thanks! Those sites will definitely be helpful. I will be doing a lot of studying today. Please stay tuned in about 3 hours when I run Nick's query. I will inform you. ok to email as well if okay with you. Sikaman

Maciej Los 10-Apr-13 7:08am

OK, i'll be in touch. Please, do not publish your email, because of spam robots. Write a comment to this and i'll promise to see it.

Sikaman 10-Apr-13 8:43am

Thank you for being so savvy. I just run the query and this is the error message received: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'INNER'.

Maciej Los 10-Apr-13 8:45am

Remove "," between a and INNER

Sikaman 10-Apr-13 9:03am

Maciej, you certainly are the guru. I did remove that ',', and it gripped with this message:Msg 4121, Level 16, State 1, Line 1
Cannot find either column "c" or the user-defined function or aggregate "c.datediff", or the name is ambiguous.
So I removed the 'c' in front of 'datediff', and presto, it worked!!!
If spam robots will get to posted emails, how about providing you with a phone number...not that I mind the spam calls. I can deal with them personally.
Sincere, thanks by the way!

Maciej Los 10-Apr-13 9:09am

Thank you, Sikaman. You're to kind. Your words are like honey on my heart.

Cheers!

Sikaman 10-Apr-13 10:12am

Morning, Marciej: Not sure how your site works, but if you're up and about...
I am adding one additional line to further extract data under a 'ServiceDate' on the dataset that was returned. I want to extract those between now, current date, and 10 years ago.
Typical data in the 'ServiceDate' column looks like the following:
2006-08-11 01:04:00.000
2006-08-11 01:04:00.000
2006-08-11 01:04:00.000
2006-08-11 01:04:00.000
2003-03-14 12:10:00.000
2003-03-14 12:10:00.000
2003-05-23 10:04:00.000
2003-05-23 10:04:00.000

10 years ago from today will be 2003-04. This means if what I wrote executes correctly, I will have 6 returns out of the 8.
This is what I wrote: and datediff(year,ServiceDate,getdate()) between Now() and 10.
Query returned an error message: Msg 195, Level 15, State 10, Line 22
'Now' is not a recognized built-in function name.

What am I doing incorrectly. Is there a better line to extract this. Thanks

Sikaman 10-Apr-13 10:39am

I have since solved this problem. Solution: and datediff(year,ServiceDate,getdate()) < = 10

Maciej Los · Accepted Answer · 2013-04-10T00:31:00

Solution1 by Nick Fisher looks good now. I think it's very helpful, but i need to point you in the right direction ;)

Refer below links:
http://www.w3schools.com/sql/sql_join.asp[^]
http://msdn.microsoft.com/en-us/library/aa196318%28v=sql.80%29.aspx[^]
http://msdn.microsoft.com/en-us/library/ms173815.aspx[^]

Adn finally, see this great article: Visual Representation of SQL Joins[^]