Select Statement

• riyadh_01

  SSC Veteran

  Points: 250

  More actions

  April 4, 2015 at 4:09 pm

  #398281

  Hi,

  I have a table called CPRDLkupMedical, with a sample of the data as shown below;

  MedicalCodeIDSnomedSnomedDescriptionEmiscodeReadcodeTerm

  1115006772671000006117NULL75161Insertion of moveable orthodontic appliance

  2140004NULLNULLH121zChronic pharyngitis NOS

  3140004556141000006119NULLH121zChronic pharyngitis NOS

  432610000061071529013EMISATTF4042Blind hypertensive eye

  5416118004656NULLPCDSTUnspecified

  664254 600NULLPCDVVUnspecified

  Now I have another table called 'EMISDBMaster.dbo.Diary' a sample of data is shown below;

  DairyGuidDigest SnomedSnomedDescriptionEmiscodeReadcodeTerm

  tqrws445 416118004 656 NULL PCDST_12A Unspecified

  hsgdfgfd 416118004 656 NULL PCDST_AH Unspecified

  63g45ded 416118004 656 NULL PCDST_GH Unspecified

  agdfdgsff 3261000006107 2 EMISATT_RS NULL NULL

  vsdfsdfsf 3261000006107 2 EMISATT_A NULL NULL

  yyyyycfh 140004 NULL NULL H121zChronic pharyngitis NOS

  Now, I have my select statement as below;

  SELECT EMISDBMaster.dbo.Diary.DiaryGuidDigest, dbo.EmisPatient.PatientID, dbo.EmisConsultation.ConsultationID, dbo.EmisStaff.StaffID,

  EMISDBMaster.dbo.Diary.EffectiveDateTime AS DiaryEntryDate, dbo.CPRDLkupMedical.MedicalCodeID

  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

  I want the result to be as follows;

  DairyGuidDigest PatientID ConsultationID StaffID DiaryEntryDate MedicalCodeID

  tqrws445 56 677 3 12/12/01 5

  hsgdfgfd 87 622 2 14/09/00 5

  63g45ded 100 7236 3 11/07/99 5

  agdfdgsff 6 178 1 26/08/06 4

  vsdfsdfsf 65 893 3 25/10/77 4

  yyyyycfh 567 876 2 16/12/87 2

  So what is really happening is if the first 5 characters of readcode = PCDST and snomed = 416118004 in the EmisDBmaster.dbo.Diary then its MedicalCodeId = 5

  And if the first 7 characters of Emiscode = EMISATT and snomed = 3261000006107 in the EmisDbmaster.dbo.Diary then its medicalCodeid = 4

  I have a case statement as ;

  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

  How can implement this in my select statement to receive the results I need.

  Thank you so much

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  April 6, 2015 at 7:20 am

  #1788902

  What have you tried? It looks like you have already written the case expression you need. Just put it in as a column. In the meantime you really need to start using aliases in your queries. 3 and 4 part naming in the list of columns has been deprecated (not soon enough in my opinion) and you are going to have to use aliases. Given the very length names you have it would reduce the amount of code by about 50% and maintenance will become a LOT easier when you aren't forced to wade through such long names over and over.

  Here is an example of your query after using aliases and some formatting.

  SELECT d.DiaryGuidDigest

  , p.PatientID

  , c.ConsultationID

  , s.StaffID

  , d.EffectiveDateTime AS DiaryEntryDate

  , m.MedicalCodeID

  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

  AND d.SnomedCTConceptId IS NULL

  )

  AND Isnull(d.EmisCode, N'(novalue)') = Isnull(m.Emiscode, N'(novalue)')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'(novalue)') = Isnull(m.Term, N'(novalue)')

  AND Isnull(d.ReadCode, N'(novalue)')COLLATE SQL_Latin1_General_CP1_CS_AS = Isnull(m.ReadCode, N'(novalue)')COLLATE SQL_Latin1_General_CP1_CS_AS

  LEFT OUTER JOIN dbo.EmisConsultation c ON d.ConsultationGuidDigest = c.ConsultationGuidDigest

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/