TSQL question

• work.dev

  SSC Veteran

  Points: 204

  More actions

  August 18, 2011 at 9:29 am

  #246546

  create Table TestTable

  (

  ParentId [varchar](1) NOT NULL,

  ChildId [varchar](2) NOT NULL,

  RecordType [varchar](1) NOT NULL,

  )

  INSERT INTO TestTable(ParentId,ChildId,RecordType) Values('1','1','A')

  INSERT INTO TestTable(ParentId,ChildId,RecordType) Values('1','2','A')

  INSERT INTO TestTable(ParentId,ChildId,RecordType) Values('1','3','I')

  INSERT INTO TestTable(ParentId,ChildId,RecordType) Values('1','4','A')

  INSERT INTO TestTable(ParentId,ChildId,RecordType) Values('2','5','A')

  INSERT INTO TestTable(ParentId,ChildId,RecordType) Values('2','6','A')

  INSERT INTO TestTable(ParentId,ChildId,RecordType) Values('2','7','A')

  INSERT INTO TestTable(ParentId,ChildId,RecordType) Values('2','8','A')

  INSERT INTO TestTable(ParentId,ChildId,RecordType) Values('3','9','A')

  INSERT INTO TestTable(ParentId,ChildId,RecordType) Values('3','10','I')

  INSERT INTO TestTable(ParentId,ChildId,RecordType) Values('4','11','A')

  INSERT INTO TestTable(ParentId,ChildId,RecordType) Values('4','12','A')

  Would like to create a query that satisfies below conditions

  If any child record related to a parent record has a RecordType Of 'I'

  I would like all the child records within the parent record flagged as 'Closed'

  If all child records related to a parent record has a RecordType Of 'A'

  I would like all the child records within the parent record flagged as 'Open'

  In the example data above i want the result to look like below

  ParentIdChildIdRecordTypeOpen_Closed

  11AClosed

  12AClosed

  13IClosed

  14AClosed

  25AOpen

  26AOpen

  27AOpen

  28AOpen

  39AClosed

  310IClosed

  411AOpen

  412AOpen

  Parent Ids 1 and 3 have one child that has a record Type of 'I' so all the children are marked as 'Closed'

  Any help is appreciated.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 18, 2011 at 9:42 am

  #1370214

  i think this does what you wanted;

  i grabbed a subquery of the items that would be marked as closed....everything else would still be open, right?

  SELECT

  #TestTable.ParentId,

  #TestTable.ChildId,

  #TestTable.RecordType,

  CASE

  WHEN MyCloseItems.ParentId IS NULL

  THEN 'Open'

  ELSE 'Closed'

  END AS Open_Closed

  FROM #TestTable

  LEFT OUTER JOIN

  (SELECT

  ParentId,RecordType

  FROM #TestTable

  WHERE RecordType = 'I'

  GROUP BY ParentId,RecordType) AS MyCloseItems

  ON #TestTable.ParentId = MyCloseItems.ParentId

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• work.dev

  SSC Veteran

  Points: 204

  More actions

  August 18, 2011 at 12:08 pm

  #1370299

  Awesome. This was exactly what i was looking for. Thanks you!!!

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  August 18, 2011 at 9:05 pm

  #1370507

  Ummm... so what do you want done if the following row is add to the rows you posted?

  INSERT INTO TestTable(ParentId,ChildId,RecordType) Values('8','20','I')

  --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)