TSQL question

Question

Post reply

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

ParentId ChildId RecordType Open_Closed

1 1 A Closed

1 2 A Closed

1 3 I Closed

1 4 A Closed

2 5 A Open

2 6 A Open

2 7 A Open

2 8 A Open

3 9 A Closed

3 10 I Closed

4 11 A Open

4 12 A Open

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

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 · Answer 2

work.dev

SSC Veteran

Points: 204

August 18, 2011 at 12:08 pm

#1370299

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

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)
Intro to Tally Tables and Functions