Select Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2015-04-03 : 14:04:02

Hi,

I have created the following table using the insert statement below;
In this insert statement, for emiscode and readcode, am trying to create one medicalcodeid for all emiscode with EMISATT (first 7 characters) and snomed as stated. Same principal in Readcode.

Therefore, for example
Emiscode Snomed
EMISATT_245 3261000006107
EMISATT_8483 3261000006107
EMISATT_7333 3261000006107

All the above will have only one medicalcodeid as all the 3 above will be seen as same.

INSERT INTO dbo.CPRDLkupMedical (Snomed,SnomedDescription,EmisCode,ReadCode,Term)
SELECT DISTINCT Snomed,SnomedDescription,
case WHEN left(Emiscode,7) = 'EMISATT' AND snomed = 3261000006107 
THEN 'EMISATT' 
ELSE Emiscode
END AS Emiscode,
CASE WHEN left(Readcode,5)= 'PCSDT' AND snomed = 416118004 
THEN 'PCSDT' 
ELSE Readcode
END AS Readcode, 
Term
FROM dbo.CPRDALLMedicalCodes

Now for example, I have results as;
N/B: MedicalCodeId is an incremental value

MedicalCodeId Snomed SnomedDescription EmisCode ReadCode Term
1 1 56 NULL PCSDT Administrator

Now, When I try to do the following I rightfully get a NULL MedicalCodeID. The reason is am matching it with the original Readcode at EMISDBMaster.dbo.Diary.ReadCode which is PCSDT_ATT (therefore it will pull a NULL medicalcodeID because I have created the CPRDLkupMedical with a Readcode as PCSDT. Not as PCSDT_ATT)

SELECT     EMISDBMaster.dbo.Diary.DiaryGuidDigest, dbo.EmisPatient.PatientID, dbo.EmisConsultation.ConsultationID, dbo.EmisStaff.StaffID, 
                      EMISDBMaster.dbo.Diary.EffectiveDateTime AS DiaryEntryDate, dbo.CPRDLkupMedical.MedicalCodeID, EMISDBMaster.dbo.Diary.DurationTerm AS Duration
FROM         EMISDBMaster.dbo.Diary INNER JOIN
                      dbo.EmisPatient ON EMISDBMaster.dbo.Diary.PatientGuidDigest = dbo.EmisPatient.PatientGuidDigest INNER JOIN
                      dbo.EmisStaff ON EMISDBMaster.dbo.Diary.AuthorisingUserRoleGuidDigest = dbo.EmisStaff.StaffGuidDigest LEFT OUTER JOIN
                      dbo.CPRDLkupMedical ON (EMISDBMaster.dbo.Diary.SnomedCTConceptId = dbo.CPRDLkupMedical.Snomed OR
                      EMISDBMaster.dbo.Diary.SnomedCTConceptId IS NULL AND EMISDBMaster.dbo.Diary.SnomedCTConceptId IS NULL) AND ISNULL(EMISDBMaster.dbo.Diary.EmisCode, N'(novalue)')
                       = ISNULL(dbo.CPRDLkupMedical.Emiscode, N'(novalue)')COLLATE SQL_Latin1_General_CP1_CS_AS AND (EMISDBMaster.dbo.Diary.SnomedCTDescriptionId = dbo.CPRDLkupMedical.SnomedDescription OR
                      EMISDBMaster.dbo.Diary.SnomedCTDescriptionId IS NULL AND dbo.CPRDLkupMedical.SnomedDescription IS NULL) AND ISNULL(EMISDBMaster.dbo.Diary.Term, N'(novalue)') = ISNULL(dbo.CPRDLkupMedical.Term, N'(novalue)') 
                      AND ISNULL(EMISDBMaster.dbo.Diary.ReadCode, N'(novalue)')COLLATE SQL_Latin1_General_CP1_CS_AS = ISNULL(dbo.CPRDLkupMedical.ReadCode, N'(novalue)')COLLATE SQL_Latin1_General_CP1_CS_AS LEFT OUTER JOIN
                      dbo.EmisConsultation ON EMISDBMaster.dbo.Diary.ConsultationGuidDigest = dbo.EmisConsultation.ConsultationGuidDigest

Now, I want to update the query (2) so that it sees PCDST_ATT (readcode) as PCSDT and hence pulls the medicalcodeid = 1
So same CASE statement used in the INSERT used in query (1)is implemented in the select statement.

Any help will be highly appreciated.

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-03 : 16:09:12

OK -- Here is a revised query. See if it does it for you. I made some changes:

1. I aliased the table names for readability
2. I highlighted one line that I think is redundant as is. (I suspect you want something else there)
3. I noticed inconsistent use of the COLLATE clause. Not sure if that is a problem for you or not.
4. You should replace the ISNULL functions in the join predicates with testing for equality or both columns null. Since ISNULL is not SARGable
5. I added a CASE statement in the JOIN clause that may do what you want


SELECT D.DiaryGuidDigest
	,P.PatientID
	,C.ConsultationID
	,S.StaffID
	,D.EffectiveDateTime AS DiaryEntryDate
	,M.MedicalCodeID
	,D.DurationTerm AS Duration
FROM EMISDBMaster.dbo.Diary D
INNER JOIN dbo.EmisPatient P
   ON D.PatientGuidDigest = P.PatientGuidDigest
INNER JOIN dbo.EmisStaff S
   ON D.AuthorisingUserRoleGuidDigest = S.StaffGuidDigest
LEFT OUTER JOIN dbo.CPRDLkupMedical M ON (
		D.SnomedCTConceptId = M.Snomed
		OR D.SnomedCTConceptId IS NULL
		-- ****The next line is redundant****
		AND D.SnomedCTConceptId  IS NULL
		)
	AND ISNULL(D.EmisCode, N'') = ISNULL(M.Emiscode, N'') COLLATE SQL_Latin1_General_CP1_CS_AS
	AND (
		D.SnomedCTDescriptionId = M.SnomedDescription
		OR D.SnomedCTDescriptionId IS NULL
		AND M.SnomedDescription IS NULL
		)
	AND ISNULL(D.Term, N'') = ISNULL(M.Term, N'')
	-- AND ISNULL(D.ReadCode, N'') = ISNULL(M.ReadCode, N'') COLLATE SQL_Latin1_General_CP1_CS_AS
	AND 1 = CASE
		WHEN D.Readcode LIKE 'PCDST%' AND M.snomed =416118004
			THEN 1
		END
LEFT OUTER JOIN dbo.EmisConsultation C
   ON D.ConsultationGuidDigest = C.ConsultationGuidDigest

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2015-04-03 : 17:20:20

But here you are not comparing the Readcode with the one in CPRDLkupMedical table - why?

AND 1 = CASE
WHEN D.Readcode LIKE 'PCDST%' AND M.snomed =416118004
THEN 1
END

N:B
The readcode is 200000 records few thousands have the like PCDST others have different values.. So its not ONLY PCDST - so please show me how I can match it to CPRDLkupMedical table.

So the above is only an example but we can have data as

Readcode Snomed
GHY 726272
RHKL 7272
PTC 655
PCDST_1 62627
PCDST_72 7273

I tried the above code - it brings loads of NULL values for medicalcodeID - there should be NO NULL value as all combination exist in table CPRDLkUpMedical table

Thanks

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2015-04-03 : 20:09:37

Pls help

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-04 : 07:52:08

Change d.read code to m.read code

Did you fix the problem I pointed out?

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2015-04-04 : 11:52:16

First and foremost thanks gbritton - much appreciated

Let me explain further - please see sample of the cprdLkupmedical table

MedicalCodeID	Snomed	SnomedDescription	Emiscode	Readcode	Term
1	115006	772671000006117	NULL	75161	Insertion of moveable orthodontic appliance
2	140004	NULL	NULL	H121z	Chronic pharyngitis NOS
3	140004	556141000006119	NULL	H121z	Chronic pharyngitis NOS
4	193003	1409014	NULL	G221	Benign hypertensive renal disease
5	199004	1418011	NULL	L467	Hypogalactia
6	220000	NULL	NULL	N0662	Unspecified monoarthritis of the upper arm
7	220000	NULL	NULL	N0664	Unspecified monoarthritis of the hand
8	220000	NULL	NULL	N0665	Unspecified monoarthritis of the pelvic region and thigh
9	220000	NULL	NULL	N0666	Unspecified monoarthritis of the lower leg
10	3261000006107	1529013	EMISATT	F4042	Blind hypertensive eye
11	416118004	656	NULL	PCDST	Unspecified
12	64254    	600	NULL	PCDVV	Unspecified

Now in EMISDBMaster.dbo.Diary I have the following records (its a sample);

Snomed	SnomedDescription	Emiscode	Readcode	Term
416118004       656     NULL		PCDST_12A       Unspecified
416118004	656	NULL            PCDST_AH        Unspecified
416118004	656	NULL            PCDST_R3	Unspecified
416118004	656	NULL            PCDST_A3	Unspecified

Now all the above records should pull the MedicalCodeID = 11 (from CPRDLkupMedical) as the first 5 characters in
READCODE are PCDST and they have all same snomed = 416118004

