PRODAT PUBLIC

h1. Dashboard Beispiele

h2. Bestellvolumen im Jahr je Woche gegen Budget

!clipboard-202012101056-wlafr.png!

{{collapse(SQLs)

<pre><code class="sql">

-- Je Woche (oben)

SELECT 

  To_char(ld_datum, 'YY"KW"IW') AS year_week,

  SUM(ld_netto_basis_w) AS ld_netto_basis_w,

  5000 as planned

FROM ldsdok 

WHERE ld_code = 'E' -- nur externe Bestellungen

AND ld_datum > today() - 365

GROUP BY year_week

ORDER BY year_week

-- Kumuliert (unten)

WITH _baseqry AS (

	SELECT 

	  To_char(ld_datum, 'YY"KW"IW') AS year_week,

	  SUM(ld_netto_basis_w) AS ld_netto_basis_w,

	  5000 AS planned

	FROM ldsdok 

	WHERE ld_code = 'E' -- nur externe Bestellungen

	AND ld_datum > today() - 365

	GROUP BY year_week

	ORDER BY year_week

)

SELECT 

  t1.year_week,

  SUM(t2.ld_netto_basis_w) AS ld_netto_basis_w,

  SUM(t2.planned) AS planned

FROM _baseqry t1

INNER JOIN _baseqry t2 ON t1.year_week >= t2.year_week

GROUP BY t1.year_week

ORDER BY t1.year_week

</code></pre>

}}

h2. Hauptlieferanten mit Risikobewertung

Beispiel für Anwendung von Kundeneigenschaften an den Lieferantendaten

!clipboard-202012101101-udmqt.png!

{{collapse(SQL)

<pre><code class="sql">

SELECT

ad_krz,

ad_fa1,

ad_land,

a2_haupt,

trecnoparam.getinteger('Risk.SinglesSource', adk.dbrid) AS SinglesSource,

trecnoparam.getinteger('Risk.QPerformance', adk.dbrid) AS QPerformance,

trecnoparam.getinteger('Risk.TPerformance', adk.dbrid) AS TPerformance,

trecnoparam.getinteger('Risk.Country', adk.dbrid) AS Country,

trecnoparam.getinteger('Risk.Capacity', adk.dbrid) AS Capacity,

trecnoparam.getinteger('Risk.Financial', adk.dbrid) AS Financial,

trecnoparam.getinteger('Risk.Certification', adk.dbrid) AS Certification,

trecnoparam.getinteger('Risk.NoRisk', adk.dbrid) AS NoRisk

FROM

 adk 

 LEFT JOIN adk2 ON ad_krz=a2_krz

WHERE a2_haupt LIKE 'H%'

</code></pre>

}}