sgmunson - Tuesday, September 26, 2017 9:43 AM

Maxer - Monday, September 25, 2017 1:17 PM

I have an issue where I wrote a triangle join and then realized that when doing some testing and I want to move away from that solution.
This is a vendor data structure so I can't change anything about it.

I do want to make a view to show the "desired output". 

The issue is this:
I have a table, the table holds information on containers, (think like large cargo boxes down to pallets, down to individual boxes).

One of the fields is a DueDate field.  The issue is this:
In the table data DueDate is only populated for the top level (1=top and  2 lower than 1, 3 lower than 2, 4 = lowest, etc...)  and it is inherited for every level BELOW that....
unless a different date is populated.  Then it stops inheriting the parent and that level becomes the parent and it continues down.

Sort of like a rolling DOWN , roll down until you hit new value then use the new and roll it down .
It rolls down as a ragged hierarchy and each "." in the ContainerID indicates a deeper level.  So while multiple rows are level 3, they have different heritage.  Z7281.2.A1 shares the same progenitor (Z7281) but have different children: Z7281.2.A1 stops at Depth 3 but it's sibling Z7281.2.A3 continues on to have additional children: Z7281.2.A3.78,79,80,etc...  (ragged hierarchy)

 

Note on the left only a few of those dates are populated.  On the right the color coding matches the "roll down".

I had tried joining on depth 1  (as anchor) values using LIKE 'ContainerID ' + '%' so it would join to the top level and iterate down through all the children.  *EDIT: forgot to say I did the like AND depth = depth+1 from the anchored CTE

However, that triangle joins out of control.

I am wondering what other approaches would be?

I could do a cursor loop but I REALLY wanted to avoid that and just handle it in a view.  I was thinking LEAD LAG or previous with the windowing functions?

Thoughts or suggestions?

Thanks!!!

Create table and sample data attached.

EDIT: Resorted sample data via ID:

Just an FYI, but you appear to have data that disagrees with itself in the last couple of rows, where the right-most column should be 2018/07/01, right?