(It should also be noted that I am assuming the snomeddescription,emiscode and term in both tables (CPRDLkupMedical & EMISDBMaster.dbo.Diary)match.

I hope am clearer now..

I changed the code to the below;


SELECT D.DiaryGuidDigest
	,P.PatientID
	,C.ConsultationID
	,S.StaffID
	,D.EffectiveDateTime AS DiaryEntryDate
	,M.MedicalCodeID
	,D.DurationTerm AS Duration
FROM EMISDBMaster.dbo.Diary D
INNER JOIN dbo.EmisPatient P
   ON D.PatientGuidDigest = P.PatientGuidDigest
INNER JOIN dbo.EmisStaff S
   ON D.AuthorisingUserRoleGuidDigest = S.StaffGuidDigest
LEFT OUTER JOIN dbo.CPRDLkupMedical M ON (
		D.SnomedCTConceptId = M.Snomed
		OR D.SnomedCTConceptId IS NULL
		-- ****The next line is redundant****
		AND D.SnomedCTConceptId  IS NULL
		)
	--AND ISNULL(D.EmisCode, N'') = ISNULL(M.Emiscode, N'') COLLATE SQL_Latin1_General_CP1_CS_AS
	AND 1 = CASE
		WHEN M.Emiscode LIKE 'EMISATT%' AND M.snomed =3261000006107
			THEN 1
		END
	AND (
		D.SnomedCTDescriptionId = M.SnomedDescription
		OR D.SnomedCTDescriptionId IS NULL
		AND M.SnomedDescription IS NULL
		)
	AND ISNULL(D.Term, N'') = ISNULL(M.Term, N'')
	-- AND ISNULL(D.ReadCode, N'') = ISNULL(M.ReadCode, N'') COLLATE SQL_Latin1_General_CP1_CS_AS
	AND 1 = CASE
		WHEN M.Readcode LIKE 'PCDST%' AND M.snomed =416118004
			THEN 1
		END
LEFT OUTER JOIN dbo.EmisConsultation C
   ON D.ConsultationGuidDigest = C.ConsultationGuidDigest

I still receive MedicalCodeID as NULLS I also changed the d.readcode to m readcode.

Note: I have also added the Emiscode bit...

Any idea why it is still not working i.e., I receive NULL values.

Many Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-04 : 14:15:00

Hmmmm,

Well the problem I pointed out a few posts ago is still there (the redundant line). Plus you have this:


AND 1 = CASE
		WHEN M.Emiscode LIKE 'EMISATT%' AND M.snomed =3261000006107
			THEN 1
		END

which is only satisfied by medicalcodeid=10.

and this:


AND 1 = CASE
		WHEN M.Readcode LIKE 'PCDST%' AND M.snomed =416118004
			THEN 1
		END

is only satisfied by medical codeid = 11. Since both cannot be true at once, you get nulls.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2015-04-04 : 15:13:41

Now is there anyway we can accomodate both cases.

OK - I ll remove the redundant line but the logic wont work anyway. As I have to have both cases within the select statement as what I did in the INSERT statement in post #1

Please help me.. Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-04 : 22:35:15

Try or not and

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2015-04-05 : 03:25:09

I tried with readcode only and removed the redundant line. I still have NULLs.

Please help me need to deliver piece of work but cant continue unless l sort out this query.

Thanks

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2015-04-05 : 13:09:21

Please help me

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-05 : 13:32:17

Here's what I meant by OR instead of AND"


SELECT D.DiaryGuidDigest
	,P.PatientID
	,C.ConsultationID
	,S.StaffID
	,D.EffectiveDateTime AS DiaryEntryDate
	,M.MedicalCodeID
	,D.DurationTerm AS Duration
FROM Diary D
INNER JOIN dbo.EmisPatient P
   ON D.PatientGuidDigest = P.PatientGuidDigest
INNER JOIN dbo.EmisStaff S
   ON D.AuthorisingUserRoleGuidDigest = S.StaffGuidDigest
LEFT OUTER JOIN cprdLkupmedical M ON (
		D.SnomedCTConceptId = M.Snomed
		OR D.SnomedCTConceptId IS NULL
		-- ****The next line is redundant****
		AND M.Snomed  IS NULL
		)
	AND ISNULL(D.EmisCode, N'') = ISNULL(M.Emiscode, N'') COLLATE SQL_Latin1_General_CP1_CS_AS
	AND (
		D.SnomedCTDescriptionId = M.SnomedDescription
		OR D.SnomedCTDescriptionId IS NULL
		AND M.SnomedDescription IS NULL
		)
	AND ISNULL(D.Term, N'') = ISNULL(M.Term, N'')

	AND 
	(
		D.Readcode LIKE 'PCDST%' AND M.snomed =416118004
	 OR M.Emiscode LIKE 'EMISATT%' AND M.snomed =3261000006107
	)
	
LEFT OUTER JOIN dbo.EmisConsultation C
   ON D.ConsultationGuidDigest = C.ConsultationGuidDigest

Note that, you can still get NULL results depending on what is in your Staff and Patient tables.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2015-04-05 : 16:05:09

Still not working .. Received medicalCodeId as NULLS and the staff and patient tables do have match results.

Lets tackle this another way -

Lets change;


 ISNULL(EMISDBMaster.dbo.Diary.EmisCode, N'(novalue)')
                       = ISNULL(dbo.CPRDLkupMedical.Emiscode, N'(novalue)')COLLATE SQL_Latin1_General_CP1_CS_AS

So that, if in EMISDBMaster.dbo.Diary.EmisCode the Emiscode = EMISATT_UI or EMISATT_YGH (for example)then it should read this as EMISATT and match it with the Emiscode in dbo.CPRDLkupMedical.

Let's ONLY do step please.. I think this is all what I need first to sort out...

Thank you gribtton for all your time and efforts - There hasnt been an easter break for me ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-06 : 03:37:57

Same problem as this topic?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=200672

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources