SQL ¿Cómo se obtienen las ganancias en un año (ingresos-gastos) sin insertar manualmente el año?

Mi código se ve así por ahora:

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)

GANANCIAS anuales = Ingresos anuales (Suma de las transacciones de servicios) - Gastos anuales (Salario de 12 meses + Gastos por la compra de productos hasta la entrega + Gastos por la compra de un nuevo equipo)

Obtengo los ingresos uniéndome a la tabla de servicios (que contiene los servicios proporcionados por la tienda), una tabla de servicios compartidos (servicios disponibles por transacción) y la tabla de pedidos de clientes (para transacciones), luego los extraje por año para obtener el valor anual.

los gastos de compra provienen de una tabla de entrega que registra las entregas de productos entrantes, y una tabla de tipo de equipo registra los tipos de equipos (secadora, lavadora, etc.), mientras que los equipos son las unidades individuales de equipo (lavadora-01, lavadora-02, etc. )

y, por último, la tabla de puestos contiene el salario de cada puesto de empleado

Answer

Puede comenzar haciéndose una subconsulta que contenga una fila para cada año en su base de datos. En su caso, parece probable que, entre su deliveryy customer_orderslas tablas, aparezcan todos los años interesantes. Así que haz esto. Te da una mesa virtual con una fila por año.

   SELECT DISTINCT EXTRACT(YEAR FROM dlvr_d) yr
     FROM delivery
    UNION
   SELECT DISTINCT EXTRACT(YEAR FROM cust_ordr_do) yr
    FROM customer_orders

Luego puede escribir sus consultas para comenzar con esa subconsulta, y LEFT JOIN las otras cosas que desee. Algo como esto podría funcionar.

WITH years AS (
   SELECT DISTINCT EXTRACT(YEAR FROM dlvr_d) yr
     FROM delivery
    UNION
   SELECT DISTINCT EXTRACT(YEAR FROM cust_ordr_do) yr
    FROM customer_orders
)
SELECT years.yr, serv.serv_p
 FROM years
 LEFT JOIN (
    Select SUM(s.serv_p) serv_p, EXTRACT(YEAR FROM co.cust_order_do) yr
      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'
    GROUP BY EXTRACT(YEAR FROM co.cust_ordr_do)
 ) serv ON years.yr = serv.yr

Eso le dará un conjunto de resultados con una fila para cada año. Tendrá una columna nula (lo que significa que no hay resultados para el año) o un valor útil de la consulta que mostraste.

No entiendo su consulta lo suficientemente bien como para refactorizar todo, lamento decirlo.