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:30]
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>
 Count produced items after June 18th 2018 (included) grouped by day, article code and work order on production resource 821102A (DMG LB) 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='821102A' and regprod.data>='20180618' where regprod.codubi='821102A' and regprod.data>='20180618'
 group by data, dart, procord group by data, dart, procord
Line 90: Line 91:
 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 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='821102A' and data='20180618' 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/franke/shared_data.1535365843.txt.gz
  • Last modified: 2018/08/27 12:30
  • by f.zoppini