Gaspedal -snipet

[Flo Boj]

to guys

running idempiere in real env -

shall help to estimate date promised

[[

CREATE OR REPLACE FUNCTION Gaspedal()
RETURNS void AS $$
BEGIN

   -- Vorhandene Tabelle löschen, falls sie existiert
    DROP TABLE IF EXISTS ueit_gaspedal;

    -- Neue Tabelle erstellen
    CREATE TABLE ueit_gaspedal (
        Monat TEXT,
        Dauer_der_Fertigung_1000 TEXT,
        Prozent_von_Maximum TEXT
    );
    -- Daten in die Tabelle einfügen
    INSERT INTO ueit_gaspedal (Monat, Dauer_der_Fertigung_1000, Prozent_von_Maximum)
    WITH MonthlyProduction AS (
        SELECT
            TO_CHAR(DATE_TRUNC('month', ol.created), 'YYYY-MM-DD') AS Monat,
            ROUND(SUM(ol.qtyordered * ol.priceactual) / 1000, 0)::TEXT || ' K' AS Dauer_der_Fertigung_1000,
            SUM(ol.qtyordered * ol.priceactual) AS Dauer_der_Fertigung
        FROM C_Orderline ol
        JOIN C_Order o ON ol.C_Order_ID = o.C_Order_ID
        WHERE o.Docstatus = 'CO'
          AND o.Docaction = 'CL'
          AND o.C_DoctypeTarget_ID = 1000032
        GROUP BY DATE_TRUNC('month', ol.created)
    )
    SELECT
        Monat,
        Dauer_der_Fertigung_1000,
        ROUND(Dauer_der_Fertigung / (SELECT MAX(Dauer_der_Fertigung) FROM MonthlyProduction) * 100, 0)::TEXT || ' %'
    FROM MonthlyProduction
    ORDER BY Monat;
END;
$$ LANGUAGE plpgsql;
###to be run daily by whatever daily schedule
select * from gaspedal();

COLUMSQL - idempiere ( I got it inside order )
---------------------
(SELECT
    LEFT(TO_CHAR(NOW() - INTERVAL '1 month', 'FMMonth'), 3) || ':' ||
    COALESCE(
        (SELECT prozent_von_maximum::text
         FROM ueit_gaspedal
         WHERE monat = TO_CHAR(DATE_TRUNC('month', NOW() - INTERVAL '1 month'), 'YYYY-MM-DD')),
        '0'
    ) || ', ' ||
    LEFT(TO_CHAR(NOW(), 'FMMonth'), 3) || ':' ||
    COALESCE(
        (SELECT prozent_von_maximum::text
         FROM ueit_gaspedal
         WHERE monat = TO_CHAR(DATE_TRUNC('month', NOW()), 'YYYY-MM-DD')),
        '0'
    ) )

]]

Have a lot of fun &

thx for your patience

Florian

[Flo Boj]

AI Stuff complete lol. LOL ! was 3 years in my mind !!

Solution: 

Observe closely, at what percentage of gaspedal you begin to produce more and more mistakes. 

ie 35 percent points you know your guys start doing errors and errors, machines will be down, guys will be in holiday or ill.... 

then :

(idempiere-column sql ..... column-sql)

(SELECT
  LEFT(TO_CHAR(NOW() - INTERVAL '1 month', 'FMMonth'), 3) || ':' ||
  COALESCE((
    SELECT prozent_von_maximum::text
    FROM ueit_gaspedal
    WHERE monat = TO_CHAR(DATE_TRUNC('month', NOW() - INTERVAL '1 month'), 'YYYY-MM-DD')
  ), '0') || ', ' ||
  LEFT(TO_CHAR(NOW(), 'FMMonth'), 3) || ':' ||
  COALESCE((
    SELECT prozent_von_maximum::text

    FROM ueit_gaspedal
    WHERE monat = TO_CHAR(DATE_TRUNC('month', NOW()), 'YYYY-MM-DD')

  ), '0') || '__Please add workingdays to your calculation:+' ||
  COALESCE((
    SELECT
      CASE
        WHEN ROUND(SUM(
          CASE
            WHEN CAST(prozent_von_maximum AS INTEGER) > 35
            THEN CAST(prozent_von_maximum AS INTEGER) - 35
            ELSE 0
          END
        )::NUMERIC / 3, 2) / 2 < 0
        THEN '0'
        ELSE FLOOR(
          ROUND(SUM(
            CASE
              WHEN CAST(prozent_von_maximum AS INTEGER) > 35
              THEN CAST(prozent_von_maximum AS INTEGER) - 35
              ELSE 0
            END
          )::NUMERIC / 3, 2) / 2
        )::TEXT
      END
    FROM ueit_gaspedal
    WHERE TO_DATE(monat, 'YYYY-MM-DD') >= date_trunc('month', CURRENT_DATE - interval '3 months')
      AND TO_DATE(monat, 'YYYY-MM-DD') < date_trunc('month', CURRENT_DATE)
  ), '0'))

Have a lot of fun!