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 */
);
