3
\$\begingroup\$

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 
\$\endgroup\$
2
\$\begingroup\$

I'd use common table expressions (CTE) for COSTS, EXPENSES and DURATIONS tables. In the CTE, you can do all the calculations that require a WHERE clause in CASE expressions. Then you can do your joins to the CTE expression names. In my example below, the expression name for the EXPENSES query is EXPENSES_SUMS.

Example

WITH
EXPENSES_SUMS
AS
(
    SELECT 
          PROJ_ID
        , SUM(CASE 
                WHEN EXTRACT(YEAR FROM DOC_DATE) < EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = FALSE 
                THEN TOTAL_VALUE
          END) AS PAST_YEARS
        , SUM(CASE  
                WHEN 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
                THEN TOTAL_VALUE
          END) AS PAST_MONTHS
        , SUM(CASE  
                WHEN 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
                THEN TOTAL_VALUE 
          END) AS CURRENT_MONTH
        , SUM(CASE  
                WHEN EXTRACT(YEAR FROM DOC_DATE) = EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = FALSE
                THEN TOTAL_VALUE
          END) AS CURRENT_YEAR
        , SUM(CASE  
                WHEN CASH_DEDUCTIONS = FALSE
                THEN TOTAL_VALUE
          END) AS TOTAL_EXPENSES
        , SUM(CASE  
                WHEN EXTRACT(YEAR FROM DOC_DATE) < EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = TRUE
                THEN TOTAL_VALUE
          END) AS CASH_PAST_YEARS
        , SUM(CASE  
                WHEN 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
                THEN TOTAL_VALUE 
          END) AS CASH_PAST_MONTHS
        , SUM(CASE  
                WHEN 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
                THEN TOTAL_VALUE
          END) AS CASH_CURRENT_MONTH
        , SUM(CASE  
                WHEN EXTRACT(YEAR FROM DOC_DATE) = EXTRACT(YEAR FROM CURRENT_DATE) AND CASH_DEDUCTIONS = TRUE
                THEN TOTAL_VALUE
          END) AS CASH_CURRENT_YEAR
        , SUM(CASE  
                WHEN CASH_DEDUCTIONS = TRUE
                THEN TOTAL_VALUE
          END) AS CASH_TOTAL_EXPENSES
        , SUM(TOTAL_VALUE) AS TOTAL_EXPENSES_CASH
        --you can add more columns with your conditions here in case expressions

    FROM 
        EXPENSES
    GROUP BY 
        PROJ_ID
)
, 
COSTS_SUMS
AS
(
    SELECT 
          PROJ_ID
        , SUM(COALESCE(ADDED_VALUE, 0) - COALESCE(REMOVED_VALUE, 0)) AS ADDED_COSTS
        --you can add more columns with your conditions here in case expressions

    FROM 
        COSTS
    GROUP BY 
        PROJ_ID
)
, 
DURATIONS_SUMS
AS
(
    SELECT 
          PROJ_ID
        , SUM(COALESCE(ADDED_VALUE, 0) - COALESCE(REMOVED_VALUE, 0)) AS ADDED_DURATIONS
        --you can add more columns with your conditions here in case expressions

    FROM 
        DURATIONS
    GROUP BY 
        PROJ_ID
)
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(ED.PAST_YEARS, 0) AS PAST_YEARS
    , COALESCE(ED.PAST_MONTHS, 0) AS PAST_MONTHS
    , COALESCE(ED.CURRENT_MONTH, 0) AS CURRENT_MONTH
    , COALESCE(ED.CURRENT_YEAR, 0) AS CURRENT_YEAR
    , COALESCE(ED.TOTAL_EXPENSES, 0) AS TOTAL_EXPENSES
    , COALESCE(ED.CASH_PAST_YEARS, 0) AS CASH_PAST_YEARS
    , COALESCE(ED.CASH_PAST_MONTHS, 0) AS CASH_PAST_MONTHS
    , COALESCE(ED.CASH_CURRENT_MONTH, 0) AS CASH_CURRENT_MONTH
    , COALESCE(ED.CASH_CURRENT_YEAR, 0) AS CASH_CURRENT_YEAR
    , COALESCE(ED.CASH_TOTAL_EXPENSES, 0) AS CASH_TOTAL_EXPENSES
    , COALESCE(ED.TOTAL_EXPENSES_CASH, 0) AS TOTAL_EXPENSES_CASH
    , ((COALESCE(P.COST, 0) + COALESCE(CO.ADDED_COSTS, 0)) - COALESCE(ED.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( (ED.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
    LEFT JOIN EXPENSES_SUMS ED ON P.PROJ_ID = ED.PROJ_ID
    LEFT JOIN COSTS_SUMS CO ON P.PROJ_ID = CO.PROJ_ID
    LEFT JOIN DURATIONS_SUMS DU ON P.PROJ_ID = CO.PROJ_ID
ORDER BY 
      P.PROJ_YEAR
    , P.TYPE_ID
    , P.PROJ_NO 

You could even create an expression in the CTE for common values.

SELECT 
      EXTRACT(MONTH FROM CURRENT_DATE) AS CURRENT_MONTH
    , EXTRACT(YEAR FROM CURRENT_DATE) AS CURRENT_YEAR
FROM 
    rdb$database
\$\endgroup\$
  • 1
    \$\begingroup\$ I'll update my answer when I get to the office. My apologies, I don't have an instance of Firebird on my machine to test the syntax. \$\endgroup\$ – aduguid Aug 22 '18 at 21:21
  • 1
    \$\begingroup\$ I also had to wrap the CASE expression with SUM for the grouping \$\endgroup\$ – aduguid Aug 22 '18 at 23:25
  • 1
    \$\begingroup\$ I just noticed the group by is a bit off, I'll update it now \$\endgroup\$ – aduguid Aug 23 '18 at 0:21
  • 1
    \$\begingroup\$ Since I wrapped the CASE expression in the SUM, we don't need the other columns in the GROUP BY \$\endgroup\$ – aduguid Aug 23 '18 at 0:22
  • 1
    \$\begingroup\$ Now it's working as expected, thank you very much I really appreciate what you did, I have learned very important things from your code, I will accept the answer because it helped me and improved my SQL knowledge \$\endgroup\$ – Sa Ra Fin Bas Aug 23 '18 at 0:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.