SELECT "production_workshift"."wiring_id",
       "wirings_wiring"."supplier",
       "wirings_wiring"."customer",
       "wirings_wiring"."standard_seconds",
       "wirings_wiring"."standard_production",
       "production_workshift"."date",
       COALESCE(SUM("scrap_report"."scrap_counter"), 0)     AS "scrap_count",
       COALESCE(SUM("test_report"."tot_assembled"), 0)      AS "tot_assembled",
       SUM("test_report"."pause_time")                      AS "pause_time",
       ( 1 / Avg(( 1 / "test_report"."efficiency" )) )      AS "efficiency",
       Avg("test_report"."productivity")                    AS "productivity",
       Avg("test_report"."std_dev")                         AS "std_dev",
       MIN("test_report"."first_test")                      AS "first_test",
       MAX("test_report"."last_test")                       AS "last_test",
       Avg("test_report"."avg_tot")                         AS "avg_tot",
       Avg("test_report"."avg_setup")                       AS "avg_setup",
       Avg("test_report"."avg_positioning")                 AS "avg_positioning"
       ,
       Avg("test_report"."avg_assembly")                    AS
       "avg_assembly",
       Avg("test_report"."avg_taping")                      AS "avg_taping",
       MIN("test_report"."min_time")                        AS "min_time",
       COUNT(DISTINCT "production_workshift"."operator_id") AS
       "online_operators"
FROM   "production_workshift"
       INNER JOIN "wirings_wiring"
               ON ( "production_workshift"."wiring_id" =
                  "wirings_wiring"."code" )
       LEFT OUTER JOIN "scrap_report"
                    ON ( "production_workshift"."id" =
                         "scrap_report"."workshift_id" )
       LEFT OUTER JOIN "test_report"
                    ON ( "production_workshift"."id" =
                         "test_report"."workshift_id" )
WHERE  ( "production_workshift"."date" >= '2019-01-24' :: DATE
         AND "production_workshift"."date" <= '2019-01-24' :: DATE
         AND NOT ( "wirings_wiring"."plant_id" = 'UTILS'
                   AND "wirings_wiring"."plant_id" IS NOT NULL )
         AND "wirings_wiring"."plant_id" = 'EL_SIE'
         AND "production_workshift"."date" >= '2019-01-24' :: DATE
         AND "production_workshift"."date" <= '2019-01-24' :: DATE
         AND "wirings_wiring"."plant_id" = 'EL_SIE' )
GROUP  BY "production_workshift"."wiring_id",
          "wirings_wiring"."supplier",
          "wirings_wiring"."customer",
          "wirings_wiring"."standard_seconds",
          "wirings_wiring"."standard_production",
          "production_workshift"."date"
HAVING ( COALESCE(SUM("test_report"."tot_assembled"), 0) >= 1
          OR COALESCE(SUM("scrap_report"."scrap_counter"), 0) >= 1 )








DOVE SCRAP_REPORT è
 SELECT ps.workshift_id,
    ps.DATE,
    SUM(ps.qty) AS scrap_counter
   FROM production_scrap ps
  GROUP BY ps.workshift_id, ps.DATE;


E TEST_REPORT è
 SELECT   pt.workshift_id,
         pt.DATE,
         COUNT(pt.id)                                                                                                                                                                                             AS tot_assembled,
         SUM(COALESCE(tpt.pause_time / 1000.0, 0.0))                                                                                                                                                              AS pause_time,
         Avg((pt."end"               - pt.START)::           NUMERIC / 1000.0) FILTER (WHERE pt.reliable = TRUE)                                                                                                  AS avg_tot_raw,
         avg((pt."end"               - pt.START)::           NUMERIC / 1000.0 - COALESCE(tpt.pause_time / 1000.0, 0.0)) FILTER (WHERE pt.reliable = TRUE)                                                         AS avg_tot,
         avg((pt.first_connection    - pt.START)::           NUMERIC / 1000.0) FILTER (WHERE pt.reliable = TRUE)                                                                                                  AS avg_setup,
         avg((pt.last_connection     - pt.START)::           NUMERIC / 1000.0) FILTER (WHERE pt.reliable = TRUE)                                                                                                  AS avg_assembly,
         avg((pt.last_connection     - pt.first_connection)::NUMERIC / 1000.0) FILTER (WHERE pt.reliable = TRUE)                                                                                                  AS avg_positioning,
         avg((pt."end"               - pt.last_connection):: NUMERIC / 1000.0) FILTER (WHERE pt.reliable = TRUE)                                                                                                  AS avg_taping,
         1::NUMERIC / avg(1::NUMERIC / (100::NUMERIC / (((pt."end" - pt.START)::NUMERIC - COALESCE(tpt.pause_time, 0.0)) / 1000.0) * ww.standard_seconds::NUMERIC)) FILTER (WHERE pt.reliable = TRUE)             AS efficiency,
         avg((ww.standard_seconds::NUMERIC - ((pt."end" - pt.START)::NUMERIC - COALESCE(tpt.pause_time, 0::NUMERIC) / 1000.0) / 1000.0) / ww.standard_seconds::NUMERIC * 100.0) FILTER (WHERE pt.reliable = TRUE) AS productivity,
         stddev_pop((pt."end"              - pt.START)::NUMERIC / 1000.0 - COALESCE(tpt.pause_time / 1000.0, 0.0)) FILTER (WHERE pt.reliable = TRUE)                                                              AS std_dev,
         MIN(pt.last_connection)                                                                                                                                                                                  AS first_test,
         MAX(pt.last_connection)                                                                                                                                                                                  AS last_test,
         MIN((pt."end" - pt.START)::NUMERIC / 1000.0 - COALESCE(tpt.pause_time / 1000.0, 0.0)) FILTER (WHERE pt.reliable = TRUE)                                                                                  AS min_time
FROM     production_test pt
JOIN     test_pause_time tpt
ON       1 = 1
AND      tpt.test_id = pt.id
JOIN     production_workshift pw
ON       1 = 1
AND      pt.workshift_id = pw.id
JOIN     wirings_wiring ww
ON       1 = 1
AND      pw.wiring_id::text = ww.code::text
GROUP BY pt.workshift_id,
         pt.DATE
HAVING   COUNT(pt.id) > 0
ORDER BY pt.workshift_id;