Query assistance request

Question

Post reply

Query assistance request

rothj

Hall of Fame

Points: 3087
More actions
March 14, 2012 at 9:22 am

#257385

I have a table (JeffTest) that has two fields (Encounter, DocType).
How can I get the Encounter that does not have a DocType of '305'?
I'd expect the result to be 27242361 & 27242379.
Sample:
29182805-504
29182805-400
29182805229
29182805231
29182805305
29182805-503
27242361-400
27242361229
27242361-503
27242361231
27242379149
27242379-400
27242379229
27242379231
27242379-503
27242379-403

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 1

Hi

SELECT

Encounter

FROM

JeffTest

WHERE

Encounter NOT IN ( SELECT Encounter FROM JeffTest WHERE DocType = 305 )

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

rothj Hall of Fame Points: 3087 More actions · Answer 2

Great, thanks, I knew it was easy. I'm still learning.

Many Thanks!

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 3

Andy Hyslop (3/14/2012)
Hi
SELECT
Encounter
FROM
JeffTest
WHERE
Encounter NOT IN ( SELECT Encounter FROM JeffTest WHERE DocType = 305 )
Andy

If you only want to see the Encounter value once in the result set, I'd add the DISTINCT keyword to Andy's great response, as follows:

SELECT DISTINCT

Encounter

FROM

JeffTest

WHERE

Encounter NOT IN ( SELECT Encounter FROM JeffTest WHERE DocType = 305 )

Rob Schripsema
Propack, Inc.

rothj Hall of Fame Points: 3087 More actions · Answer 4

rothj

Hall of Fame

Points: 3087

March 14, 2012 at 9:52 am

#1459312

Thanks Rob, yes that is needed.

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 5

If you only want to see the Encounter value once in the result set, I'd add the DISTINCT keyword to Andy's great response, as follows:

D'oh spot on! Forgot to add the DISTINCT! :Whistling:

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 6

Another way

SELECT Encounter

FROM JeffTest

GROUP BY Encounter

HAVING MAX(CASE WHEN DocType = 305 THEN 1 ELSE 0 END)=0

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

rothj Hall of Fame Points: 3087 More actions · Answer 7

Remember I said I'm still learning?

Can someone show me how to do this without using the temp db (all in one statement)? I know it is a nested select but I'm not clear on the syntax. This solution does work but I want to learn.

Thanks for any insight.

SELECT e.EncounterNo, e.EncntrStartDate, d.DocType, m.MedRecNo, ee.EnrolleeName

INTO #JeffTest --Temp Table

FROM Encounters e (nolock)

JOIN DocsOwners do (nolock) on e.EncntrOwnerId = do.OwnerId

JOIN Documents d (nolock) on do.DocId = d.DocId

JOIN MedicalRecords AS m (nolock) ON e.MedRecOwnerId = m.MedRecOwnerId AND e.MedRecModNum = m.MedRecModNum

JOIN dbo.Enrollees AS ee (nolock) ON m.EnrolleeOwnerId = ee.EnrolleeOwnerId

WHERE EncntrStartDate >= '12/01/2011 00:00:00' and EncntrStartDate &lt= '12/31/2011 23:59:59'

and e.SvcCode= 'EMR'

ORDER BY e.EncntrOwnerId

GO

SELECT DISTINCT EncounterNo, EncntrStartDate, MedRecNo, EnrolleeName

FROM #JeffTest

WHERE

