I have 3 sections as follows;
- TIER I
- TIER II
- TIER III
Each having tables as below
- Performance Measures which references Objectives
- Performance Actual which references Performance Measure
The Objectives of Tier II references those of Tier I & those of Tier III references those of Tier II.
My requirement I want to get Tier II Performance from Tier III performance, and also get Tier I performance as from Tier II performance.
What I have Tried
SELECT d_o.ObjectiveName, ia.Q1 AS 'Q1Actual', ia.Q2 AS 'Q2Actual', ia.Q3 AS 'Q3Actual', ia.Q4 AS 'Q4Actual', i_o.ObjectiveName AS 'Individual Objective', fy.FY AS 'FY', AVG(ia.Q1), dpm.DeptMeasureId, dpm.Q1, ia.* FROM individual_appraisal ia LEFT JOIN individual_pm ipm on ia.MeasureId = ipm.MeasureId LEFT JOIN individual_objectives i_o ON ipm.IndObjectiveId = i_o.IndObjectiveId LEFT JOIN department_objectives d_o ON i_o.DeptObjectiveId = d_o.DeptObjectiveId LEFT JOIN department_pm dpm ON dpm.DeptObjectiveId = d_o.DeptObjectiveId LEFT JOIN tier_one_objectives t1_o ON d_o.TierOneObjectiveId = t1_o.TierOneObjectiveId LEFT JOIN financial_years fy ON t1_o.FYID = fy.FYID WHERE i_o.StaffUser = 'staffuserName...' GROUP BY dpm.DeptMeasureId
Note I have attached a database diagram for the same to better understand the question.
Your kind help is highly appreciated.