Help - Need to fill in parent lot tool info for given table

Question

Help - Need to fill in parent lot tool info for given table

haloway13

SSC Rookie

Points: 47
More actions
August 8, 2012 at 11:45 am

#279345

Some background information is in order:
3rd -7th columns refer to tools that lots are processed through, in that order.
I need to be able to fill in the child's information from the parent for the prior steps the parent has gone through.
I appreciate any help you can give me.
Thank you,
Tim
P.S. I was trying to use the code blocks for the insertion of sample data table etc, and it kept giving me an error. Am I not using the code blocks properly? I resorted to attaching it as a text file.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You have sample data but there's no expected results. I'm not sure if I understood correctly, here's what I imagine you're trying to do (but it might be completely wrong).

Please provide expected results and the work you have done so we won't be shooting in the dark.

SELECT child.LotID,

child.Parent,

CASE WHEN LEN( parent.VBS) > 0 THEN parent.VBS ELSE child.VBS END AS VBS,

CASE WHEN LEN( parent.MOL) > 0 THEN parent.MOL ELSE child.MOL END AS MOL,

CASE WHEN LEN( parent.CRC) > 0 THEN parent.CRC ELSE child.CRC END AS CRC,

CASE WHEN LEN( parent.CBD) > 0 THEN parent.CBD ELSE child.CBD END AS CBD,

CASE WHEN LEN( parent.TCO) > 0 THEN parent.TCO ELSE child.TCO END AS TCO

FROM #mytable child

LEFT

JOIN #mytable parent ON child.Parent = parent.LotID

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

thall 8753 (8/8/2012)
Some background information is in order:
3rd -7th columns refer to tools that lots are processed through, in that order.
I need to be able to fill in the child's information from the parent for the prior steps the parent has gone through.
I appreciate any help you can give me.
Thank you,
Tim
P.S. I was trying to use the code blocks for the insertion of sample data table etc, and it kept giving me an error. Am I not using the code blocks properly? I resorted to attaching it as a text file.

For some reason there are some people that the code blocks won't work. I can use them so I will post your code.

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable

(

LotID nvarchar(12),

Parent nvarchar(12),

VBS char,

MOL char,

CRC char,

CBD char,

TCO char

)

INSERT INTO #mytable

(LotID,Parent,VBS,MOL,CRC,CBD,TCO)

SELECT 'EC22001','','1','1','1','3','' UNION ALL

SELECT 'EC22001D','EC22001','','1','','','' UNION ALL

SELECT 'EC22001I','EC22001','','','','','2' UNION ALL

SELECT 'TC22002','','1','2','1','2','2' UNION ALL

SELECT 'TC22002B','TC22002','','2','','','' UNION ALL

SELECT 'TC22002F','TC22002','','','','2','2' UNION ALL

SELECT 'EC22003','','1','4','4','','' UNION ALL

SELECT 'EC22003A','EC22003','','4','4','','' UNION ALL

SELECT 'EC22004','','1','4','','','' UNION ALL

SELECT 'EC22004G','EC22004','','4','4','','' UNION ALL

SELECT 'EC23001','','1','1','1','3','2' UNION ALL

SELECT 'TC23002','','1','2','1','2','2' UNION ALL

SELECT 'EC23003','','1','1','1','3','2' UNION ALL

SELECT 'TC23004','','1','2','2','3','2' UNION ALL

SELECT 'TC23005','','1','2','2','2','2' UNION ALL

SELECT 'TC23005B','TC23005','','2','','','' UNION ALL

SELECT 'EC23006','','1','1','4','2','2' UNION ALL

SELECT 'TC24001','','1','2','2','2','2' UNION ALL

SELECT 'TC24001B','TC24001','','2','','','' UNION ALL

SELECT 'EC24002','','1','1','','','' UNION ALL

SELECT 'TC25001','','1','2','2','2','2' UNION ALL

SELECT 'TC25002','','1','2','1','3','2' UNION ALL

SELECT 'TC25003','','1','2','2','2','2' UNION ALL

SELECT 'TC25003G','TC25003','','','','','1' UNION ALL

SELECT 'EC25004','','1','1','1','','' UNION ALL

SELECT 'EC25004C','EC25004','','1','','','' UNION ALL

SELECT 'EC25005','','1','1','1','2','2' UNION ALL

SELECT 'TC25006','','1','2','2','2','2' UNION ALL

SELECT 'TC25006D','TC25006','','2','','','' UNION ALL

SELECT 'TC25006E','TC25006D','','2','','','' UNION ALL

SELECT 'TC25006I','TC25006','','','','3','1' UNION ALL

SELECT 'EC25007','','1','4','4','','' UNION ALL

SELECT 'EC25008','','1','','','','' UNION ALL

SELECT 'TC26001','','1','2','2','','' UNION ALL

SELECT 'EC26003','','1','1','1','3','1' UNION ALL

SELECT 'EC26003B','EC26003','','1','','','' UNION ALL

SELECT 'TC26004','','1','2','2','3','2' UNION ALL

SELECT 'TC26004E','TC26004','','','','2','2' UNION ALL

SELECT 'TC26004G','TC26004','','','1','','' UNION ALL

SELECT 'TC26004H','TC26004','','','1','','' UNION ALL

SELECT 'EC27001','','1','1','1','','' UNION ALL

SELECT 'EC27002','','1','','','','' UNION ALL

SELECT 'EC27003','','1','2','','','' UNION ALL

SELECT 'EC27003B','EC27003','','2','','','' UNION ALL

SELECT 'EC27004','','1','','','','' UNION ALL

SELECT 'EC27005','','1','1','1','2','2' UNION ALL

SELECT 'TC27006','','1','2','2','2','2' UNION ALL

SELECT 'TC27006E','TC27006','','','','3','2' UNION ALL

SELECT 'EC27007','','1','2','','','' UNION ALL

SELECT 'EC27007B','EC27007','','2','','','' UNION ALL

SELECT 'TC27008','','1','2','2','2','1' UNION ALL

SELECT 'EC28001','','1','1','2','','' UNION ALL

SELECT 'EC28001A','EC28001','','1','1','2','1' UNION ALL

SELECT 'EC28001C','EC28001A','','1','','','' UNION ALL

SELECT 'TC28002','','1','2','2','3','1' UNION ALL

SELECT 'TC28002B','TC28002','','2','','','' UNION ALL

SELECT 'TC28002E','TC28002','','','','2','2' UNION ALL

SELECT 'TC28002H','TC28002E','','','','','1' UNION ALL

SELECT 'TC28002I','','','','','2','1' UNION ALL

SELECT 'EC28003','','1','2','4','','' UNION ALL

SELECT 'EC28003D','EC28003','','2','','','' UNION ALL

SELECT 'EC28003G','EC28003','','','','','' UNION ALL

SELECT 'EC28003J','EC28003G','','','','2','2' UNION ALL

SELECT 'TC28004','','1','2','2','','' UNION ALL

SELECT 'TC28004B','TC28004','','2','','','' UNION ALL

SELECT 'TC28004E','TC28004','','','2','2','1' UNION ALL

SELECT 'TC28004F','TC28004E','','','2','','' UNION ALL

SELECT 'EC29001','','1','','','','' UNION ALL

SELECT 'TC29002','','1','2','1','3','' UNION ALL

SELECT 'TC29002B','TC29002','','2','','','' UNION ALL

SELECT 'EC29003','','1','1','1','3','2' UNION ALL

SELECT 'EC29004','','1','2','1','3','1' UNION ALL

SELECT 'EC29004B','EC29004','','2','','','' UNION ALL

SELECT 'EC29005','','1','1','1','3','2' UNION ALL

SELECT 'EC29005B','EC29005','','1','','','' UNION ALL

SELECT 'EC29006','','1','2','2','2','1' UNION ALL

SELECT 'EC29006B','EC29006','','2','','','' UNION ALL

SELECT 'EC29006E','EC29006','','','','2','1' UNION ALL

SELECT 'EC29006G','EC29006E','','','','3','1' UNION ALL

SELECT 'EC29006I','EC29006','','','','2','1' UNION ALL

SELECT 'TC30001','','1','2','2','3','' UNION ALL

SELECT 'TC30001B','TC30001','','2','','','' UNION ALL

SELECT 'EC30002','','1','2','4','','' UNION ALL

SELECT 'EC30003','','1','1','1','','' UNION ALL

SELECT 'EC30003B','EC30003','','1','','','' UNION ALL

SELECT 'TC30004','','1','2','1','','' UNION ALL

SELECT 'TC30004B','TC30004','','2','','','' UNION ALL

SELECT 'TC30004E','TC30004','','','','','' UNION ALL

SELECT 'TC31001','','1','2','','','' UNION ALL

SELECT 'LC31001','','1','1','','','' UNION ALL

SELECT 'TC31001B','TC31001','','2','','','' UNION ALL

SELECT 'TC31005','','1','2','','','' UNION ALL

SELECT 'EC32001','','1','','','','' UNION ALL

SELECT 'EC32002','','1','','','','' UNION ALL

SELECT 'EC32003','','1','','','','' UNION ALL

SELECT 'TC32004','','1','','','','' UNION ALL

SELECT 'TC32005','','1','','','','' UNION ALL

SELECT 'TC32006','','1','','','','' UNION ALL

SELECT 'TC32007','','1','','','','' UNION ALL

SELECT 'TC32008','','1','','','','' UNION ALL

SELECT 'TC32009','','1','','','',''

_______________________________________________________________

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/

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

One thing I would recommend is that you should ALWAYS specify the length of your character data. If you don't it will assume the default length. Do you know what the default length of a varchar datatype is? Did you know that it can change depending on the context of where it is declared or being used? That is why you need to specify the length. If your data is longer than the default length it will be truncated with no warnings. To clarify here is verbiage from BOL. For the record you have these all effectively declared as varchar(1). That means you are using 3 bytes to hold 1 byte of data for each column in each row of the table. I know disc space is cheap and this is just a temp table but imagine if this is a data warehouse and you have 3 million rows? That is 10 bytes per rows times 3 millions rows. That is over 28MB of wasted space for just 1 table.

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

From the data you posted it looks like maybe you should be using a tinyint instead of a varchar?

_______________________________________________________________

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/

haloway13 SSC Rookie Points: 47 More actions · Answer 4

Sean,

First, I would like to thank you for taking the time to answer my post.

I have attached expected output in an excel spreadsheet. In the expected tab, I have the tool id that should be pulled down from the parent to populate the the childs processed tool id.

of particular interest is LotID TC28002 where the children follow different paths through the tools.

Situations in which it would not be appropiate to get the id from the parent is when lets say

Lot 123 goes through VBS1, Mol1, CRC1

Child 123A gets created and goes through CBD2, then gets scrapped and no further tools were used

Lot 123 continues through CBD1, and TCO1

Thank you for pointing out char length always being specified. Very detailed explanation and goes a long way to educating me on the topic. Thanks!

And yes, tinyint is more appropriate. You are also correct in that I am not worried about those details at this time.

At this point I would be embarrassed to post any of the work I have already done. I feel completely lost on this one...

haloway13 SSC Rookie Points: 47 More actions · Answer 5

Also,

I was using Firefox 14.01 while trying to post a code block...

Using IE 8 seems to work just fine.

Select * from test

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

thall 8753 (8/8/2012)
Sean,
First, I would like to thank you for taking the time to answer my post.
I have attached expected output in an excel spreadsheet. In the expected tab, I have the tool id that should be pulled down from the parent to populate the the childs processed tool id.
of particular interest is LotID TC28002 where the children follow different paths through the tools.
Situations in which it would not be appropiate to get the id from the parent is when lets say
Lot 123 goes through VBS1, Mol1, CRC1
Child 123A gets created and goes through CBD2, then gets scrapped and no further tools were used
Lot 123 continues through CBD1, and TCO1
Thank you for pointing out char length always being specified. Very detailed explanation and goes a long way to educating me on the topic. Thanks!
And yes, tinyint is more appropriate. You are also correct in that I am not worried about those details at this time.
At this point I would be embarrassed to post any of the work I have already done. I feel completely lost on this one...

That helps. To keep things simple lets look at just LotID 'EC22001'. I put what I think is the parent and the two children of the LotID into the following temp table:

create table #MyResult

(

LotID nvarchar(12),

Parent nvarchar(12),

VBS char,

MOL char,

CRC char,

CBD char,

TCO char

)

insert #MyResult

select 'EC22001', '', '1', '1', '1', '3', '' union all

select 'EC22001D', 'EC22001', '1', '1', '', '', '' union all

select 'EC22001I', 'EC22001', '1', '1', '1', '3', '2'

select * from #MyResult

select * from #mytable where lotid = 'EC22001' or parent = 'EC22001'

