Ansprechpartner
Konzept (technisch): |
|
Betrieb |
Durchführung
am 15. monatlich per Crontab:
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:
("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:
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:
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 |
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 |
SE |
Satzart ist 2-stellig und erstmaliges Vorkommen der Kennungen 1150 oder 1250 |
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 |
Überblick
Inhalte