FreightTransport/create_views.sql

-- View: kotov_aa.v_late_orders

-- DROP VIEW kotov_aa.v_late_orders;

CREATE OR REPLACE VIEW kotov_aa.v_late_orders
 AS
 SELECT o.orderid,
    c.companyname,
    v.brand,
    cg.name AS cargo_name,
    o.plandate,
    o.factdate,
    o.status,
        CASE
            WHEN o.factdate IS NULL AND o.plandate < CURRENT_DATE THEN 'Просрочен'::text
            WHEN o.factdate > o.plandate THEN ('Просрочен на '::text || (o.factdate - o.plandate)) || ' дн.'::text
            ELSE 'В срок'::text
        END AS result_check
   FROM orders o
     JOIN customers c ON o.customerid = c.customerid
     JOIN vehicles v ON o.vehicleid = v.vehicleid
     JOIN cargo cg ON o.cargoid = cg.cargoid
  WHERE o.factdate > o.plandate OR o.factdate IS NULL AND o.plandate < CURRENT_DATE
  ORDER BY o.plandate;

ALTER TABLE kotov_aa.v_late_orders
    OWNER TO kotov_aa;
-- View: kotov_aa.v_orders_full

-- DROP VIEW kotov_aa.v_orders_full;

CREATE OR REPLACE VIEW kotov_aa.v_orders_full
 AS
 SELECT o.orderid,
    c.companyname AS client,
    v.brand AS vehicle,
    cg.name AS cargo,
    o.orderdate,
    o.plandate,
    o.factdate,
    o.status
   FROM orders o
     JOIN customers c ON o.customerid = c.customerid
     JOIN vehicles v ON o.vehicleid = v.vehicleid
     JOIN cargo cg ON o.cargoid = cg.cargoid;

ALTER TABLE kotov_aa.v_orders_full
    OWNER TO kotov_aa;