﻿select
Betrieb,
Stufe2, 
FAGNR, 
FTYPE,
KNDNR,
KNAME, 
Stufe5, 
REDAT, 
O500_HW2BUCHGBETR, 
FLDAT as FLDAT_von, 
FLDAT as FLDAT_bis, 
FLDAT, 
O500_MAHNSTUFE, 
Next_MAHNSTUFE,  
ZA_1,
ZA_2,
ZA_3,
O500_HW2BETR_OFFEN, 
OPDAT,
O500_BELEGNR1,
Ktelf, 
Kstra, 
Kdplz, 
Kdort,  
Bemerkung, 
Wiedervorlage, 
Bemerkung_2, 
Bearbeitet, 
Mahnen, 
`User`, 
Aenderungsdatum,  
User_2, 
Reklamation, 
Aenderungsdatum_u2,
geplAuslief_NA, 
Auslief_NA, 
geplAuslief_GA, 
Auslief_GA, 
LKNAME, 
LKTELF, 
LKSTRA, 
LKDPLZ, 
LKDORT,
Reserve_1,
Reserve_3,
shipment_date,
customer_number_leasing,
customer_name_leasing,
text1,
text2,
round((unix_timestamp(current_date())-unix_timestamp(FLDAT))/86400) as "Faell_Tage",
case 
  when (round((unix_timestamp(current_date())-unix_timestamp(FLDAT))/86400) < 14 ) then '0-2 Wochen'  
  when (round((unix_timestamp(current_date())-unix_timestamp(FLDAT))/86400) < 28 )then '2-4 Wochen' 
  when (round((unix_timestamp(current_date())-unix_timestamp(FLDAT))/86400) < 42 )then '4-6 Wochen' 
  when (round((unix_timestamp(current_date())-unix_timestamp(FLDAT))/86400) >= 42 ) then '> 6 Wochen'   
  else 'n.v.' 
end as "Wochen"

from fm_aha_report

where stufe2 NOT IN ('SC','TZ')

and 

case
  when [userid] IN ('girardini','mba') then stufe2 IN ('NA')
  when [userid] IN ('rosenkranz') then stufe2 IN ('GA')
  when [userid] IN ('dk') then kndnr NOT IN ('580000','690121','690122','690123','GARANTIE')
  else 1 end
  
and 

case
  when [userid] IN ('carville') then betrieb IN ('GÖG')
  when [userid] IN ('ortner') then betrieb IN ('WTB')
  when [userid] IN ('karas') then betrieb IN ('LEH')
  when [userid] IN ('wecker') then betrieb IN ('GÖG','LEH')
  when [userid] IN ('pho') then betrieb IN ('GÖG','WTB','LEH')
  when [userid] IN ('preis') then betrieb IN ('GÖG','LEH','WTB')
  else 1 end

order by FLDAT asc