I use the code below to calculate the required information from other tables. I used joins to display names instead of IDs and to get required sums from other tables. I used COALESCE to convert null to zero.
I had to used it again if I need to sum already COALESCED values. The code below is hard to understand and it's getting harder because I need to add more information. This is just a small part of the main project so it will be really hard to work with it and will have many error and bugs.
Does it have to be so complicated or did I do it wrong? If it has to be complicated like this, is there any replacement to get same results with easier way and code, maybe with an RDBMS other than Firebird?
SELECT P.PROJ_ID, P.PROJ_STATUS, P.TYPE_ID, PT.TYPE_NAME, P.CLASS_ID, CLA.CLASS_NAME, P.PROJ_NO, P.PROJ_YEAR, P.PROJ_NAME, P.OLD_PROJ_NAME,
P.AGENCY_ID, A.AGENCY_NAME, P.CONTRACT_NO, P.CONTRACT_DATE, P.MINISTRY_ID, M.MINISTRY_NAME,
P.DIRECTORATE_ID, DIR.DIRECTORATE_NAME,
P.COST, P.ESTIMATED_COST, COALESCE(CO.ADDED_COSTS, 0) AS ADDED_COSTS, (COALESCE(P.COST, 0) + COALESCE(CO.ADDED_COSTS, 0)) AS TOTAL_COST,
P.ALLOCATION,
COALESCE(EPY.PAST_YEARS, 0) AS PAST_YEARS,
COALESCE(EF.PAST_MONTHS, 0) AS PAST_MONTHS,
COALESCE(ECM.CURRENT_MONTH, 0) AS CURRENT_MONTH,
COALESCE(ECY.CURRENT_YEAR, 0) AS CURRENT_YEAR,
COALESCE(E.TOTAL_EXPENSES, 0) AS TOTAL_EXPENSES,
COALESCE(CASH_EPY.CASH_PAST_YEARS, 0) AS CASH_PAST_YEARS,
COALESCE(CASH_EF.CASH_PAST_MONTHS, 0) AS CASH_PAST_MONTHS,
COALESCE(CASH_ECM.CASH_CURRENT_MONTH, 0) AS CASH_CURRENT_MONTH,
COALESCE(CASH_ECY.CASH_CURRENT_YEAR, 0) AS CASH_CURRENT_YEAR,
COALESCE(CASH_E.CASH_TOTAL_EXPENSES, 0) AS CASH_TOTAL_EXPENSES,
COALESCE(TOTAL_E.TOTAL_EXPENSES_CASH, 0) AS TOTAL_EXPENSES_CASH,
((COALESCE(P.COST, 0) + COALESCE(CO.ADDED_COSTS, 0)) - COALESCE(E.TOTAL_EXPENSES, 0)) AS REMAINING,
P.DURATION, COALESCE(DU.ADDED_DURATIONS, 0) AS ADDED_DURATIONS,
(COALESCE(P.DURATION, 0) + COALESCE(DU.ADDED_DURATIONS, 0)) AS TOTAL_DURATION, P.START_DATE, P.FINISH_DATE,
P.GOVERNORATE_ID, G.GOVERNORATE_NAME, P.PROVINCE_ID, PR.PROVINCE_NAME, P.DISTRICT_ID, D.DISTRICT_NAME,
P.TOWN_ID, T.TOWN_NAME,
COALESCE( (E.TOTAL_EXPENSES / (COALESCE(P.COST, 0) + COALESCE(CO.ADDED_COSTS, 0)))/100, 0) AS FINANCIAL_ACHIEVEMENT,
P.MATERIAL_ACHIEVEMENT, P.NOTES
FROM PROJECTS P
INNER JOIN PROJECTS_TYPES PT
ON P.TYPE_ID = PT.TYPE_ID
INNER JOIN CLASSES CLA
ON P.CLASS_ID = CLA.CLASS_ID
INNER JOIN AGENCIES A
ON P.AGENCY_ID = A.AGENCY_ID
LEFT JOIN MINISTRIES M
ON P.MINISTRY_ID = M.MINISTRY_ID
LEFT JOIN DIRECTORATES DIR
ON P.DIRECTORATE_ID = DIR.DIRECTORATE_ID
INNER JOIN GOVERNORATES G
ON P.GOVERNORATE_ID = G.GOVERNORATE_ID
LEFT JOIN PROVINCES PR
ON P.PROVINCE_ID = PR.PROVINCE_ID
LEFT JOIN DISTRICTS D
ON P.DISTRICT_ID = D.DISTRICT_ID
LEFT JOIN TOWNS T
ON P.TOWN_ID = T.TOWN_ID
-- ADDED COSTS
LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(ADDED_VALUE, 0) - COALESCE(REMOVED_VALUE, 0)) as ADDED_COSTS
FROM COSTS
GROUP BY PROJ_ID ) AS CO
ON P.PROJ_ID = CO.PROJ_ID
-- EXPENSES FROM PAST YEARS
LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as PAST_YEARS
FROM EXPENSES WHERE EXTRACT(YEAR FROM DOC_DATE) < EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = FALSE
GROUP BY PROJ_ID ) AS EPY
ON P.PROJ_ID= EPY.PROJ_ID
-- EXPENSES FROM PAST MONTHS IN CUREENT YEAR
LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as PAST_MONTHS
FROM EXPENSES WHERE EXTRACT(MONTH FROM DOC_DATE) < EXTRACT(MONTH FROM CURRENT_DATE)
AND EXTRACT(YEAR FROM DOC_DATE) = EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = FALSE
GROUP BY PROJ_ID ) AS EF
ON P.PROJ_ID= EF.PROJ_ID
-- EXPENSES FROM CURRENT MONTH AND YEAR
LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as CURRENT_MONTH
FROM EXPENSES WHERE EXTRACT(MONTH FROM DOC_DATE) = EXTRACT(MONTH FROM CURRENT_DATE) AND EXTRACT(YEAR FROM DOC_DATE) = EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = FALSE
GROUP BY PROJ_ID ) AS ECM
ON P.PROJ_ID= ECM.PROJ_ID
-- SUM OF EXPENSES IN CURRENT YEAR
LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as CURRENT_YEAR
FROM EXPENSES WHERE EXTRACT(YEAR FROM DOC_DATE) = EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = FALSE
GROUP BY PROJ_ID ) AS ECY
ON P.PROJ_ID= ECY.PROJ_ID
-- TOTAL EXPENSES FROM ALL TIME
LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as TOTAL_EXPENSES
FROM EXPENSES WHERE CASH_DEDUCTIONS = FALSE
GROUP BY PROJ_ID ) AS E
ON P.PROJ_ID= E.PROJ_ID
-- CASH DEDUCTIONS SUMS
-- CASH DEDUCTIONS FROM PAST YEARS
LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as CASH_PAST_YEARS
FROM EXPENSES WHERE EXTRACT(YEAR FROM DOC_DATE) < EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = TRUE
GROUP BY PROJ_ID ) AS CASH_EPY
ON P.PROJ_ID= CASH_EPY.PROJ_ID
-- CASH DEDUCTIONS FROM PAST MONTHS IN CUREENT YEAR
LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as CASH_PAST_MONTHS
FROM EXPENSES WHERE EXTRACT(MONTH FROM DOC_DATE) < EXTRACT(MONTH FROM CURRENT_DATE)
AND EXTRACT(YEAR FROM DOC_DATE) = EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = TRUE
GROUP BY PROJ_ID ) AS CASH_EF
ON P.PROJ_ID= CASH_EF.PROJ_ID
-- CASH DEDUCTIONS FROM CURRENT MONTH AND YEAR
LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as CASH_CURRENT_MONTH
FROM EXPENSES WHERE EXTRACT(MONTH FROM DOC_DATE) = EXTRACT(MONTH FROM CURRENT_DATE) AND EXTRACT(YEAR FROM DOC_DATE) = EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = TRUE
GROUP BY PROJ_ID ) AS CASH_ECM
ON P.PROJ_ID= CASH_ECM.PROJ_ID
-- SUM OF CASH DEDUCTIONS IN CURRENT YEAR
LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as CASH_CURRENT_YEAR
FROM EXPENSES WHERE EXTRACT(YEAR FROM DOC_DATE) = EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = TRUE
GROUP BY PROJ_ID ) AS CASH_ECY
ON P.PROJ_ID= CASH_ECY.PROJ_ID
-- TOTAL CASH DEDUCTIONS FROM ALL TIME
LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as CASH_TOTAL_EXPENSES
FROM EXPENSES WHERE CASH_DEDUCTIONS = TRUE
GROUP BY PROJ_ID ) AS CASH_E
ON P.PROJ_ID= CASH_E.PROJ_ID
-- TOTAL EXPENSES AND CASH DEDUCTIONS FROM ALL TIME
LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(TOTAL_VALUE, 0)) as TOTAL_EXPENSES_CASH
FROM EXPENSES
GROUP BY PROJ_ID ) AS TOTAL_E
ON P.PROJ_ID= TOTAL_E.PROJ_ID
-- ADDED DURATIONS
LEFT JOIN (SELECT PROJ_ID, SUM(COALESCE(ADDED_VALUE, 0) - COALESCE(REMOVED_VALUE, 0)) as ADDED_DURATIONS
FROM DURATIONS
GROUP BY PROJ_ID ) AS DU
ON P.PROJ_ID= DU.PROJ_ID
ORDER BY P.PROJ_YEAR, P.TYPE_ID, P.PROJ_NO
