Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3

Ansprechpartner

Konzept (technisch):

Bernd Althaus

Betrieb

Anna Schuh

Durchführung
am 15. monatlich per Crontab:

Panel

 00 06 15  *  *          /pica/v_cbs/ddb/jobs/ddb_prepare_minitab.sh fidfnidf                      ** Standard Durchlaufzeiten **
 00 12 15  *  *          /pica/v_cbs/ddb/jobs/ddb_prepare_minitab_a.sh fidfnidf                  ** Durchlaufzeiten von Datensätzen, die Status a erhalten haben **
 00 15 15  *  *          /pica/v_cbs/ddb/jobs/ddb_prepare_minitab_b.sh fidfnidf                  ** Durchlaufzeiten von Datensätzen, die Status b erhalten haben **
 

Standard

select distinct convert(char(8),recordstamp), title_number, updated_by_sourcecode, new_material_tag
from dbs_title_log where title_fileset = 21 and title_number in
(
select distinct(title_number) from dbs_title_log where title_fileset = 21 and
recordstamp > "$WORKING_DATE_1" and recordstamp < "$WORKING_DATE_2" and ((updated_by_sourcecode in ("1166","1266") and new_material_tag in
("Aa","Af","AF","Ba","Bf","BF","Ea","Ef","EF","Ka","Kf","KF","Oa","Of","OF","Sa","Sf","SF","Ca","Cf","CF","Za") )
or (updated_by_sourcecode in ("1150","1250") and new_material_tag in ("Oa","Oc","Of","OF")))
) and recordstamp > "2005010100000000" and recordstamp < "$CURRENT_DATE_1"
and updated_by_sourcecode in ("1130","1131","1140","1141","1145","1150","1166","1230","1240","1241","1245","1250","1266","1138","1139","1149","1239","1249")
order by title_number
go

Status a

select distinct convert(char(8),recordstamp) as recordstamp,
title_number, updated_by_sourcecode, new_material_tag
from dbs_title_log
where title_number in (
select distinct(title_number)
 from dbs_title_log where title_fileset = 21
 and recordstamp > "$WORKING_DATE_1"
 and recordstamp < "$WORKING_DATE_2"
 and updated_by_sourcecode in ("1150","1250")
 and extra2 = "a"
 and extra1 != extra2
)
and recordstamp > "2007010100000000"
and title_fileset = 21
and title_level = 0
and ((extra1 != extra2) or (updated_by_sourcecode in ("1130", "1138", "1139", "1149", "1230", "1239", "1249", "1166","1266")))
order by title_number
go

Status b

select distinct convert(char(8),recordstamp), title_number, updated_by_sourcecode, new_material_tag
from dbs_title_log
where title_number in (
select distinct(title_number)
 from dbs_title_log where title_fileset = 21
 and recordstamp > "$WORKING_DATE_1"
 and recordstamp < "$WORKING_DATE_2"
 and updated_by_sourcecode in ("1140","1141","1240","1241")
 and extra2 in ("b","os")
 and extra1 != extra2
)
and recordstamp > "2007010100000000"
and title_fileset = 21
and title_level = 0
and ((extra1 != extra2) or (extra1 = NULL))
and updated_by_sourcecode in ("1130","1131","1140","1141","1150","1166","1230","1240","1241","1250","1266","1138","1139","1149","1239","1249")
order by title_number
go

Fachliche Vorgaben

Relevante Satzarten (Pica3-Kategorie 0500, Pica+-Kategorie 002@) sind:

Panel

("Aa","Ac","Af","AF","Ba","Bc","Bf","BF","Ea","Ec","Ef","EF","Ka","Kc","Kf","Oa","Oc","Of","OF","Sa","Sc","Sf","SF","Ca","Cc","Cf","CF","Za","Zc")

Durch entsprechende (evtl. geschachtelte) SELECT-Anweisungen sollten folgende Informationen ermittelt werden:

1. Erstellung einer IPN-Liste (title_number), der Datensätze, die

  • eine der 2-stelligen relevanten Satzarten enthält und
  • im aktuellen Monat durch eine der beiden Magazinkennungen ("1166","1266") korrigiert wurde.

Doppelte IPNs sind zu entfernen!

2. Anhand dieser IPN-Liste sollen danach alle Änderungen an den Datensätzen innerhalb des letzten Jahres ermittelt werden. Dabei sollen der Zeitstempel, die IPN, die Änderungskennung und die Satzart ausgegeben werden. Doppelte Einträge sind zu entfernen.

Die vollständige SELECT-Anweisung:

Panel

select distinct convert(char(8),recordstamp), title_number, updated_by_sourcecode, new_material_tag
from dbs_title_log where title_fileset = 21 and title_number in
(select title_number from dbs_title_log where title_fileset = 21 and
recordstamp > "$WORKING_DATE_1" and recordstamp < "$WORKING_DATE_2" and
updated_by_sourcecode in ("1166","1266") and new_material_tag in
("Aa","Ac","Af","AF","Ba","Bc","Bf","BF","Ea","Ec","Ef","EF","Ka","Kc","Kf","Oa","Oc","Of","OF","Sa","Sc","Sf","SF","Ca","Cc","Cf","CF","Za","Zc")) and
recordstamp > "2005010100000000" and recordstamp < "$CURRENT_DATE_1"
and updated_by_sourcecode in ("1130","1131","1140","1141","1145","9831","9841","1150","1166","1230","1240","1241","1245","9832","9842","1250","1266","1138","1139","1149","1239","1249")
order by title_number
go

Anforderung Fr. Meinel am 12.02.08:

Panel

select distinct convert(char(8),recordstamp), title_number, updated_by_sourcecode, new_material_tag
from dbs_title_log where title_fileset = 21 and title_number in
(
select distinct(title_number) from dbs_title_log where title_fileset = 21 and
recordstamp > "$WORKING_DATE_1" and recordstamp < "$WORKING_DATE_2" and ((updated_by_sourcecode in ("1166","1266") and new_material_tag in
("Aa","Ac","Af","AF","Ba","Bc","Bf","BF","Ea","Ec","Ef","EF","Ka","Kc","Kf","Oa","Oc","Of","OF","Sa","Sc","Sf","SF","Ca","Cc","Cf","CF","Za","Zc") )
or (updated_by_sourcecode in ("1150","1250") and new_material_tag in ("Oa","Oc","Of","OF")))
) and recordstamp > "2005010100000000" and recordstamp < "$CURRENT_DATE_1"
and updated_by_sourcecode in ("1130","1131","1140","1141","1145","9831","9841","1150","1166","1230","1240","1241","1245","9832","9842","1250","1266","1138","1139","1149","1239","1249")
order by title_number
go
EOF

Das Ergebnis der SELECT-Anweisungen wird mit Hilfe des Scriptes bearbeitet:

  • doppelte Zeilen bezüglich IPN, Sourcecode und Satzart werden entfernt,
  • die Datumangabe wird formatiert
  • sonstige Bereinigungen werden durchgeführt

Die Ergebnisdatei dieser Umformatierungen wird mit Hilfe des Perl-Scriptes /pica/v_cbs/ddb/sql/minitab/minitab.pl (erstellt von Lorand Bruhacs) in eine Input-Datei für die Anwendung MiniTab umgewandelt:

Ort

Satzart

IPN

Erfassung

SG-Vergabe

Objekt im Haus

FE

SE

eigenes Magazin

Magazin anderer Standort

F

Aa

98023134

2006-06-22

2006-06-22

2006-07-18

2006-07-21

2006-07-26

 

2006-07-28

F

Aa

98023239

2006-06-22

2006-06-22

2006-07-23

2006-07-29

2006-07-25

2006-07-21

2006-07-28

F

Aa

98023380

2006-06-22

2006-06-22

2006-07-18

2006-07-20

2006-07-26

2005-06-22

2006-07-28

L

Aa

98031847

2006-06-29

 

2006-07-18

2006-07-20

2006-07-25

2006-07-28

2006-07-27

L

Aa

98031933

2006-06-29

 

2006-07-18

2006-07-20

2006-07-25

2006-07-28

2006-07-27

L

Aa

98032021

2006-06-29

2006-06-29

2006-06-30

2006-07-12

2006-07-24

2006-07-28

 

L

Aa

98032388

 

 

2006-06-29

2006-07-25

2005-06-27

2006-07-28

 

Regeln für die Bildung der einzelnen Spalteninhalte

Ort

=L, wenn Kennung 1145, 1130, 1131, 1138, 1139, 1149, 1140, 1150, 9831, 9841 vorkommt
=F, wenn Kennung 1245, 1230, 1239, 1249, 1240, 1250, 9832, 9842 vorkommt

Satzart

die Satzart ist einer der Zeilen 1166, 1266, 1150 oder 1250(wegen Satzarten O)* zu entnehmen (immer 2-stellig!) oder bei erstmaligem Vorkommen und evtl 3. Pos entfernen

IPN

bei erstmaligem Vorkommen zu übernehmen

Erfassung

Satzart ist 3-stellig und erstmaliges Vorkommen der Kennungen 1145, 1130, 1131, 1245, 1230

SG-Vergabe

Satzart ist 3-stellig und erstmaliges Vorkommen der Kennungen 1140 oder 1240

Objekt im Haus

Satzart ist 3-stellig und erstmaliges Vorkommen der Kennungen 1138, 1139, 1149, 1239, 1249, 9831, 9832, 9841, 9842

FE

Satzart ist 2-stellig und erstmaliges Vorkommen der Kennungen 1140, 1141, 1240 oder 1241
neu am 25.2.2009: für minitab_a und minitab_b: Statusänderung nach "b"

SE

Satzart ist 2-stellig und erstmaliges Vorkommen der Kennungen 1150 oder 1250
neu am 25.2.2009: für minitab_a und minitab_b: Statusänderung nach "a"

eigenes Magazin

Satzart ist 2-stellig und erstmaliges Vorkommen der Kennung 1166 bei Ort=L oder der Kennung 1266 bei Ort=F

anderes Magazin

Satzart ist 2-stellig und erstmaliges Vorkommen der Kennung 1166 bei Ort=F oder der Kennung 1266 bei Ort=L