Subject How can I maintain the hierarchy of the data in the results returned
Author
Post date 2016-08-08T19:54:08Z

I have the following tables in a relationship I’ve tried to draw below.  The OrgChart table is a closure table to display the hierarchy of the organisation.  The Organisation is 3 Levels deep. 

 

|--------------|  

|Guidance   | 

|_________| 

    |                                                   

    |                                                           

    m                  

|-----------------|                  |-------------------|

|Assignment   |   ------m  |Organisation    |

|___________|                 ---------------------

    |                                              |     |

    |                                              m   m

    |                                         |------------|

    |                                         |OrgChart|

    m                                       |------------|

|--------------|

|Review       |

|_________|

 

When a guidance is issued it is assigned to all levels of the organisation for review.  The lowest level (level 3 tier) reviews it and this is recorded in the review table.  The Level 2 tier signs off the review when all the level 3 tiers under it has reviewed the guidance and so on.  What I’d like to do is to write a query (or series of queries)  so I can list the last review of each level in the organisation through the hierarchy.

 

Guidance ref_num|  Date last reviewed|   Dept Name

100                        | 5/5/16                     | Dept A

100                        | 1/4/16                     | Dept AA

100                        | 1/3/16                     | Dept AAA   

100                        | 5/5/16                     | Dept A

100                        | 1/2/16                     | Dept AA2

100                        | 3/3/16                     | Dept AAA2

 

Etc.

 

The idea is to later scan the table to produce a report as below

 

Ref_num | Level1 _ Dept | level 1_Review | Level2_Dept | Level2_Review | Level3_Dept  | Level3_Review

100          | Dept A            | 5/5/16               |Dept AA         |1/4/16                 | Dept AAA      |1/3/16

100          | Dept A            | 5/5/16               |Dept AA2       |1/2/16                | Dept AAA2    |3/3/16

 

 

I'd be grateful for any help.  Here is the full definition of the tables:


CREATE TABLE ASSIGNMENT (

    ASSID         DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */,

    VERID         DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */,

    ORGID         DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */,

    ASSIGNED_DTE  DOM_DATE NOT NULL /* DOM_DATE = DATE */,

    EMPID         DOM_INTLARGE /* DOM_INTLARGE = BIGINT */,

    STEID         DOM_INTSMALL /* DOM_INTSMALL = SMALLINT */

);


CREATE TABLE GUIDANCE (
    GDLID    DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */,
    VERID   DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */,
    REF_NUM  DOM_VARCHARSMALL NOT NULL /* DOM_VARCHARSMALL = VARCHAR(15) */,
    CAPTION  DOM_VARCHARLARGE NOT NULL /* DOM_VARCHARLARGE = VARCHAR(225) */,
    LINK     DOM_VARCHARLARGE /* DOM_VARCHARLARGE = VARCHAR(225) */,
    GTYCAT   DOM_INTSMALL NOT NULL /* DOM_INTSMALL = SMALLINT */
);

CREATE TABLE ORGANISATION (

    ORGID      DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */,

    ORG_NME    DOM_VARCHARMEDIUM NOT NULL /* DOM_VARCHARMEDIUM = VARCHAR(30) */,

    LEVEL_NUM  DOM_INTSMALL NOT NULL /* DOM_INTSMALL = SMALLINT */,

    PARENT     DOM_INTLARGE /* DOM_INTLARGE = BIGINT */,

    INUSE      DOM_BOOL /* DOM_BOOL = CHAR(1) CHECK (value is NULL or (value between 0 and 1)) */

);


CREATE TABLE ORGCHART (
    OCHID               DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */,
    PARENTID            DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */,
    CHILDID             DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */,
    DEPTH               DOM_INTSMALL NOT NULL /* DOM_INTSMALL = SMALLINT */,
    ORGANISATION_ORGID  DOM_INTLARGE /* DOM_INTLARGE = BIGINT */
);


CREATE TABLE REVIEW (
    REVID       DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */,
    ASSID       DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */,
    REVIEW_DTE  DOM_DATE NOT NULL /* DOM_DATE = DATE */,
    GOTID       DOM_INTSMALL NOT NULL /* DOM_INTSMALL = SMALLINT */,
    NOTE        BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    EMPID       DOM_INTLARGE /* DOM_INTLARGE = BIGINT */
);