SELECT p.persnr PersNr,
CASE WHEN p.gschl = 'M' THEN 'Herr' ELSE 'Frau' END AS Anrede,
z.vorname AS Vorname,
z.anredename AS Nachname,
p.str AS Strasse,
SUBSTRING(p.ort,1,5) AS PLZ,
SUBSTRING(p.ort,7,50) AS Ort
{sReplace1}
FROM [pinfo1] pi1 {sReplace2}, [einaus] e, [p] p, [pstlz] z
{sReplace3}
WHERE p.persnr = z.persnr
AND p.persnr = e.persnr
AND (e.aus IS NULL OR e.aus > GetDate())
AND e.ein <= GetDate()
AND p.persnr = pi1.persnr
AND pi1.panel = 0
{sReplace4}
AND ({sReplace5})
SELECT *, CASE WHEN G25 > '' THEN 'Wir bieten Ihnen zusätzlich die G20 (Angebotsuntersuchung) an.' ELSE '' END AS Zusatz
FROM (
SELECT p.persnr PersNr,
CASE WHEN p.gschl = 'M' THEN 'Herr' ELSE 'Frau' END AS Anrede,
z.vorname AS Vorname,
z.anredename AS Nachname,
p.str AS Strasse,
SUBSTRING(p.ort,1,5) AS PLZ,
SUBSTRING(p.ort,7,50) AS Ort
{sReplace1},
YEAR(GETDATE() + 93) AS Jahr,
{sMonat} AS Monat,
{sMonatsEnde} AS MonatsEnde
FROM [pinfo1] pi1 {sReplace2}, [einaus] e, [p] p, [pstlz] z
{sReplace3}
WHERE p.persnr = z.persnr
AND p.persnr = e.persnr
AND (e.aus IS NULL OR e.aus > GetDate())
AND e.ein <= GetDate()
AND p.persnr = pi1.persnr
AND pi1.panel = 0
AND ({sReplace5}) ) x
SELECT p.persnr PersNr,
CASE WHEN p.gschl = 'M' THEN 'Herr' ELSE 'Frau' END AS Anrede,
z.vorname AS Vorname,
z.anredename AS Nachname,
p.str AS Strasse,
SUBSTRING(p.ort,1,5) AS PLZ,
SUBSTRING(p.ort,7,50) AS Ort
{sReplace1}
FROM [pinfo1] pi1 {sReplace2}, [einaus] e, [p] p, [pstlz] z
{sReplace3}
WHERE p.persnr = z.persnr
AND p.persnr = e.persnr
AND (e.aus IS NULL OR e.aus > GetDate())
AND e.ein <= GetDate()
AND p.persnr = pi1.persnr
AND pi1.panel = 0
AND ({sReplace5})
