68 lines
1.7 KiB
SQL
Executable File
68 lines
1.7 KiB
SQL
Executable File
-- An welchen Dienststellen waren die Studenten im Jahrgang 08
|
|
|
|
SELECT t1.saaid
|
|
, w.bezeichnung
|
|
, t1.uid
|
|
, x.vorname
|
|
, x.nachname
|
|
, t1.beginn
|
|
, t1.ende
|
|
, t1.abteilung
|
|
, x.hs
|
|
, y.bez
|
|
, y.str
|
|
, y.plz
|
|
, y.postfach
|
|
, y.plz_postfach
|
|
, y.ort
|
|
, y.mail
|
|
FROM stan_antrag t1, stan_vertiefungsbereich w, stud x, stan_dienststellen y, stan_stellenart z
|
|
WHERE t1.uid=x.uid
|
|
AND t1.dst_id=y.dst_id
|
|
AND t1.vert_bereich=w.vbid
|
|
AND z.artid=6
|
|
AND t1.status = (
|
|
SELECT MAX(t2.status)
|
|
FROM stan_antrag t2
|
|
WHERE t1.aendid = t2.aendid
|
|
AND t2.status !='2'
|
|
)
|
|
AND (t1.zuweisung is NULL OR t1.zuweisung='Z')
|
|
and upper(y.ort) like '%BADEN%'
|
|
and beginn >= '2016-07-15'
|
|
and ende <= '2017-09-14'
|
|
union
|
|
SELECT t1.saaid
|
|
, w.bezeichnung
|
|
, t1.uid
|
|
, x.vorname
|
|
, x.nachname
|
|
, t1.beginn
|
|
, t1.ende
|
|
, t1.abteilung
|
|
, x.hs
|
|
, y.bez
|
|
, y.str
|
|
, y.plz
|
|
, y.postfach
|
|
, y.plz_postfach
|
|
, y.ort
|
|
, NULL mail
|
|
FROM stan_antrag t1, stud x, stan_vertiefungsbereich w, stan_dienststellen_tmp y, stan_stellenart z
|
|
WHERE t1.uid=x.uid
|
|
AND t1.dst_id=y.dst_id
|
|
AND t1.vert_bereich=w.vbid
|
|
AND z.artid=t1.art
|
|
AND t1.status = (
|
|
SELECT MAX(t2.status)
|
|
FROM stan_antrag t2
|
|
WHERE t1.aendid = t2.aendid
|
|
AND t2.status !='2'
|
|
)
|
|
AND (t1.zuweisung is NULL OR t1.zuweisung='Z')
|
|
and upper(ort) like '%BADEN%'
|
|
and beginn >= '2016-07-15'
|
|
and ende <= '2017-09-14'
|
|
ORDER BY hs, uid, beginn
|
|
|
|
|