﻿select 
Betrieb,  
Stufe2, 
FAGNR, 
FTYPE,
KNDNR,
KNAME, 
Stufe5, 
REDAT, 
O500_HW2BUCHGBETR,  
FLDAT as FLDAT_von, 
FLDAT as FLDAT_bis, 
FLDAT, 
O500_MAHNSTUFE, 
Next_MAHNSTUFE, 
O500_HW2BETR_OFFEN, 
OPDAT,
O500_BELEGNR1,
Ktelf, 
Kstra, 
Kdplz, 
Kdort,  
Bemerkung, 
Wiedervorlage, 
Bemerkung_2, 
Bearbeitet, 
Mahnen, 
`User`, 
Aenderungsdatum,  
User_2, 
Aenderungsdatum_u2,
KZALA, 
Versicherung,
Rechtsanwalt,
Reklamation, 
Selbstbeteiligung, 
SelbstbeteiligungBetrag, 
Mwst, 
MwstBetrag,
Reserve_1,
Reserve_3,
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_ahr_report

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

and

case 
  when [userid] IN ('aet','afr','afo','asd','asf','asi','azn','bc','bsc','bz','ch','cbo','cgr','cho','cke','cki','cpe','cwd','de','dek','dfa','dfi','dki','dsu','eeb','ef','egi','eh','ekd','el','em','es','fau','fdo','fe','fgh','fhs','fhu','fsc','gcu',
  'ge','gj','gk','hh','hce','hfr','hk','ho','ite','jal','jb','jhe','jhl','jkn','jmr','jg','jre',
  'kh','kgr','kni','kta','kwe','kwi','lgi','lkr','lm','lre','lt','lwr','mb','mdi','mdk','me','mei','mf','mfr','mge','mgi','mmi','mop','msi','msu','mtr','mwe','nba','nf','nfi','ngu','nke','nna','nsa','nsc','nsm','nwa','obo','oke','ost','pkr','pma','psc','pwi',
  'rhi','rm','ro','rr','sbr','sft','ssr','tre','skr','smr','spa','spe','ssh','sso','thg','tgn','tke','tkh','tno','ts','tsp','tze','var','vfe','vko','wb','we','wh','wk','wm','ws','wsc','yko','yun') then stufe2 IN ('SC')
  when [userid] IN ('ach','asa','atz','bs','bsr','cfo','cla','cle','cw','dbi','dwe','ead','esr','fs','gmo','js','kk','ksc','mae','lmo','man','mmz','msd','mrr','mvk','mvs','ng',
  'or','pjr','psh','rcl','rti','sb','sd','se','sf','shu','sl','spr','ssg','st','sz','ta','thc','tl','tpa','tsa','tzg','tzw','usc','ut') then stufe2 IN ('TZ')
  else 1 end

and 

case 
  when [userid] IN ('sz') then betrieb IN ('VÖH','KRU','LL')
  when [userid] IN ('me') then betrieb IN ('VÖH','KRU','ULM')
  when [userid] IN ('ch','lre','ws') then betrieb IN ('MM','VÖH')
  when [userid] IN ('el','wsc') then betrieb IN ('MM','KRU')
  when [userid] IN ('nsa') then betrieb IN ('MM','KRU','GZ','LL','ULM')
  when [userid] IN ('ef') then betrieb IN ('MM','LL')
  when [userid] IN ('sd') then betrieb IN ('VÖH','KRU')
    when [userid] IN ('??') then betrieb IN ('GZ','KRU')
   when [userid] IN ('mvo', 'lmo') then betrieb IN ('GZ')
  when [userid] IN ('jb') then betrieb IN ('VÖH','ULM')
  when [userid] IN ('kh','wh') then betrieb IN ('KRU','ULM')
  when [userid] IN ('fe') then betrieb IN ('KRU','LL')
  when [userid] IN ('lgl') then betrieb IN ('ULM','KRU')
  when [userid] IN ('aet','afr','ag','asf','atz','bv','bsc','bsr','bz','cbo','cki','cpe','dek','dfa','egi','eh','es','fau','fhu','gcu','gk','hh','hk','jal','jg','js','jre','khe','kk','kni','kwi','ma','mmz','mrr','mrs','msu','mwe','nfi','ng','oke','pkr','pjr','pwi','rhi','rm','ro','sf','sl','smr','tke','tma','ut','we','yun') then betrieb IN ('MM')
  when [userid] IN ('gj','cw','st') then betrieb IN ('VÖH')
  when [userid] IN ('asd','dbi','shu','ssr','rr','rcl','jj','kkl','spe','vwi') then betrieb IN ('KRU')
  when [userid] IN ('afo','mmi','csi','dsu','mdi','ngu','ite','jhl','spr','vko') then betrieb IN ('GZ')
  when [userid] IN ('ach','asi','asl','awr','azn','bs','cgr','cla','cle','de','dfi','dki','dsm','dwe','ead','eeb','ekd','esr','fce','fgh','fs','fsc','gml','gmo','hce','hja','ho','jkn','jmr','kta','ksc','lgi','lbr','lkr','lt','lwr','mb','mdk','mei','mge','mgi','mtr','nba','nke','nf','nsm','nwa',
  'obo','or','psc','psh','pz','rl','rti','sb','se','tre','sbr','ssg','sis','ta','tgn','thg','tkh','tno','tsa','tsp','tze','tzg','tzw','um','usc','vdu','vfe','vko','wb','wm') then betrieb IN ('ULM')
  when [userid] IN ('agr','ak','ala','asa','ash','bc','cfo','cho','cwd','dp','fdo','feb','fkn','hfr','jdr','jhe','kgr','kwe','lra','mae','man','mfr','mop','msd','msi','mvk','mvs','mzo','nna','nst','pma','sft','spa','ssh','thc','tl','tpa','ts','var','yko') then betrieb IN ('LL')
  else 1 end

order by FLDAT asc