Comments by Sikaman - CodeProject

Sikaman 10-Apr-13 17:40pm View

Thank you, I have it corrected. Perfectly understood.

Sikaman 10-Apr-13 17:37pm View

Jose, thank you. It works. Much appreciated!

Sikaman 10-Apr-13 13:14pm View

Hi Zoltan, I tried the solution to no avail. This is what I did:

and select distinct MTMRN from (sh-sql01.DataWarehouse-SQLQuery.Exam_SchedProv_Age_v2.sql*)

and this is the message:

Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near '-'.

Sikaman 10-Apr-13 10:39am View

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

Sikaman 10-Apr-13 10:12am View

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 9:03am View

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!

Sikaman 10-Apr-13 8:43am View

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'.

Sikaman 10-Apr-13 6:57am View

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

Sikaman 10-Apr-13 6:15am View

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

Sikaman 10-Apr-13 6:12am View

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.

Sikaman 10-Apr-13 4:52am View

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

Sikaman 10-Apr-13 2:41am View

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 10-Apr-13 1:17am View

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 9-Apr-13 14:41pm View

Deleted

Maciej, thanks for your honest assessment. I am in the process of doing just that right now.
I couldn't locate the "Improve question" widget, so I am putting everything into this comment.
The first select statement (please obtain from above through the "WHERE EXAM='COL' statement) has this data:
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]

produces the following data when executed:

534645 WEINSTEIN M.D.,ROBERT E. 1881645679 1/3/2012 2012-01-03 08:15AM 1/3/12 8:15 PHY ARR 1881645679 N
113298 WEINSTEIN M.D.,ROBERT E. 1881645679 1/3/2012 2012-01-03 08:30AM 1/3/12 8:30 OVT ARR 1881645679
578251 WEINSTEIN M.D.,ROBERT E. 1881645679 1/3/2012 2012-01-03 08:30AM 1/3/12 8:30 OVT ARR 1881645679 N
751847 WEINSTEIN M.D.,ROBERT E. 1881645679 1/3/2012 2012-01-03 09:00AM 1/3/12 9:00 PHY ARR 1881645679 N
88919 WEINSTEIN M.D.,ROBERT E. 1881645679 1/3/2012 2012-01-03 09:15AM 1/3/12 9:15 OVT ARR 1881645679 N
638098 WEINSTEIN M.D.,ROBERT E. 1881645679 1/3/2012 2012-01-03 09:15AM 1/3/12 9:15 OVT ARR 1881645679 N
371643 WEINSTEIN M.D.,ROBERT E. 1881645679 1/3/2012 2012-01-03 09:30AM 1/3/12 9:30 OVT ARR 1881645679 N
757291 WEINSTEIN M.D.,ROBERT E. 1881645679 1/3/2012 2012-01-03 09:30AM 1/3/12 9:30 OVT ARR 1881645679 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 produced here:
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]

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

Sikaman 9-Apr-13 13:13pm View

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.

Sikaman 9-Apr-13 13:01pm View

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.

Sikaman 9-Apr-13 8:50am View

From Sikaman to Om Prakash Pant. Your solution much appreciated! It worked! Is there anyway of personally getting in touch with you...possible negotiated comp. for much anticipated sql queries. cbonsu2@verizon.net

Comments by Sikaman (Top 17 by date)