Query-Identifying populations

• boehnc

  Ten Centuries

  Points: 1003

  More actions

  October 25, 2012 at 9:26 am

  #284040

  I have the following table and fields:

  Table: tbl.transactions T

  Fields: T.visitid, T.diagnosiscode

  It looks like this:

  visitid, diagnosiscode

  1111,34.0

  1111,68.0

  1111,34.2

  1112,34.3

  1112,45.0

  1112,23.4

  I need to identify the individuals that have CHF or pneumonia for each visitID. The codes are below.

  DiagnosisCode in ('34.0','34.1','34.2','34.3') as CHF

  DiagnosisCode in ('45.0','45.1','45.2') as Pneumonia

  So it looks something similar to below:

  VisitID,CHF,Pneumonia

  1111,1,0

  1112,1,1

  or

  Visitid,Comorbidity

  1111,CHF

  1112,CHF

  1112,Pne

  whatever is best for SQL integrity..

  Any help is appreciated!

• DiverKas

  SSCrazy

  Points: 2049

  More actions

  October 25, 2012 at 10:12 am

  #1552793

  boehnc (10/25/2012)

  ---

  I have the following table and fields:

  Table: tbl.transactions T

  Fields: T.visitid, T.diagnosiscode

  It looks like this:

  visitid, diagnosiscode

  1111,34.0

  1111,68.0

  1111,34.2

  1112,34.3

  1112,45.0

  1112,23.4

  I need to identify the individuals that have CHF or pneumonia for each visitID. The codes are below.

  DiagnosisCode in ('34.0','34.1','34.2','34.3') as CHF

  DiagnosisCode in ('45.0','45.1','45.2') as Pneumonia

  So it looks something similar to below:

  VisitID,CHF,Pneumonia

  1111,1,0

  1112,1,1

  or

  Visitid,Comorbidity

  1111,CHF

  1112,CHF

  1112,Pne

  whatever is best for SQL integrity..

  Any help is appreciated!

  Smells like school course work. And it looks like you have everything you need. What have you tried?

• CapnHector

  SSCoach

  Points: 16143

  More actions

  October 25, 2012 at 10:27 am

  #1552799

  DiverKas (10/25/2012)

  ---

  boehnc (10/25/2012)

  ---

  I have the following table and fields:

  Table: tbl.transactions T

  Fields: T.visitid, T.diagnosiscode

  It looks like this:

  visitid, diagnosiscode

  1111,34.0

  1111,68.0

  1111,34.2

  1112,34.3

  1112,45.0

  1112,23.4

  I need to identify the individuals that have CHF or pneumonia for each visitID. The codes are below.

  DiagnosisCode in ('34.0','34.1','34.2','34.3') as CHF

  DiagnosisCode in ('45.0','45.1','45.2') as Pneumonia

  So it looks something similar to below:

  VisitID,CHF,Pneumonia

  1111,1,0

  1112,1,1

  or

  Visitid,Comorbidity

  1111,CHF

  1112,CHF

  1112,Pne

  whatever is best for SQL integrity..

  Any help is appreciated!

  Smells like school course work. And it looks like you have everything you need. What have you tried?

  I would have to agree looks like all that is missing is the SELECT. I will point you to Jeff Moden's article on Cross Tabs http://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx which will let you get this output quite easily.

  VisitID,CHF,Pneumonia

  1111,1,0

  1112,1,1

  ---

  For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

  For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

  Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
  Jeff Moden's Cross tab and Pivots Part 1[/url]
  Jeff Moden's Cross tab and Pivots Part 2[/url]