﻿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_rem_report

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

and 

case
  when [userid] IN ('aba','bwo','mba','sop') then stufe2 IN ('NA')
  when [userid] IN ('hko','sc','sv','fpo','fda') then stufe2 IN ('GA')
  else 1 end
  
and 

case
  when [userid] IN ('mh') then betrieb IN ('MM','VÖH','KRU','ULM')
  when [userid] IN ('mc') then betrieb IN ('MM','VÖH','KRU','LL')
  when [userid] IN ('mj') then betrieb IN ('MM','KRU','ULM','LL')
  when [userid] IN ('hg') then betrieb IN ('VÖH','KRU','ULM','LL')
  when [userid] IN ('??') then betrieb IN ('MM','LL')
  when [userid] IN ('be') then betrieb IN ('VÖH','KRU')
  when [userid] IN ('mba') then betrieb IN ('MM','KRU')
  when [userid] IN ('??') then betrieb IN ('GZ','KRU')
  when [userid] IN ('mvo') then betrieb IN ('GZ')
  when [userid] IN ('aho','azh','bv','fpo','hko','jlt','jsh','jti','mga','sc','sko','sop') then betrieb IN ('MM')
  when [userid] IN ('mba','kkl') then betrieb IN ('KRU')
  when [userid] IN ('asl','bta','bwo','fda','lho','mn','mne','mvo','nba','rl','rp','son','ssu','tbo','um','vb','vs') then betrieb IN ('ULM')
  when [userid] IN ('ak','ala','ash','bb','dp','jdr','muy','mzo','nst','rhe','skr','sv','ype') then betrieb IN ('LL')
  when [userid] IN ('cab','fd','aho','mk','ka','fl','tb','gr','kgu','hbo','sbe','hp',
					'fja','jja','twa','sdo','tbe','ba','af','vpo','mku','rwo','osa','ipl',
					'sse','rze','aan','tal','wr','mma') then kdbnr = [userid]
  else 1 end

order by FLDAT asc