EncounterNo NOT IN ( SELECT EncounterNo FROM #JeffTest WHERE DocType = 305 )

ORDER BY MedRecNo

GO

Hmm, I had to take out the Drop_Temp_Table command from the end to get the 'code' to post.

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 8

You can use a CTE

WITH JeffTest AS (

SELECT e.EncounterNo, e.EncntrStartDate, d.DocType, m.MedRecNo, ee.EnrolleeName

FROM Encounters e (nolock)

JOIN DocsOwners do (nolock) on e.EncntrOwnerId = do.OwnerId

JOIN Documents d (nolock) on do.DocId = d.DocId

JOIN MedicalRecords AS m (nolock) ON e.MedRecOwnerId = m.MedRecOwnerId AND e.MedRecModNum = m.MedRecModNum

JOIN dbo.Enrollees AS ee (nolock) ON m.EnrolleeOwnerId = ee.EnrolleeOwnerId

WHERE EncntrStartDate >= '12/01/2011 00:00:00' and EncntrStartDate <= '12/31/2011 23:59:59'

and e.SvcCode= 'EMR'

)

SELECT DISTINCT EncounterNo, EncntrStartDate, MedRecNo, EnrolleeName

FROM JeffTest

WHERE

EncounterNo NOT IN ( SELECT EncounterNo FROM JeffTest WHERE DocType = 305 )

ORDER BY MedRecNo

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

rothj Hall of Fame Points: 3087 More actions · Answer 9

rothj

Hall of Fame

Points: 3087

March 15, 2012 at 12:48 pm

#1460094

Thank you Mark.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

rothj (3/15/2012)
Remember I said I'm still learning?
Can someone show me how to do this without using the temp db (all in one statement)?

Actually, it's not always a good thing to try to do everything in a single query. You can end up with accidental many-to-many joins and other performance related problems.

If you end up with a "DISTINCT" in your query, such a problem may exist.

I've converted many a report from a grueling 45 minute run to a 3-8 second run by using Temp Tables and other "Divide'n'Conquer" methods.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

brad.mason5 Hall of Fame Points: 3299 More actions · Answer 11

How about this option?

SELECT e.EncounterNo, e.EncntrStartDate, d.DocType, m.MedRecNo, ee.EnrolleeName

FROM Encounters e (NOLOCK)

JOIN DocsOwners do (NOLOCK) ON e.EncntrOwnerId = do.OwnerId

JOIN Documents d (NOLOCK) ON do.DocId = d.DocId

JOIN MedicalRecords AS m (NOLOCK) ON e.MedRecOwnerId = m.MedRecOwnerId AND e.MedRecModNum = m.MedRecModNum

JOIN dbo.Enrollees AS ee (NOLOCK) ON m.EnrolleeOwnerId = ee.EnrolleeOwnerId

WHERE EncntrStartDate >= '12/01/2011 00:00:00' AND EncntrStartDate <= '12/31/2011 23:59:59'

AND e.SvcCode= 'EMR'

AND NOT EXISTS (SELECT 1

FROM Encounters a

WHERE a.e.EncntrOwnerId = e.EncntrOwnerId

AND a.DocType = 305

)

brad.mason5 Hall of Fame Points: 3299 More actions · Answer 12

This above option will not work as DocType is not in Encounters. Using not exists is another option I was shooting for.

SELECT e.EncounterNo, e.EncntrStartDate, d.DocType, m.MedRecNo, ee.EnrolleeName

FROM Encounters e (NOLOCK)

JOIN DocsOwners do (NOLOCK) ON e.EncntrOwnerId = do.OwnerId

JOIN Documents d (NOLOCK) ON do.DocId = d.DocId

JOIN MedicalRecords AS m (NOLOCK) ON e.MedRecOwnerId = m.MedRecOwnerId AND e.MedRecModNum = m.MedRecModNum

JOIN dbo.Enrollees AS ee (NOLOCK) ON m.EnrolleeOwnerId = ee.EnrolleeOwnerId

WHERE EncntrStartDate >= '12/01/2011 00:00:00' AND EncntrStartDate <= '12/31/2011 23:59:59'

AND e.SvcCode= 'EMR'

AND NOT EXISTS (SELECT 1

FROM Encounters

JOIN DocsOwners WITH (NOLOCK)

ON Encounters.EncntrOwnerId = DocsOwners.OwnerId

JOIN Documents WITH (NOLOCK)

ON Documents.DocId = DocsOwners.DocId

WHERE Encounters.EncntrOwnerId = e.EncntrOwnerId

AND Documents.DocType = 305

)