This now shows your desired results followed by the original data. How do you know the "order". The only thing I can see would be the LotID column. Remember that in sql a table is an unordered set. The concept of previous and next only works when there is an order.

The reason I put my suggestions in another post was because it really didn't seem totally relevant to the topic at hand but was something I felt was important to say. 🙂

_______________________________________________________________

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/

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

thall 8753 (8/8/2012)
Also,
I was using Firefox 14.01 while trying to post a code block...
Using IE 8 seems to work just fine.
Select * from test

There seems to be no real rhyme or reason from what I can tell. I use FF 14.0.1 and it works fine for me.

_______________________________________________________________

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/

haloway13 SSC Rookie Points: 47 More actions · Answer 8

Order of how a lot can travel through the tools?

Left to right: VBS ... TCO

Maybe I don't understand the question.

Tim

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

thall 8753 (8/9/2012)
Order of how a lot can travel through the tools?
Left to right: VBS ... TCO
Maybe I don't understand the question.
Tim

From your original post:

I need to be able to fill in the child's information from the parent for the prior steps the parent has gone through.

Again keeping with just our LotID of EC22001 we see the following three rows in #mytable.

select * from #mytable where lotid = 'EC22001' or parent = 'EC22001'

From that table how do we know what the prior steps are? It is pretty straight forward that the parent is first but how do we know the order of the two children?

_______________________________________________________________

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/

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

thall 8753 (8/9/2012)
Order of how a lot can travel through the tools?
Left to right: VBS ... TCO
Maybe I don't understand the question.
Tim

Looking at this again I am thinking that maybe these are unrelated? I think I am understanding now what you need. Give me a bit and I will see if I can work something up.

_______________________________________________________________

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/

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Borrowing from the fine example Luis provided this produces the results you are looking at least with the sample data.

SELECT child.LotID,

child.Parent,

isnull(CASE WHEN LEN( child.VBS) > 0 OR LEN( child.MOL) > 0 OR LEN( child.CRC) > 0 OR LEN( child.CBD) > 0 OR LEN( child.TCO) > 0 THEN parent.VBS ELSE '' END, child.VBS) AS VBS,

isnull(CASE WHEN LEN( child.MOL) > 0 OR LEN( child.CRC) > 0 OR LEN( child.CBD) > 0 OR LEN( child.TCO) > 0 THEN parent.MOL ELSE '' END, child.MOL) AS MOL,

isnull(CASE WHEN LEN( child.CRC) > 0 OR LEN( child.CBD) > 0 OR LEN( child.TCO) > 0 THEN parent.CRC ELSE '' END, child.CRC) AS CRC,

isnull(CASE WHEN LEN( child.CBD) > 0 OR LEN( child.TCO) > 0 THEN parent.CBD else '' END, child.CBD) AS CBD,

CASE WHEN LEN( child.TCO) > 0 THEN child.TCO else '' END AS TCO

FROM #mytable child

LEFT

JOIN #mytable parent ON child.Parent = parent.LotID

Hopefully that will work. If not somebody else is going to have to help. I am outta here for a long weekend and won't be back until at least Thursday next week.

_______________________________________________________________

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/

haloway13 SSC Rookie Points: 47 More actions · Answer 12

insert #MyResult

select 'TC30001','','1','2','2','3','' UNION ALL

select 'TC30001B','TC30001','1','2','','',''

select * from #mytable where lotid = 'TC30001' or parent = 'TC30001'

select * from #MyResult where lotid = 'TC30001' or parent = 'TC30001'

Is also indicative how what I would like to see.

Maybe if I describe the data a little more:

Physical splitting is taking place. A parent can have any number of splits, each creating a child. The tools used for that child are always the parents set up to the point in which the lot will split, then the child can take its own path. The children that do not appear finished can also have their own splits. The tool history for each child will comprise their own history, plus whatever is missing to the left of its first history.

I guess not everything that is describable has a solution?

Tim

haloway13 SSC Rookie Points: 47 More actions · Answer 13

That worked well for children, but grandchildren are left as null.

Is there an elegant way to get the info from the parents parent when the parent is null also? For that matter, a solution is needed for an unlimited number of generations.

Hrrm, after thinking about it a little bit more its seems to me that to be accurate the first child needs to be filled in iteratively down the chain to the youngest grandchild, otherwise nulls get copied from that childs parent.

Thank you,

Tim