cli:franke:shared_data

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
cli:franke:shared_data [2018/08/27 12:25]
f.zoppini [Quering samples] 		cli:franke:shared_data [2018/08/27 14:01] (current)
f.zoppini [Sample queries]
Line 2: Line 2:
 ==== Database access: technical information ==== ==== Database access: technical information ====
 ^DB Server | 10.100.145.20 (fkt20g.fa.franke.world) | ^DB Server | 10.100.145.20 (fkt20g.fa.franke.world) |
-^SB Server Port |5432|+^DB Server Port |5432|
 ^DBMS | Postgresql| ^DBMS | Postgresql|
 ^ODBC Driver | [[https://ftp.postgresql.org/pub/odbc/versions/msi/psqlodbc_09_03_0300-1.zip|32bit]], [[https://ftp.postgresql.org/pub/odbc/versions/msi/psqlodbc_09_03_0300-x64-1.zip|64bit]] | ^ODBC Driver | [[https://ftp.postgresql.org/pub/odbc/versions/msi/psqlodbc_09_03_0300-1.zip|32bit]], [[https://ftp.postgresql.org/pub/odbc/versions/msi/psqlodbc_09_03_0300-x64-1.zip|64bit]] |
 ^Database name|andon_FWK| ^Database name|andon_FWK|
-^Username|(readonly)| +^Database schema|shareddata| 
-^Password||+^Username| michael.lehmann (readonly)| 
 +^Password| Swh2Rj6Q|
  
 ==== E-R diagram ==== ==== E-R diagram ====
Line 34: Line 35:
 | | |oracr|Time of the tracked production unit (HH:MM:SS)|varchar| | | | |oracr|Time of the tracked production unit (HH:MM:SS)|varchar| |
 ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^
-| **causanc** |**Non compliant causes list**|__codice__|Cause unique identifier|varchar|  YES  |+| **qcausanc** |**Non compliant causes list**|__codice__|Cause unique identifier|varchar|  YES  |
 | | |descrizio|Cause description|varchar| | | | |descrizio|Cause description|varchar| |
 ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^
-| **causafm** |**Downtime causes list**|__codice__|Downtime unique identifier|varchar|  YES  |+| **qcausafm** |**Downtime causes list**|__codice__|Downtime unique identifier|varchar|  YES  |
 | | |descrizio|Downtime description of the monitored resource|varchar| | | | |descrizio|Downtime description of the monitored resource|varchar| |
 | | |tipofm|Downtime type: F = unplanned downtime, M = microstop, P = planned downtime|varchar| | | | |tipofm|Downtime type: F = unplanned downtime, M = microstop, P = planned downtime|varchar| |
Line 72: Line 73:
 |**regfm**(__codice__, data, ora, tm, tmdiff, tipoevento, codubi, caufm)|- //codubi// foreign key on //prodline.codice// \\ - //caufm// foreign key on //causafm.codice//| |**regfm**(__codice__, data, ora, tm, tmdiff, tipoevento, codubi, caufm)|- //codubi// foreign key on //prodline.codice// \\ - //caufm// foreign key on //causafm.codice//|
  
-==== Quering samples ====+==== Sample queries ====
 Produced items on June 18th 2018 with date, time, unique identifier, production resource description, department description, article code (model) and work order, sorted by event time epoch Produced items on June 18th 2018 with date, time, unique identifier, production resource description, department description, article code (model) and work order, sorted by event time epoch
 <code sql> <code sql>
-select data, ora, codlot, prodline.descrizio as linea, prodline.descrep, unitprod.dart, unitprod.procord from regprod  +SELECT DATA, ora, codlot, prodline.descrizio AS linea, prodline.descrep, unitprod.dart, unitprod.procord FROM shareddata.regprod  
-inner join prodline on prodline.codice=regprod.codubi +INNER JOIN shareddata.prodline ON prodline.codice=shareddata.regprod.codubi 
-inner join unitprod on unitprod.codice=regprod.codlot +INNER JOIN shareddata.unitprod ON unitprod.codice=shareddata.regprod.codlot 
-where data='20180618' +WHERE DATA='20180618' 
-order by tm+ORDER BY tm
 </code> </code>
-Conteggio dei pezzi prodotti dopo il 17 Maggio (compreso) raggruppati per giorno, per prodotto e ordini di produzione, nella linea 04+Count produced items after June 18th 2018 (included) grouped by day, article code and work order on production resource 821102A (DMG LB)
 <code sql> <code sql>
-select data, unitprod.dart, unitprod.procord, sum(qta) as pezzi from regprod +select data, unitprod.dart, unitprod.procord, sum(qta) as pezzi from shareddata.regprod 
-inner join unitprod on unitprod.codice=regprod.codlot +inner join shareddata.unitprod on shareddata.unitprod.codice=shareddata.regprod.codlot 
-where regprod.codubi='04' and regprod.data>='20170517'+where regprod.codubi='821102A' and regprod.data>='20180618'
 group by data, dart, procord group by data, dart, procord
 </code> </code>
-Estrarre la lista delle fermate (inizio, fine) della linea 04 dalle ore 06:00 alle ore 14:00 del giorno 17 Maggio, con data, ora, differenza temporale tra due eventi in minuti, tipo di evento, descrizione della causa di fermo, tipo di fermo, setup (1,0)+Get the DMG LB (821102A) downtime events list from 06:00 to 14:00 of June 18th 2018 with date, time, duration in minutes, event type, downtime cause description, downtime kind, changeover time flag
 <code sql> <code sql>
-select data, ora, tmdiff/60 as dtm, tipoevento, causafm.descrizio as dfermo, causafm.tipofm, causafm.setup from regfm +select data, ora, tmdiff/60 as dtm, tipoevento, qcausafm.descrizio as dfermo, qcausafm.tipofm, qcausafm.setup from shareddata.regfm 
-inner join causafm on causafm.codice=regfm.caufm +inner join shareddata.qcausafm on shareddata.qcausafm.codice=regfm.caufm 
-where codubi='04' and data='20170517' and ora>='06:00' and ora<='14:00'+where codubi='821102A' and data='20180618' and ora>='06:00' and ora<='14:00'
 order by tm order by tm
 </code> </code>
-{{:cli:elica:2017-05-18.gif|}} 
  • cli/franke/shared_data.1535365517.txt.gz
  • Last modified: 2018/08/27 12:25
  • by f.zoppini