Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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.
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

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

 
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 7:33am
Sikaman293
Edited 9-Apr-13 22:01pm
v9
Comments
Maciej Los at 9-Apr-13 12:43pm
   
Are these tables related? On which key?
Sikaman at 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 at 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 at 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 at 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 at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

If the leading zeros need trimming, then why not do a basic join and trim the key values in the WHERE statement?
 
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.
  Permalink  
v3
Comments
Sikaman at 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) at 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 at 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) at 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 at 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) at 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 at 10-Apr-13 6:31am
   
Good job, my 5!
Sikaman at 10-Apr-13 6:15am
   
Marciej, greetings! Just to say thanks for your assistance. Much appreciated! Sikaman
Maciej Los at 10-Apr-13 6:31am
   
You're welcome ;)
Call again!
See my answer.
Sikaman at 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 at 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 at 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 at 10-Apr-13 8:45am
   
Remove "," between a and INNER
Sikaman at 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 at 10-Apr-13 9:09am
   
Thank you, Sikaman. You're to kind. Your words are like honey on my heart.
 
Cheers!
Sikaman at 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 at 10-Apr-13 10:39am
   
I have since solved this problem. Solution: and datediff(year,ServiceDate,getdate()) < = 10
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Solution1 by Nick Fisher looks good now. I think it's very helpful, but i need to point you in the right direction Wink | ;)
 
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[^]
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 587
1 Sergey Alexandrovich Kryukov 479
2 Maciej Los 305
3 Mathew Soji 195
4 Richard MacCutchan 145
0 OriginalGriff 7,356
1 Sergey Alexandrovich Kryukov 6,712
2 DamithSL 5,461
3 Manas Bhardwaj 4,916
4 Maciej Los 4,475


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 10 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100