Recursive calculations using Hierarchy function

[quetzco]

I'm working on BOMs and I need to calculate start date and due date of any single node of one tree result of applying Hierarchy function.

I know the due date of the root code and, applying the specific leadtime of the node, I calculate the the start date.

For each subsequent node of the tree, the due date will be exactly the start date of the father node, while the start date will be the due date minus the specific leadime of the node.... and so on till the end of the tree.

Is there any simple way to do this in only one call of the Hierarchy function without any other evaluation on the result?

Thanks

Antonio

[lbreddemann]

This is a rather complex but interesting requirement.

Why don't you provide a table definition (CREATE TABLE), some test data (INSERT commands), the expected result, and how your computation rule formally looks like?

With that, you might get someone to look into how to do this.

[quetzco]

Here is a sample:

CREATE COLUMN TABLE BOM_TESTH -- BOM Header
(FATHER nvarchar(50)
,STARTD DATE -- Start Date
,DUED DATE -- End Date
,LT smallint -- Lead Time
,QTY decimal(19,6) -- Reference quantity
);

CREATE COLUMN TABLE BOM_TESTR -- BOM Rows
(FATHER nvarchar(50)
,SON nvarchar(50)
,STARTD DATE
,DUED DATE
,LT smallint
,QTY decimal(19,6)
);

INSERT INTO BOM_TESTH
(FATHER,STARTD,DUED,LT,QTY)
SELECT 'FATHER01','20200711','20200731',20,1.0 FROM DUMMY;

INSERT INTO BOM_TESTR
(FATHER,SON,STARTD,DUED,LT,QTY)
SELECT 'FATHER01','SON01',null,null,10,1.0 FROM DUMMY;

INSERT INTO BOM_TESTR
(FATHER,SON,STARTD,DUED,LT,QTY)
SELECT 'FATHER01','SON02',null,null,3,1.0 FROM DUMMY;

INSERT INTO BOM_TESTR
(FATHER,SON,STARTD,DUED,LT,QTY)
SELECT 'SON02','SON03',null,null,7,1.0 FROM DUMMY;

INSERT INTO BOM_TESTR
(FATHER,SON,STARTD,DUED,LT,QTY)
SELECT 'SON02','SON04',null,null,3,1.0 FROM DUMMY;

INSERT INTO BOM_TESTR
(FATHER,SON,STARTD,DUED,LT,QTY)
SELECT 'SON04','SON05',null,null,5,1.0 FROM DUMMY;

I've simulated in EXCEL the BoM structure and the dates calculation:

Is enough to know the Due Date of the father (31st of July) to obtain when the operations on the last level son must start (30th of June). In Excel I've applied a simple subtraction of the leadtime at the due dates, regardless the calendar working days...

The HIERARCHY function applied is this:

SELECT
-- System info
hierarchy_rank AS rank
,hierarchy_tree_size AS tree_size
,hierarchy_parent_rank AS parent_rank
,hierarchy_level AS Lvl
,hierarchy_is_cycle AS is_cycle
,hierarchy_is_orphan AS is_orphan
- - user info
,CAST(parent_id AS varchar(50)) AS "Father"
,CAST(node_id AS varchar(50)) AS "Son"
,CAST(StartDate AS Date) AS "StartDate"
,CAST(DueDate AS Date) AS "DueDate"
,CAST(lt AS smallint) AS "LT"
,CAST(Qty AS decimal(19,6)) AS "Qty"
FROM HIERARCHY ( SOURCE (
SELECT CAST(FATHER AS varchar(50)) AS node_id
,CAST(null AS varchar(50)) AS parent_id
,CAST(null AS Date) AS StartDate
,CAST('20200731' AS Date) AS DueDate
,CAST(LT AS smallint) AS lt
,CAST(QTY AS decimal(19,6)) AS Qty
FROM BOM_TESTH

UNION ALL

SELECT SON as node_id
,FATHER AS parent_id
,null AS startdate
,null AS DueDate
,LT AS lt
,QTY AS Qty
FROM BOM_TESTR
)
CACHE FORCE
)

The result is this:

Remember, the rule is:

the due date of one level is exactly the start date of the upper level minus the leadtime