#BRJAVA
Input
$Var0#Name:##
$Var1#Vorname:##
$Var2#Zeitraum von:#01.01.2007#$DATUM
$Var3#Zeitraum bis:#01.01.2009#$DATUM
SQL
Select distinct sba_vg_akz as AKZ,
bt.bt_name as Name,
bt.bt_vorname as Vorname,
bs.bs_name as Betriebsstätte,
sba_vg_kennz as KFZKennz,
sba_vg_erfdat=BR_DATUM as Erfassungsdatum,
sba_vg_manr as Mitarbeiter,
agkatde1.txt1 as Status,
erfdat=BR_DATUM as 'Datum der SE',
verstbetrag as Bussgeld,
betrag_euro as 'festgesetzter Betrag',
agkatde2.txt1 as 'letzte Sachentscheidung'
from bt
join sba_vgkontroll on
sba_vgkontroll.sba_vg_btnr = bt.bt_nr
and sba_vgkontroll.gueltig_bis = 0
and sba_vgkontroll.sba_vg_erfdat >= '$Var2_NORM_DATUM'
and sba_vgkontroll.sba_vg_erfdat >= '$Var3_NORM_DATUM'
left outer join agkatde as agkatde1 on
agkatde1.subid = 207
and agkatde1.nr = sba_vgkontroll.sba_se
and agkatde1.guelbis > 2000000000
join bs on
sba_vgkontroll.sba_bs_nr = bs.bs_nr
and bs.gueltig_bis = 0
left outer join sba_bg_gesamt on
sba_bg_gesamt.akz = sba_vgkontroll.sba_vg_akz
and sba_bg_gesamt.gueltig_bis = 0
left outer join sba_dok on
sba_dok.akz = sba_vgkontroll.sba_vg_akz
and sba_dok.lfdnr = (select max(lfdnr)
from sba_dok
where sba_dok.akz = sba_vgkontroll.sba_vg_akz
#and sba_dok.betrag_euro > 0
)
left outer join agkatde as agkatde2 on
agkatde2.subid = 207
and agkatde2.nr = sba_dok.se_nr
and agkatde2.guelbis > 2000000000
where bt.bt_name_norm like '$Var0_NORM%'
and bt.bt_vorname_norm like '$Var1_NORM%'
and bt.gueltig_bis = 0
order by bt.bt_name
Report
Table
|
