SQL How do you get the earnings in a year (revenue-expenses) without manually inserting the year?

  postgresql, python, sql, union

My code looks like this for now:

Select EXTRACT(YEAR FROM d.dlvr_d),
                    (Select SUM(s.serv_p) 
                    FROM customer_orders co
                    INNER JOIN co_service cs ON co.cust_ordr_id = cs.cust_ordr_id 
                    INNER JOIN service s ON cs.serv_id = s.serv_id
                    WHERE co.cust_ordr_delete_ind = 'False' and EXTRACT(YEAR FROM co.cust_ordr_do)= 2019
                    GROUP BY EXTRACT(YEAR FROM co.cust_ordr_do))
                    -
                    (Select SUM(s.serv_p)
                    FROM customer_orders co
                    INNER JOIN co_service cs ON co.cust_ordr_id = cs.cust_ordr_id 
                    INNER JOIN service s ON cs.serv_id = s.serv_id
                    WHERE co.cust_ordr_delete_ind = 'False' and EXTRACT(YEAR FROM co.cust_ordr_do) = 2019
                    GROUP BY EXTRACT(YEAR FROM co.cust_ordr_do))
                    -
                    ((Select SUM(e.eqp_pp)
                    FROM equipment e
                    INNER JOIN equipment_type y on y.eqp_t_id = e.eqp_t_id
                    WHERE e.eqp_delete_ind = 'False' and y.eqp_t_delete_ind = 'False' and EXTRACT(YEAR FROM e.eqp_pd)=2019)
                    +
                    (SELECT SUM((d.prod_qty_ds)*(p.prod_up))
                    FROM product p
                    INNER JOIN delivery d ON p.prod_id = d.prod_id
                    WHERE d.dlvr_delete_ind = 'False' and EXTRACT(YEAR FROM d.dlvr_d)=2019
                    GROUP BY EXTRACT(YEAR FROM d.dlvr_d))
                    +
                    (SELECT 12*SUM(p.pos_sal)
                    FROM employee e
                    INNER JOIN positions p ON p.pos_id = e.pos_id
                    WHERE e.emp_delete_ind = 'False'))
                    FROM delivery d
                    WHERE d.dlvr_delete_ind = 'False' and EXTRACT(YEAR FROM d.dlvr_d)=2019
                    GROUP BY EXTRACT(YEAR FROM d.dlvr_d)
                    UNION
                    Select EXTRACT(YEAR FROM d.dlvr_d),
                    (Select SUM(s.serv_p) 
                    FROM customer_orders co
                    INNER JOIN co_service cs ON co.cust_ordr_id = cs.cust_ordr_id 
                    INNER JOIN service s ON cs.serv_id = s.serv_id
                    WHERE co.cust_ordr_delete_ind = 'False' and EXTRACT(YEAR FROM co.cust_ordr_do)= 2020
                    GROUP BY EXTRACT(YEAR FROM co.cust_ordr_do))
                    -
                    (Select SUM(s.serv_p)
                    FROM customer_orders co
                    INNER JOIN co_service cs ON co.cust_ordr_id = cs.cust_ordr_id 
                    INNER JOIN service s ON cs.serv_id = s.serv_id
                    WHERE co.cust_ordr_delete_ind = 'False' and EXTRACT(YEAR FROM co.cust_ordr_do) = 2020
                    GROUP BY EXTRACT(YEAR FROM co.cust_ordr_do))
                    -
                    ((Select SUM(e.eqp_pp)
                    FROM equipment e
                    INNER JOIN equipment_type y on y.eqp_t_id = e.eqp_t_id
                    WHERE e.eqp_delete_ind = 'False' and y.eqp_t_delete_ind = 'False' and EXTRACT(YEAR FROM e.eqp_pd)=2020)
                    +
                    (SELECT SUM((d.prod_qty_ds)*(p.prod_up))
                    FROM product p
                    INNER JOIN delivery d ON p.prod_id = d.prod_id
                    WHERE d.dlvr_delete_ind = 'False' and EXTRACT(YEAR FROM d.dlvr_d)=2020
                    GROUP BY EXTRACT(YEAR FROM d.dlvr_d))
                    +
                    (SELECT 12*SUM(p.pos_sal)
                    FROM employee e
                    INNER JOIN positions p ON p.pos_id = e.pos_id
                    WHERE e.emp_delete_ind = 'False'))
                    FROM delivery d
                    WHERE d.dlvr_delete_ind = 'False' and EXTRACT(YEAR FROM d.dlvr_d)=2020
                    GROUP BY EXTRACT(YEAR FROM d.dlvr_d)
                    UNION
                    Select EXTRACT(YEAR FROM d.dlvr_d),
                    (Select SUM(s.serv_p) 
                    FROM customer_orders co
                    INNER JOIN co_service cs ON co.cust_ordr_id = cs.cust_ordr_id 
                    INNER JOIN service s ON cs.serv_id = s.serv_id
                    WHERE co.cust_ordr_delete_ind = 'False' and EXTRACT(YEAR FROM co.cust_ordr_do)= 2021
                    GROUP BY EXTRACT(YEAR FROM co.cust_ordr_do))
                    -
                    (Select SUM(s.serv_p)
                    FROM customer_orders co
                    INNER JOIN co_service cs ON co.cust_ordr_id = cs.cust_ordr_id 
                    INNER JOIN service s ON cs.serv_id = s.serv_id
                    WHERE co.cust_ordr_delete_ind = 'False' and EXTRACT(YEAR FROM co.cust_ordr_do) = 2021
                    GROUP BY EXTRACT(YEAR FROM co.cust_ordr_do))
                    -
                    ((Select SUM(e.eqp_pp)
                    FROM equipment e
                    INNER JOIN equipment_type y on y.eqp_t_id = e.eqp_t_id
                    WHERE e.eqp_delete_ind = 'False' and y.eqp_t_delete_ind = 'False' and EXTRACT(YEAR FROM e.eqp_pd)=2021)
                    +
                    (SELECT SUM((d.prod_qty_ds)*(p.prod_up))
                    FROM product p
                    INNER JOIN delivery d ON p.prod_id = d.prod_id
                    WHERE d.dlvr_delete_ind = 'False' and EXTRACT(YEAR FROM d.dlvr_d)=2021
                    GROUP BY EXTRACT(YEAR FROM d.dlvr_d))
                    +
                    (SELECT 12*SUM(p.pos_sal)
                    FROM employee e
                    INNER JOIN positions p ON p.pos_id = e.pos_id
                    WHERE e.emp_delete_ind = 'False'))
                    FROM delivery d
                    WHERE d.dlvr_delete_ind = 'False' and EXTRACT(YEAR FROM d.dlvr_d)=2021
                    GROUP BY EXTRACT(YEAR FROM d.dlvr_d)

Annual EARNINGS = Annual Revenue (Sum of Service Transactions) – Annual Expenses (Salary for 12 months + Expenses from Purchasing Products through delivery + Expenses from purchasing a new equipment)

I get the revenue by joining the service table (contains the services provided by the shop) a co_service table (services availed per transaction) and the customer order table (for transactions) then i extracted them by year to get the annual value

expenses for purchasing come from a delivery table which records incoming product deliveries, and an equipment type table records the kinds of equipments (dryer, washer, etc), while equipments are the individual units of equipment (washer-01, washer-02, etc)

and lastly positions table contains the salary for each employee position

Source: Python Questions

LEAVE A COMMENT