Electronic Patient Records - using SQL

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

SQL Server 2008 Forums

Transact-SQL (2008)

Electronic Patient Records - using SQL

Author

Topic

happygolucky30
Starting Member

3 Posts

Posted - 2012-09-17 : 07:33:43

Hi - I am a medical practitioner, trying to get information from thousands of patient records, stored in the 'comments' column of the Progress Note table from our electronic patient records system, in order to gather a sample representative for research purposes.

Due to the sensitivity of the request and the issues with patient identifiable information, I only want to pull the necessary data from this 'comments' column, without pulling additional and un-necessary information which would be a breach of patient data.

Within the 'comments' column (which is a free text column) is included loads of text which is written about each patient when they see their Consultant, such as diagnosis, medication and general notes.

For my research, I only need to extract those patients where this 'comments' column includes the words:

Mirtazapine, Shcizophrenia and bi-polar

These three words must be present in each of the comments columns per patient to be considered valid for research purposes. So, if one of the patients comments includes two out of the three key words, I will not use it (and I don't wish to see it, as it will be deemed an un-necessary extraction) - I only want to see any 'comments' which have ALL of these 3 key words.

Can anyone help??

happygolucky30
Starting Member

3 Posts

Posted - 2012-09-17 : 07:48:09

My SQL so far is this, but I have only got so far as bringing back anything in the comments column which is like 'Mirtazapine' - this brings back too many results though and I need to focus it down to the three keywords I highlighted above in the initial question i.e. they ALL have to be present in each comment to be a valid participant:

select

ClientID, DateAndTime,VerifyDate, Enteredby, team, NoteType, comment

from (SELECT PrgProgressNote.[SequenceID],PrgProgressNote.[ClientID] ,[DateAndTime] ,[UserID],genperson.surname + ' ' + genperson.firstname [User]
,row_number () over (partition by PrgProgressNote.clientid, notenum order by subnum desc) rowcode
,[NoteNum] ,[SubNum] ,GenUserPrgNoteType.Codedescription [NoteType],[VerifyDate],[VerifyUserID] ,
genperson.addressline1 FirstAddressline, genperson.addressline2 SecondAddressline,
gp.surname + ' ' + gp.firstname [Enteredby]
,team1.codedescription Team, NoteText Comment
FROM [PrgProgressNote]
Left join GenHCP On GenHCP.GenHCPCode = userID
left join genperson on genperson.genpersonid = userID
left join Genspecialty on Genspecialty.code = GenHCP.MainGenSpecialtyCode
left join GenServiceTeam as GST1 on primaryteam = GST1.code
left join GenUserPrgNoteType on GenUserPrgNoteType.Code = notetype
left join genperson gp on gp.genpersonid = enteredby
left join GenServiceTeam team1 on GenHCP.PrimaryTeam = team1.Code

)data where
rowcode = 1
and DateAndTime >= '01/01/2011'
and VerifyDate is not null
and Comment like '%mirtazapine%'

order by 2

Mike Jackson
Starting Member

37 Posts

Posted - 2012-09-17 : 08:30:01

You could create an new field to hold a int count. Then run your query and add 1 to it when you have a match. Like....

Update yourtable
set wordcnt=wrdcnt=1
FROM [PrgProgressNote]
Left join GenHCP On GenHCP.GenHCPCode = userID
left join genperson on genperson.genpersonid = userID
left join Genspecialty on Genspecialty.code = GenHCP.MainGenSpecialtyCode
left join GenServiceTeam as GST1 on primaryteam = GST1.code
left join GenUserPrgNoteType on GenUserPrgNoteType.Code = notetype
left join genperson gp on gp.genpersonid = enteredby
left join GenServiceTeam team1 on GenHCP.PrimaryTeam = team1.Code
Where
rowcode = 1
and DateAndTime >= '01/01/2011'
and VerifyDate is not null
and Comment like '%mirtazapine%'

Then rerun your query where
wrdcnt=3

Mike

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-09-17 : 08:33:45

where
rowcode = 1
and DateAndTime >= '01/01/2011'
and VerifyDate is not null
and Comment like '%mirtazapine%'

and Comment like '%Shcizophrenia%'
and Comment like '%bi-polar%'

happygolucky30
Starting Member

3 Posts

Posted - 2012-09-17 : 08:47:54

Hi Stepson - I tried this already but it returns any comments which have either 1, 2 or all 3 of these key words in the comments column as I think it's applying this statement to ALL comments rather than for EACH of the comments returned to each patient on an individual basis.

For example, one of the comments might mention 1 and 2 of the key words, but it will still be returned in the query if another query further down has the 3rd key word in it.

I need the query to just return those comments for each individual patient which have all 3 key words in each comment.

Thank you!

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-09-19 : 03:15:09

a patient have only one raw comment ? or is 1 patient to many comments ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources