#BRJAVA
Input
$Var0#Name:###bt.bt_name_norm like '$Var0_NORM%'
$Var1#Vorname:###bt.bt_vorname_norm like '$Var1_NORM%'
$Var2#Zeitraum von:#01.01.2007#$DATUM#sba_vgkontroll.sba_vg_erfdat >= '$Var2_NORM_DATUM'
$Var3#Zeitraum bis:#01.01.2009#$DATUM#sba_vgkontroll.sba_vg_erfdat <= '$Var3_NORM_DATUM'
SQL
Select distinct sba_vgkontroll.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
left join sba_schaub on
sba_schaub.sb_fahrer = bt.bt_nr
and sba_schaub.gueltig_bis = 0
join sba_vgkontroll on
sba_vgkontroll.gueltig_bis = 0
left join sba_zug on
sba_zug.sb_nr = sba_schaub.sb_nr
and (sba_vgkontroll.sba_vg_akz = sba_zug.akz
or sba_vgkontroll.sba_vg_akz = sba_zug.akz_zugeordnet)
and sba_zug.gueltig_bis = 0
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 $Var0
and $Var1
and $Var2
and $Var3
and bt.gueltig_bis = 0
order by sba_vgkontroll.sba_vg_akz
Report
Table
|
