{"text_format":"1","workbook":"xlsx","n_sheets":8,"sheets":["Einstellungen","Tabelle1","Daten","Sammelrechnung","Zusammensetzung Daten","Sammelrechnung_neu","SQL","SQL_Backup"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":1,"defined_names":13,"comments":0,"pivot_tables":2},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Einstellungen","index":0,"n_rows":185,"n_cols":5,"rows_kept":185,"cut":false,"hidden":false,"merged":[[2,0,2,1],[9,0,9,1]]}
["Einstellungen"]
[]
["Zeitraum Angabe\n(Datenspanne die aus Asebis geladen wird)\n"]
[]
["Von (dd.mm.jjjj)","2022-01-01"]
["Bis (dd.mm.jjjj)","2022-01-31"]
[]
["Inkl. ungerundete Stunden",true]
[]
["Datenverbindung"]
[]
["Datenbank-Server"]
["Datenbank-Name",null,null,"SO","ZH"]
[null,null,null,"Alle","Alle"]
[null,null,null,"Aedermannsdorf","Adlikon"]
["IV-Leistungen",true,null,"Aeschi SO","Adliswil"]
[null,null,null,"Aetigkofen","Aesch (ZH)"]
["Nicht-KLV-Pflichtige ",true,null,"Aetingen","Aeugst am Albis"]
["Leistungen ausschliessen",null,null,"Allerheiligenberg","Affoltern am Albis"]
[null,null,null,"Balm b. Günsberg","Altikon"]
[null,null,null,"Balm b. Messen","Andelfingen"]
[null,null,null,"Balmberg","Bachenbülach"]
[null,null,null,"Balsthal","Bachs"]
[null,null,null,"Bärschwil","Bäretswil"]
[null,null,null,"Bättwil","Bassersdorf"]
[null,null,null,"Bättwil-Flüh","Bauma"]
[null,null,null,"Beinwil SO","Benken (ZH)"]
[null,null,null,"Bellach","Berg am Irchel"]
[null,null,null,"Bettlach","Bertschikon"]
[null,null,null,"Biberist","Birmensdorf (ZH)"]
[null,null,null,"Bibern SO","Bonstetten"]
[null,null,null,"Biezwil","Boppelsen"]
[null,null,null,"Bolken","Brütten"]
[null,null,null,"Boningen","Bubikon"]
[null,null,null,"Breitenbach","Buch am Irchel"]
[null,null,null,"Brittern","Buchs (ZH)"]
[null,null,null,"Brügglen","Bülach"]
[null,null,null,"Brunnenthal","Dachsen"]
[null,null,null,"Büren SO","Dägerlen"]
[null,null,null,"Burgäschi","Dällikon"]
[null,null,null,"Büsserach","Dänikon"]
[null,null,null,"Däniken SO","Dättlikon"]
[null,null,null,"Deitingen","Dielsdorf"]
[null,null,null,"Derendingen","Dietikon"]
[null,null,null,"Dornach","Dietlikon"]
[null,null,null,"Dulliken","Dinhard"]
[null,null,null,"Egerkingen","Dorf"]
[null,null,null,"Eppenberg","Dübendorf"]
[null,null,null,"Erlinsbach SO","Dürnten"]
[null,null,null,"Erschwil","Egg"]
[null,null,null,"Etziken","Eglisau"]
[null,null,null,"Fehren","Elgg"]
[null,null,null,"Feldbrunnen","Ellikon an der Thur"]
[null,null,null,"Flüh","Elsau"]
[null,null,null,"Flumenthal","Embrach"]
[null,null,null,"Fulenbach","Erlenbach (ZH)"]
[null,null,null,"Gächliwil","Fällanden"]
[null,null,null,"Gänsbrunnen","Fehraltorf"]
[null,null,null,"Gempen","Feuerthalen"]
[null,null,null,"Gerlafingen","Fischenthal"]
[null,null,null,"Gossliwil","Flaach"]
[null,null,null,"Grenchen","Flurlingen"]
[null,null,null,"Gretzenbach","Freienstein-Teufen"]
[null,null,null,"Grindel","Geroldswil"]
[null,null,null,"Günsberg","Glattfelden"]
[null,null,null,"Gunzgen","Gossau (ZH)"]
[null,null,null,"Hägendorf","Greifensee"]
[null,null,null,"Halten","Grüningen"]
[null,null,null,"Härkingen","Hagenbuch"]
[null,null,null,"Hauenstein","Hausen am Albis"]
[null,null,null,"Heinrichswil","Hedingen"]
[null,null,null,"Herbetswil","Henggart"]
[null,null,null,"Hersiwil","Herrliberg"]
[null,null,null,"Hessigkofen","Hettlingen"]
[null,null,null,"Himmelried","Hinwil"]
[null,null,null,"Hochwald","Hirzel"]
[null,null,null,"Hofstetten SO","Hittnau"]
[null,null,null,"Holderbank SO","Hochfelden"]
[null,null,null,"Horriwil","Hofstetten (ZH)"]
[null,null,null,"Hubersdorf","Hombrechtikon"]
[null,null,null,"Hüniken","Horgen"]
[null,null,null,"Ichertswil","Höri"]
[null,null,null,"Kammersrohr","Humlikon"]
[null,null,null,"Kappel SO","Hüntwangen"]
[null,null,null,"Kestenholz","Hütten"]
[null,null,null,"Kienberg","Hüttikon"]
[null,null,null,"Kleinlützel","Illnau-Effretikon"]
[null,null,null,"Kriegstetten","Kappel am Albis"]
[null,null,null,"Küttigkofen","Kilchberg (ZH)"]
[null,null,null,"Kyburg-Buchegg","Kleinandelfingen"]
[null,null,null,"Langendorf","Kloten"]
[null,null,null,"Laupersdorf","Knonau"]
[null,null,null,"Lohn-Ammannsegg","Küsnacht (ZH)"]
[null,null,null,"Lommiswil","Kyburg"]
[null,null,null,"Lostorf","Langnau am Albis"]
[null,null,null,"Lüsslingen","Laufen-Uhwiesen"]
[null,null,null,"Luterbach","Lindau"]
[null,null,null,"Lüterkofen","Lufingen"]
[null,null,null,"Lüterkofen-Ichert","Männedorf"]
[null,null,null,"Lüterswil","Marthalen"]
[null,null,null,"Lüterswil-Gächli","Maschwanden"]
[null,null,null,"Mariastein","Maur"]
[null,null,null,"Matzendorf","Meilen"]
[null,null,null,"Meltingen","Mettmenstetten"]
[null,null,null,"Messen","Mönchaltorf"]
[null,null,null,"Metzerlen","Neerach"]
[null,null,null,"Mühledorf SO","Neftenbach"]
[null,null,null,"Mümliswil","Niederglatt"]
[null,null,null,"Nennigkofen","Niederhasli"]
[null,null,null,"Neuendorf","Niederweningen"]
[null,null,null,"Niederbuchsiten","Nürensdorf"]
[null,null,null,"Niedergösgen","Oberembrach"]
[null,null,null,"Niederwil SO","Oberengstringen"]
[null,null,null,"Nuglar","Oberglatt"]
[null,null,null,"Nunningen","Oberrieden"]
[null,null,null,"Oberbalmberg","Oberstammheim"]
[null,null,null,"Oberbuchsiten","Oberweningen"]
[null,null,null,"Oberdorf SO","Obfelden"]
[null,null,null,"Obergerlafingen","Oetwil am See"]
[null,null,null,"Obergösgen","Oetwil an der Limmat"]
[null,null,null,"Oberramsern","Opfikon"]
[null,null,null,"Oekingen","Ossingen"]
[null,null,null,"Oensingen","Otelfingen"]
[null,null,null,"Olten","Ottenbach"]
[null,null,null,"Ramiswil","Pfäffikon"]
[null,null,null,"Recherswil","Pfungen"]
[null,null,null,"Rickenbach SO","Rafz"]
[null,null,null,"Riedholz","Regensberg"]
[null,null,null,"Rodersdorf","Regensdorf"]
[null,null,null,"Rohr b. Olten","Rheinau"]
[null,null,null,"Rüttenen","Richterswil"]
[null,null,null,"Schnottwil","Rickenbach (ZH)"]
[null,null,null,"Schönenwerd","Rifferswil"]
[null,null,null,"Seewen SO","Rorbas"]
[null,null,null,"Selzach","Rümlang"]
[null,null,null,"Solothurn","Rüschlikon"]
[null,null,null,"St. Pantaleon","Russikon"]
[null,null,null,"Starrkirch-Wil","Rüti (ZH)"]
[null,null,null,"Steinhof SO","Schlatt (ZH)"]
[null,null,null,"Stüsslingen","Schleinikon"]
[null,null,null,"Subingen","Schlieren"]
[null,null,null,"Trimbach","Schöfflisdorf"]
[null,null,null,"Tscheppach","Schönenberg (ZH)"]
[null,null,null,"Unterramsern","Schwerzenbach"]
[null,null,null,"Walterswil SO","Seegräben"]
[null,null,null,"Wangen b. Olten","Seuzach"]
[null,null,null,"Weissenstein","Stadel"]
[null,null,null,"Welschenrohr","Stäfa"]
[null,null,null,"Winistorf","Stallikon"]
[null,null,null,"Winznau","Steinmaur"]
[null,null,null,"Wisen SO","Sternenberg"]
[null,null,null,"Witterswil","Thalheim an der Thur"]
[null,null,null,"Wolfwil","Thalwil"]
[null,null,null,"Wöschnau","Trüllikon"]
[null,null,null,"Zuchwil","Truttikon"]
[null,null,null,"Zullwil","Turbenthal"]
[null,null,null,null,"Uetikon am See"]
[null,null,null,null,"Uitikon"]
[null,null,null,null,"Unterengstringen"]
[null,null,null,null,"Unterstammheim"]
[null,null,null,null,"Urdorf"]
[null,null,null,null,"Uster"]
[null,null,null,null,"Volken"]
[null,null,null,null,"Volketswil"]
[null,null,null,null,"Wädenswil"]
[null,null,null,null,"Wald (ZH)"]
[null,null,null,null,"Wallisellen"]
[null,null,null,null,"Waltalingen"]
[null,null,null,null,"Wangen-Brüttisellen"]
[null,null,null,null,"Wasterkingen"]
[null,null,null,null,"Weiach"]
[null,null,null,null,"Weiningen (ZH)"]
[null,null,null,null,"Weisslingen"]
[null,null,null,null,"Wettswil am Albis"]
[null,null,null,null,"Wetzikon (ZH)"]
[null,null,null,null,"Wiesendangen"]
[null,null,null,null,"Wil (ZH)"]
[null,null,null,null,"Wila"]
[null,null,null,null,"Wildberg"]
[null,null,null,null,"Winkel"]
[null,null,null,null,"Winterthur"]
[null,null,null,null,"Zell (ZH)"]
[null,null,null,null,"Zollikon"]
[null,null,null,null,"Zumikon"]
[null,null,null,null,"Zürich"]

{"sheet":"Tabelle1","index":1,"n_rows":158,"n_cols":2,"rows_kept":158,"cut":false,"hidden":true,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["bfs","gde"]
[2421,"Aedermannsdorf"]
[2511,"Aeschi SO"]
[2441,"Aetigkofen"]
[2442,"Aetingen"]
[2579,"Allerheiligenberg"]
[2541,"Balm b. Günsberg"]
[2443,"Balm b. Messen"]
[2541,"Balmberg"]
[2422,"Balsthal"]
[2611,"Bärschwil"]
[2471,"Bättwil"]
[2476,"Bättwil-Flüh"]
[2612,"Beinwil SO"]
[2542,"Bellach"]
[2543,"Bettlach"]
[2513,"Biberist"]
[2444,"Bibern SO"]
[2445,"Biezwil"]
[2514,"Bolken"]
[2571,"Boningen"]
[2613,"Breitenbach"]
[2442,"Brittern"]
[2446,"Brügglen"]
[2447,"Brunnenthal"]
[2472,"Büren SO"]
[2511,"Burgäschi"]
[2614,"Büsserach"]
[2572,"Däniken SO"]
[2516,"Deitingen"]
[2517,"Derendingen"]
[2473,"Dornach"]
[2573,"Dulliken"]
[2401,"Egerkingen"]
[2574,"Eppenberg"]
[2503,"Erlinsbach SO"]
[2615,"Erschwil"]
[2518,"Etziken"]
[2616,"Fehren"]
[2544,"Feldbrunnen"]
[2476,"Flüh"]
[2545,"Flumenthal"]
[2575,"Fulenbach"]
[2456,"Gächliwil"]
[2423,"Gänsbrunnen"]
[2474,"Gempen"]
[2519,"Gerlafingen"]
[2449,"Gossliwil"]
[2546,"Grenchen"]
[2576,"Gretzenbach"]
[2617,"Grindel"]
[2547,"Günsberg"]
[2578,"Gunzgen"]
[2579,"Hägendorf"]
[2520,"Halten"]
[2402,"Härkingen"]
[2491,"Hauenstein"]
[2521,"Heinrichswil"]
[2424,"Herbetswil"]
[2522,"Hersiwil"]
[2450,"Hessigkofen"]
[2618,"Himmelried"]
[2475,"Hochwald"]
[2476,"Hofstetten SO"]
[2425,"Holderbank SO"]
[2523,"Horriwil"]
[2548,"Hubersdorf"]
[2524,"Hüniken"]
[2455,"Ichertswil"]
[2549,"Kammersrohr"]
[2580,"Kappel SO"]
[2403,"Kestenholz"]
[2492,"Kienberg"]
[2619,"Kleinlützel"]
[2525,"Kriegstetten"]
[2452,"Küttigkofen"]
[2453,"Kyburg-Buchegg"]
[2550,"Langendorf"]
[2426,"Laupersdorf"]
[2526,"Lohn-Ammannsegg"]
[2551,"Lommiswil"]
[2493,"Lostorf"]
[2454,"Lüsslingen"]
[2527,"Luterbach"]
[2455,"Lüterkofen"]
[2455,"Lüterkofen-Ichert"]
[2456,"Lüterswil"]
[2456,"Lüterswil-Gächli"]
[2477,"Mariastein"]
[2427,"Matzendorf"]
[2620,"Meltingen"]
[2457,"Messen"]
[2477,"Metzerlen"]
[2458,"Mühledorf SO"]
[2428,"Mümliswil"]
[2459,"Nennigkofen"]
[2404,"Neuendorf"]
[2405,"Niederbuchsiten"]
[2495,"Niedergösgen"]
[2552,"Niederwil SO"]
[2478,"Nuglar"]
[2621,"Nunningen"]
[2541,"Oberbalmberg"]
[2406,"Oberbuchsiten"]
[2553,"Oberdorf SO"]
[2528,"Obergerlafingen"]
[2497,"Obergösgen"]
[2460,"Oberramsern"]
[2529,"Oekingen"]
[2407,"Oensingen"]
[2581,"Olten"]
[2428,"Ramiswil"]
[2530,"Recherswil"]
[2582,"Rickenbach SO"]
[2554,"Riedholz"]
[2479,"Rodersdorf"]
[2498,"Rohr b. Olten"]
[2555,"Rüttenen"]
[2461,"Schnottwil"]
[2583,"Schönenwerd"]
[2480,"Seewen SO"]
[2556,"Selzach"]
[2601,"Solothurn"]
[2478,"St. Pantaleon"]
[2584,"Starrkirch-Wil"]
[2531,"Steinhof SO"]
[2499,"Stüsslingen"]
[2532,"Subingen"]
[2500,"Trimbach"]
[2462,"Tscheppach"]
[2463,"Unterramsern"]
[2585,"Walterswil SO"]
[2586,"Wangen b. Olten"]
[2553,"Weissenstein"]
[2429,"Welschenrohr"]
[2521,"Winistorf"]
[2501,"Winznau"]
[2502,"Wisen SO"]
[2481,"Witterswil"]
[2408,"Wolfwil"]
[2574,"Wöschnau"]
[2534,"Zuchwil"]
[2622,"Zullwil"]

{"sheet":"Daten","index":2,"n_rows":1,"n_cols":22,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["BfS Gemeinde-Nr.","ZSR-Nr. Leistungs-erbringer","Abrechnungs-Jahr","Fakturierungs-Monat","Nachname 1","Nachname 2","Vorname","Geschlecht","Geburtsdatum","Neue AHV-Nr.","Alte AHV-Nr.","Strasse","Nr.","Adresszusatz","PLZ","Orts","Leistungsdatum","Leistungsart","N-KLV-Tarif","Geleistete Stunden","Verrechnete Stunden","Pat.bet"]

{"sheet":"Sammelrechnung","index":3,"n_rows":32,"n_cols":4,"rows_kept":32,"cut":false,"hidden":true,"merged":[[0,0,1,0],[0,1,1,2],[0,3,1,3],[14,0,14,2],[20,0,20,3]]}
["Name/Organisation","Strasse/Nr.",null,"PLZ/Ort"]
[]
[]
["Städtische Gesundheitsdienste"]
["Spitex Kontraktmanagement"]
["Walchestrasse 31"]
["Postfach"]
["8021 Zürich"]
[]
[]
[]
["Ort eintragen"]
["Datum eintragen"]
[]
["Rechnung über die Pflegebeiträge der öffentlichen Hand an ambulante Leistungserbringer für Klientinnen und Klienten mit Wohnsitz in der Gemeinde:",null,null,"Hier Gemeinde eintragen"]
[]
["Abrechnungsmonate"]
["Abrechnungsjahr",2015]
[]
["Zahlungsverbindung (Name der Bank und IBAN-Nr.):"]
[]
[]
["Auszahlung erst nach erfolgreicher Prüfung"]
[]
["Leistungsart/Beitr.pro Std."]
["KLV A"]
["KLV B"]
["KLV C"]
["N-KLV"]
["KLV C UV/MV"]
["KLV B UV/MV"]
["Gesamtergebnis"]

{"sheet":"Zusammensetzung Daten","index":4,"n_rows":10,"n_cols":1,"rows_kept":10,"cut":false,"hidden":true,"merged":[]}
[]
[]
["Zeilenbeschriftungen"]
["KLV A"]
["KLV B"]
["KLV C"]
["N-KLV"]
["KLV C UV/MV"]
["KLV B UV/MV"]
["Gesamtergebnis"]

{"sheet":"Sammelrechnung_neu","index":5,"n_rows":46,"n_cols":4,"rows_kept":46,"cut":false,"hidden":true,"merged":[[41,0,41,3],[0,0,1,0],[0,1,1,2],[0,3,1,3],[3,2,6,3],[13,0,13,2]]}
["Vorname Nachname / Institution","Strasse, Nr.",null,"PLZ, Ort"]
[]
["ZSR-Nr."]
[null,null,"Nur gelb markierte Felder können bearbeitet werden. Bitte mit den erforderlichen Angaben überschreiben."]
[]
[]
[]
[]
[]
[]
["Ort "]
["Datum "]
[]
["Rechnung für die Pflegerestkosten an ambulante Leistungserbringer für Klientinnen und Klienten mit Wohnsitz in der Stadt Luzern"]
[]
["Abrechnungsmonate"]
["Abrechnungsjahr"]
[]
["Pflegeleistungen"]
["Leistungsart","Stunden","Beitrag pro Std.","Total Beitrag"]
["KLV A",{"$f":"SUMIF(Daten!$S$2:$S$9999,A21,Daten!$AK$2:$AK$9999)","v":0},56.25,{"$f":"ROUND((SUM(B21*C21))*20,0)/20","v":0}]
["KLV B",{"$f":"SUMIF(Daten!$S$2:$S$9999,A22,Daten!$AK$2:$AK$9999)","v":0},73.55,{"$f":"ROUND((SUM(B22*C22))*20,0)/20","v":0}]
["KLV C",{"$f":"SUMIF(Daten!$S$2:$S$9999,A23,Daten!$AK$2:$AK$9999)","v":0},64.7,0]
["KLV A IV",{"$f":"SUMIF(Daten!$S$2:$S$9999,A24,Daten!$AK$2:$AK$9999)","v":0},0,{"$f":"ROUND((SUM(B24*C24))*20,0)/20","v":0}]
["KLV B IV",{"$f":"SUMIF(Daten!$S$2:$S$9999,A25,Daten!$AK$2:$AK$9999)","v":0},0,{"$f":"ROUND((SUM(B25*C25))*20,0)/20","v":0}]
["KLV A UV/MV",{"$f":"SUMIF(Daten!$S$2:$S$9999,A26,Daten!$AK$2:$AK$9999)","v":0},67.099999999999994,{"$f":"ROUND((SUM(B26*C26))*20,0)/20","v":0}]
["KLV B UV/MV",{"$f":"SUMIF(Daten!$S$2:$S$9999,A27,Daten!$AK$2:$AK$9999)","v":0},63.1,{"$f":"ROUND((SUM(B27*C27))*20,0)/20","v":0}]
["KLV C UV/MV",{"$f":"SUMIF(Daten!$S$2:$S$9999,A28,Daten!$AK$2:$AK$9999)","v":0},56.3,{"$f":"ROUND((SUM(B28*C28))*20,0)/20","v":0}]
["Bruttobeitrag Total KLV",{"$f":"SUM(B21:B28)","v":0},null,{"$f":"SUM(D21:D23)","v":0}]
["abzüglich PaBe",null,null,{"$f":"-SUM(Daten!$AL$2:$AL$9999)","v":0}]
["Nettobeitrag Total KLV",null,null,{"$f":"SUM(D29:D30)","v":0}]
["AÜP A",{"$f":"SUMIF(Daten!$S$2:$S$9999,A32,Daten!$AK$2:$AK$9999)","v":0},66.650000000000006,{"$f":"ROUND((SUM(B32*C32))*20,0)/20","v":0}]
["AÜP B",{"$f":"SUMIF(Daten!$S$2:$S$9999,A33,Daten!$AK$2:$AK$9999)","v":0},65.599999999999994,{"$f":"ROUND((SUM(B33*C33))*20,0)/20","v":0}]
["AÜP C",{"$f":"SUMIF(Daten!$S$2:$S$9999,A34,Daten!$AK$2:$AK$9999)","v":0},58.1,0]
["Total AÜP",{"$f":"SUM(B32:B34)","v":0},null,{"$f":"SUM(D32:D34)","v":0}]
[]
["Total öffentliche Pflegebeiträge",null,null,{"$f":"SUM(D31+D35)","v":0}]
[]
[]
[]
["Zahlungsverbindung (Name der Bank und IBAN-Nr.)/ Name Kontoinhaber:"]
[]
[]
["Auszahlung erst nach erfolgreicher Prüfung"]
[]
["Beitrag pro Stunde = Vollkosten - Beitrag KLV"]

{"sheet":"SQL","index":6,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":true,"merged":[]}
["create table #tmp_leistungstarife\n(\n id    uniqueidentifier,\n leistungstarif varchar(50) collate database_default,\n bezeichnung  varchar(50) collate database_default,\n tarifcode  varchar(50) collate database_default,\n kat_id   uniqueidentifier\n)\n\ndeclare @item          varchar(120)\ndeclare @pos          int\ndeclare @xcount    int\ndeclare @new_id    uniqueidentifier\ndeclare @kat_id    uniqueidentifier\ndeclare @kanton varchar(3)='SO'\nselect @xcount = 1\n\nif ltrim(rtrim(@leistungsparam1)) <> ''\nbegin\n while rtrim (ltrim (@leistungsparam1)) <> ''\n begin\n  select @pos = charindex (',', @leistungsparam1)\n  if @pos = 0\n  begin\n   select @item = @leistungsparam1\n   select @leistungsparam1 = ''\n  end\n  else\n  begin\n   select @item = substring (@leistungsparam1, 0, @pos)\n   select @leistungsparam1 = substring (@leistungsparam1, @pos +1, len(@leistungsparam1) - @pos)\n  end\n  if rtrim (ltrim (@item)) <> ''\n  begin\n   if @xcount = 1 \n   begin\n    select @new_id = NEWID()\n    insert into #tmp_leistungstarife (leistungstarif, id) values (LTRIM(rtrim(@item)), @new_id)\n    select @kat_id = kat_id from katalog_leistungstarif where kat_nummer = LTRIM(rtrim(@item))\n    update #tmp_leistungstarife set kat_id = @kat_id where id = @new_id\n   end\n   else\n   begin\n    if @xcount = 2\n     update #tmp_leistungstarife set bezeichnung = LTRIM(rtrim(@item)) where id = @new_id\n    if @xcount = 3\n     update #tmp_leistungstarife set tarifcode = LTRIM(rtrim(@item)) where id = @new_id\n   end\n   select @xcount = @xcount + 1\n   if @xcount = 4\n   select @xcount = 1\n\n   end  \n end \nend\n\n---\n\ndeclare @traegerGmdeZHbfsNr int\nset @traegerGmdeZHbfsNr=(select kat_bfs_nr from katalog_ort_bfs where kat_gemeindename = @gemeinde)\n\ndeclare @ltt_HW uniqueidentifier, @ltt_Pflege uniqueidentifier, @ltt_AUP uniqueidentifier\nset @ltt_HW  = 'BB4D78F3-5993-4F05-8F75-F5F76BAB61D6'\nset @ltt_Pflege = 'EE5778DE-FDDD-4720-A066-66E6258B8916'\nset @ltt_AUP = '535AD8CA-76D1-4B27-A852-106F16F98853'\n \ndeclare @ltt_Pflege1 uniqueidentifier\ndeclare @ltt_Pflege2 uniqueidentifier\ndeclare @ltt_Pflege3 uniqueidentifier\n\nselect @ltt_Pflege1 = '1CBC540C-5B2A-4532-9DCE-7414EBA0CE3C'\nselect @ltt_Pflege2 = '2A7C47DE-2A9C-4E99-B820-76ED792989EA'\nselect @ltt_Pflege3 = '5DFB9A04-E205-479D-84D5-2D12CFDDEB50'\n\n\n-- fakturierte Stunden ermitteln\nif object_id('tempdb.dbo.#rec') is not null drop table #rec\n\n\nif @ignoreNichtKLV <> 0\nbegin\n set @ltt_HW  = '00000000-0000-0000-0000-000000000000'\nend\n\n\nselect rec.rec_id, rec.rec_kli_id, convert(date,rez.rez_datum) as rez_datum, rez.rez_preis, csm.set_text zsrNr, ktzTxt.ktx_text, lep.lep_vollkosten, rez.rez_preis as lep_preis, lep.lep_id\n , \n max(rec.rec_krankenkasse_nr) as rec_krankenkasse_nr, max(rec.rec_krankenkasse) as rec_krankenkasse,\n (isnull(sum(rez_anzahl),0))  vStd\n , isnull(sum(rez.rez_patientenbeteiligung_total_betrag),0) patBet\n --, isnull(sum(rez.rez_restfinanzierung_total_betrag),0) restFin\n , rez_leiart_kat_id\n  ,kobfs.kat_kantonskuerzel as Kanton\n  --,kobfs.kat_bezeichnung as M_Name\ninto #rec\nfrom rechnung_rec rec\n inner join rechnung_zeilen_rez rez on rez.rez_rec_id=rec.rec_id and rez.rez_show = 1 and isnull(rez_use_only_patbet,0) = 0\n inner join c_settings_mand AS csm ON csm.set_oe_owner = rec.rec_oe_rechnung and csm.set_name='Konkordatsnummer'\n inner join katalog_leistungstarif AS klt ON klt.kat_id = rez.rez_leiart_kat_id and klt.kat_leistungstarif_typ IN (@ltt_Pflege, @ltt_AUP, @ltt_HW, @ltt_Pflege1, @ltt_Pflege2, @ltt_Pflege3) \n inner join leistung_preis_lep AS lep on rez.rez_leiart_kat_id = lep.lep_leistungstarif  \n                and convert(date,lep.lep_datum_von) <= convert(date, rez.rez_datum)\n                and (ISNULL(convert(date,lep.lep_datum_bis), convert(date,rez.rez_datum)) >= convert(date,rez.rez_datum)) \n                and dbo.checkOE(rez.rez_oe_erfassung, lep.lep_oe_visibility) = 1\n left join katalog_tarifziffer AS ktz on ktz.kat_id = lep.lep_tarifziffer\n left join katalog_texte AS ktzTxt on ktz.kat_name = ktzTxt.ktx_id and ktzTxt.sprachnbr = 2055\n  inner join katalog_ort_bfs kobfs \n    on Case \n     when @gemeinde = 'Alle' AND kobfs.kat_id=rec_restfinanzierung_traegergemeinde AND kobfs.kat_kantonskuerzel=@kanton then 1\n     when kobfs.kat_id=rec_restfinanzierung_traegergemeinde AND kobfs.kat_bfs_nr=@traegerGmdeZHbfsNr then 1\n  else 0\n  End = 1\nwhere \nrec.rec_jahr * 100 + rec.rec_monat BETWEEN datepart(year, @von) * 100 + datepart(month, @von) \nAND datepart(year, @bis) * 100 + datepart(month, @bis)\n and rec.rec_storno_datum is null   \n --and klt.kat_nummer between '1300' and '1399'\n\n-- ---------------------------------------------------------------------------------------ab hier Kundenspezifisch für Kinderspitex\n-- and\n--(\n--        @useIV = 0 \n--             AND (klt.kat_nummer not between '1300' and '1399')\n--        ) \n--        OR \n--        (@useIV = 1 \n--             AND (klt.kat_nummer between '1300' and '1399')\n--      )\n-- ---------------------------------------------------------------------------------------bis hier Kundenspezifisch\n\n group by rec.rec_id, rec.rec_kli_id, convert(date,rez.rez_datum),  rez.rez_preis, csm.set_text, ktzTxt.ktx_text, lep.lep_vollkosten, lep.lep_id\n, rez.rez_leiart_kat_id,kobfs.kat_kantonskuerzel--,kobfs.kat_bezeichnung\norder by convert(varchar(100),rec.rec_kli_id), convert(date,rez.rez_datum), ktzTxt.ktx_text\n\n\nupdate #rec set ktx_text = 'N-KLV' where ktx_text is null\nupdate #rec set ktx_text = UPPER(ktx_text)\nupdate #rec set ktx_text = 'N-KLV' where ktx_text not in ('N-KLV', 'KLV A', 'KLV B', 'KLV C', 'N-KLV', 'AÜP A', 'AÜP B', 'AÜP C', 'KLV A UV/MV', 'KLV B UV/MV', 'KLV C UV/MV')\nupdate #rec set ktx_text = 'N-KLV' where ktx_text not in ('N-KLV', 'KLV A', 'KLV B', 'KLV C', 'N-KLV', 'AÜP A', 'AÜP B', 'AÜP C', 'KLV A UV/MV', 'KLV B UV/MV', 'KLV C UV/MV')\n\n\n-- Einsatzgrund ermitteln\n\ndeclare @rec_id  uniqueidentifier\ndeclare @rez_datum datetime\ndeclare @einsatzgrund nvarchar(120)\ndeclare @rec_kli_id uniqueidentifier\ndeclare @rez_leiart_kat_id uniqueidentifier\ndeclare @tarifbez varchar(50)\n\ndeclare c0 cursor local forward_only for \nselect rec_id, rez_datum, rec_kli_id, t.tarifcode, t.kat_id\nfrom #rec \ninner join #tmp_leistungstarife t on t.kat_id = rez_leiart_kat_id\n\nopen c0\n\nfetch next from c0 into @rec_id, @rez_datum, @rec_kli_id, @tarifbez, @rez_leiart_kat_id\nwhile @@fetch_status = 0\nbegin\n\n\n\n update #rec set ktx_text = isnull(@tarifbez,'') where rez_datum = @rez_datum and rec_id = @rec_id and rec_kli_id = @rec_kli_id and rez_leiart_kat_id = @rez_leiart_kat_id\n\n fetch next from c0 into @rec_id, @rez_datum, @rec_kli_id, @tarifbez, @rez_leiart_kat_id\nend\n\nclose c0\ndeallocate c0\n\n\n-- Ende Einsatzgrund\n\n\n-- patientenbeteiligung ermitteln\nif object_id('tempdb.dbo.#patbet') is not null drop table #patbet\nselect rec.rec_kli_id, convert(date,rez.rez_datum) as rez_datum, rez.rez_preis, csm.set_text zsrNr, ktzTxt.ktx_text, lep.lep_vollkosten, rez.rez_preis as lep_preis, lep.lep_id\n , \n max(rec.rec_krankenkasse_nr) as rec_krankenkasse_nr, max(rec.rec_krankenkasse) as rec_krankenkasse,\n\n (isnull(sum(rez_anzahl),0))  vStd\n\n , isnull(sum(rez.rez_patientenbeteiligung_total_betrag),0) patBet\n , isnull(sum(rez.rez_restfinanzierung_total_betrag),0) restFin \n ,kobfs.kat_kantonskuerzel as Kanton\n --,kobfs.kat_bezeichnung as M_Name\ninto #patbet\nfrom rechnung_rec rec\n inner join rechnung_zeilen_rez rez on rez.rez_rec_id=rec.rec_id and rez.rez_show = 1\n inner join c_settings_mand AS csm ON csm.set_oe_owner = rec.rec_oe_rechnung and csm.set_name='Konkordatsnummer'\n inner join katalog_leistungstarif AS klt ON klt.kat_id = rez.rez_leiart_kat_id and klt.kat_leistungstarif_typ IN (@ltt_Pflege, @ltt_AUP, @ltt_HW) \n inner join leistung_preis_lep AS lep on rez.rez_leiart_kat_id = lep.lep_leistungstarif \n                and convert(date, lep.lep_datum_von) <= convert(date, rez.rez_datum) \n                and (ISNULL(convert(date,lep.lep_datum_bis), convert(date,rez.rez_datum)) >= convert(date,rez.rez_datum)) \n                and dbo.checkOE(rez.rez_oe_erfassung, lep.lep_oe_visibility) = 1\n left join katalog_tarifziffer AS ktz on ktz.kat_id = lep.lep_tarifziffer\n left join katalog_texte AS ktzTxt on ktz.kat_name = ktzTxt.ktx_id and ktzTxt.sprachnbr = 2055\n  inner join katalog_ort_bfs kobfs \n    on Case \n     when (@gemeinde = 'Alle' AND kobfs.kat_id=rec_restfinanzierung_traegergemeinde) OR kobfs.kat_kantonskuerzel=@kanton then 1\n     when kobfs.kat_id=rec_restfinanzierung_traegergemeinde AND kobfs.kat_bfs_nr=@traegerGmdeZHbfsNr then 1\n  else 0\n  End = 1\nwhere rec.rec_jahr * 100 + rec.rec_monat BETWEEN datepart(year, @von) * 100 + datepart(month, @von) \nAND datepart(year, @bis) * 100 + datepart(month, @bis)\n and rec.rec_storno_datum is null \n \nand isnull(rez_use_only_patbet,0) = 1\n  \ngroup by rec.rec_kli_id, convert(date,rez.rez_datum),  rez.rez_preis, csm.set_text, ktzTxt.ktx_text, lep.lep_vollkosten, lep.lep_id,kobfs.kat_kantonskuerzel\n--,kobfs.kat_bezeichnung\norder by convert(varchar(100),rec.rec_kli_id), convert(date,rez.rez_datum), ktzTxt.ktx_text\n\nupdate #rec set patBet = #patbet.patBet\nfrom #rec \ninner join #patbet on #patbet.rec_kli_id = #rec.rec_kli_id and #patbet.rez_datum = #rec.rez_datum and #patbet.lep_id = #rec.lep_id\n\n --geleistete Stunden ermitteln\nif object_id('tempdb.dbo.#ler') is not null drop table #ler\nselect ler.ler_kli_id, convert(date,ler_tag) as ler_tag, isnull(sum(ler_dauer),0) gStd, ktzTxt.ktx_text, lep.lep_vollkosten, lep.lep_preis, lep.lep_id\ninto #ler\nfrom leistungstarif_erfassung_ler ler\n inner join katalog_leistungstarif klt on klt.kat_id=ler.ler_leistungstarif\n inner join leistung_preis_lep AS lep on ler.ler_leistungstarif = lep.lep_leistungstarif \n                and convert(date, lep.lep_datum_von) <= convert(date, ler.ler_tag) \n                and (ISNULL(convert(date,lep.lep_datum_bis), convert(date,ler.ler_tag)) >= convert(date,ler.ler_tag) )\n                and dbo.checkOE(ler.ler_oe_erfassung, lep.lep_oe_visibility) = 1\n left join katalog_tarifziffer AS ktz on ktz.kat_id = lep.lep_tarifziffer\n left join katalog_texte AS ktzTxt on ktz.kat_name = ktzTxt.ktx_id and ktzTxt.sprachnbr = 2055\nwhere convert(date, ler_tag) between @von and @bis\n and ler_kli_id is NOT NULL\ngroup by ler.ler_kli_id, convert(date,ler_tag), ktzTxt.ktx_text, lep.lep_vollkosten, lep.lep_preis, lep.lep_id\norder by convert(varchar(100),ler.ler_kli_id), convert(date,ler_tag), ktzTxt.ktx_text\n\n-- Strasse und Nr aus Adresse extrahieren\nif object_id('tempdb.dbo.#tk') is not null drop table #tk\nselect distinct uid_klient_pk, str_klient_adresse1, convert(int,0) pos, convert(nvarchar(max),'') strasse, convert(nvarchar(20),'') nr\ninto #tk\nfrom tbl_klienten\nwhere uid_klient_pk in (select rec_kli_id from #rec)\n\nupdate #tk set pos = PATINDEX('%[0-9]%', str_klient_adresse1) -- alles bis zum Beginn der HausNr wird als Strasse angesehen\nupdate #tk set pos = len(str_klient_adresse1) where pos = 0  -- keine HausNr vorhanden also ist alles Strasse\nupdate #tk set strasse = ltrim(rtrim(left(str_klient_adresse1, pos-1))), nr=substring(str_klient_adresse1,pos,20) where pos > 0\n\nalter table #tk add constraint PKtk primary key (uid_klient_pk)\n\nupdate #rec set lep_vollkosten = lep_preis where lep_vollkosten = 0.00\n\n---- Statistik Stadt ZH\nselect Distinct kli.kli_traegergemeinde_bfs_nr AS 'BfS Gemeinde-Nr.'\n , rec.zsrNr 'ZSR-Nr. Leistungs-erbringer'\n , datepart(yyyy,rec.rez_datum) 'Abrechnungs-Jahr', datepart(mm,rec.rez_datum) 'Fakturierungs-Monat'\n , tkli.str_klient_nachname AS 'Nachname 1',\n '' AS 'Nachname 2',\n tkli.str_klient_vorname AS 'Vorname',\n CASE tkli.int_klient_geschlecht_fk WHEN 2 THEN 'w' WHEN 1 THEN 'm' ELSE '' END AS 'Geschlecht',\n convert(VARCHAR, tkli.dat_klient_geburtsdatum, 104) AS 'Geburtsdatum',\n isnull(prs.prs_nnss,'') AS 'Neue AHV-Nr.',\n ISNULL(prs.prs_ahv,'') AS 'Alte AHV-Nr.',\n tk.Strasse, tk.nr AS 'Nr.',\n isnull(tkli.str_klient_adresse2,'') AS 'Adresszusatz',\n tkli.str_klient_plz AS 'PLZ',\n tkli.str_klient_ort AS 'Orts',\n convert(VARCHAR, rec.rez_datum, 104) AS 'Leistungsdatum',\n rec.ktx_text AS 'Leistungsart',\n \n  CASE rec.ktx_text  WHEN 'N-KLV' THEN (rec.lep_preis) ELSE 0.00 END AS 'N-KLV-Tarif',\n --case When @useUnroundedHours = 1 THEN ler.gStd else null end AS 'Geleistete Minuten',\n --rec.vStd AS 'Verrechnete Minuten',\n --isnull(ler.gStd/60,0) AS 'Geleistete Stunden',\n --isnull(rec.vStd/60,0)  AS 'Verrechnete Stunden',\n  CAST(ISNULL(ler.gStd,0) as decimal(5, 2)) / 60 AS 'Geleistete Stunden',\n  CAST(ISNULL(rec.vStd,0) as decimal(5, 2)) / 60 AS 'Verrechnete Stunden',\n  ISNULL(rec.patBet,0) AS 'Pat.bet'\n--,(rec.lep_vollkosten - rec.lep_preis) AS 'Restfinanzierung', \n-- (rec.lep_vollkosten - rec.lep_preis) * rec.vStd AS 'TotalMitPatBet',\n-- ((rec.lep_vollkosten - rec.lep_preis) * rec.vStd) - rec.patBet AS 'TotalOhnePatBet'\nfrom #rec rec\n inner join #tk tk on tk.uid_klient_pk=rec.rec_kli_id\n inner join tbl_klienten tkli on tkli.uid_klient_pk=rec.rec_kli_id\n inner join klient kli on kli.kli_id=rec.rec_kli_id\n inner join person_prs AS prs ON prs.prs_id = kli.kli_prs_id\n inner join #ler ler on ler.ler_kli_id=rec.rec_kli_id and convert(date, rec.rez_datum) = convert(date, ler.ler_tag) and rec.lep_id = ler.lep_id\n where rec.Kanton=@kanton\nORDER BY datepart(yyyy,rec.rez_datum), datepart(mm,rec.rez_datum), rec.zsrNr, \n tkli.str_klient_nachname,\n tkli.str_klient_vorname\n\n\ndrop table #tk\ndrop table #ler\ndrop table #rec\ndrop table #patbet\ndrop table #tmp_leistungstarife"]

{"sheet":"SQL_Backup","index":7,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":true,"merged":[]}
["create table #tmp_leistungstarife\n(\n id    uniqueidentifier,\n leistungstarif varchar(50) collate database_default,\n bezeichnung  varchar(50) collate database_default,\n tarifcode  varchar(50) collate database_default,\n kat_id   uniqueidentifier\n)\n\ndeclare @item          varchar(120)\ndeclare @pos          int\ndeclare @xcount    int\ndeclare @new_id    uniqueidentifier\ndeclare @kat_id    uniqueidentifier\n\nselect @xcount = 1\n\nif ltrim(rtrim(@leistungsparam1)) <> ''\nbegin\n while rtrim (ltrim (@leistungsparam1)) <> ''\n begin\n  select @pos = charindex (',', @leistungsparam1)\n  if @pos = 0\n  begin\n   select @item = @leistungsparam1\n   select @leistungsparam1 = ''\n  end\n  else\n  begin\n   select @item = substring (@leistungsparam1, 0, @pos)\n   select @leistungsparam1 = substring (@leistungsparam1, @pos +1, len(@leistungsparam1) - @pos)\n  end\n  if rtrim (ltrim (@item)) <> ''\n  begin\n   if @xcount = 1 \n   begin\n    select @new_id = NEWID()\n    insert into #tmp_leistungstarife (leistungstarif, id) values (LTRIM(rtrim(@item)), @new_id)\n    select @kat_id = kat_id from katalog_leistungstarif where kat_nummer = LTRIM(rtrim(@item))\n    update #tmp_leistungstarife set kat_id = @kat_id where id = @new_id\n   end\n   else\n   begin\n    if @xcount = 2\n     update #tmp_leistungstarife set bezeichnung = LTRIM(rtrim(@item)) where id = @new_id\n    if @xcount = 3\n     update #tmp_leistungstarife set tarifcode = LTRIM(rtrim(@item)) where id = @new_id\n   end\n   select @xcount = @xcount + 1\n   if @xcount = 4\n   select @xcount = 1\n\n   end  \n end \nend\n\n---\n\ndeclare @traegerGmdeZHbfsNr int\nset @traegerGmdeZHbfsNr=(select kat_bfs_nr from katalog_ort_bfs where kat_gemeindename = @gemeinde)\n\ndeclare @ltt_HW uniqueidentifier, @ltt_Pflege uniqueidentifier, @ltt_AUP uniqueidentifier\nset @ltt_HW  = 'BB4D78F3-5993-4F05-8F75-F5F76BAB61D6'\nset @ltt_Pflege = 'EE5778DE-FDDD-4720-A066-66E6258B8916'\nset @ltt_AUP = '535AD8CA-76D1-4B27-A852-106F16F98853'\n \ndeclare @ltt_Pflege1 uniqueidentifier\ndeclare @ltt_Pflege2 uniqueidentifier\ndeclare @ltt_Pflege3 uniqueidentifier\n\nselect @ltt_Pflege1 = '1CBC540C-5B2A-4532-9DCE-7414EBA0CE3C'\nselect @ltt_Pflege2 = '2A7C47DE-2A9C-4E99-B820-76ED792989EA'\nselect @ltt_Pflege3 = '5DFB9A04-E205-479D-84D5-2D12CFDDEB50'\n\n\n-- fakturierte Stunden ermitteln\nif object_id('tempdb.dbo.#rec') is not null drop table #rec\n\n\nif @ignoreNichtKLV <> 0\nbegin\n set @ltt_HW  = '00000000-0000-0000-0000-000000000000'\nend\n\n\nselect rec.rec_id, rec.rec_kli_id, convert(date,rez.rez_datum) as rez_datum, rez.rez_preis, csm.set_text zsrNr, ktzTxt.ktx_text, lep.lep_vollkosten, rez.rez_preis as lep_preis, lep.lep_id\n , \n max(rec.rec_krankenkasse_nr) as rec_krankenkasse_nr, max(rec.rec_krankenkasse) as rec_krankenkasse,\n (isnull(sum(rez_anzahl),0))  vStd\n , isnull(sum(rez.rez_patientenbeteiligung_total_betrag),0) patBet\n , isnull(sum(rez.rez_restfinanzierung_total_betrag),0) restFin\n , rez_leiart_kat_id\n  \ninto #rec\nfrom rechnung_rec rec\n inner join rechnung_zeilen_rez rez on rez.rez_rec_id=rec.rec_id and rez.rez_show = 1 and isnull(rez_use_only_patbet,0) = 0\n inner join c_settings_mand AS csm ON csm.set_oe_owner = rec.rec_oe_rechnung and csm.set_name='Konkordatsnummer'\n inner join katalog_leistungstarif AS klt ON klt.kat_id = rez.rez_leiart_kat_id and klt.kat_leistungstarif_typ IN (@ltt_Pflege, @ltt_AUP, @ltt_HW, @ltt_Pflege1, @ltt_Pflege2, @ltt_Pflege3) \n inner join leistung_preis_lep AS lep on rez.rez_leiart_kat_id = lep.lep_leistungstarif  \n                and convert(date,lep.lep_datum_von) <= convert(date, rez.rez_datum)\n                and (ISNULL(convert(date,lep.lep_datum_bis), convert(date,rez.rez_datum)) >= convert(date,rez.rez_datum)) \n                and dbo.checkOE(rez.rez_oe_erfassung, lep.lep_oe_visibility) = 1\n left join katalog_tarifziffer AS ktz on ktz.kat_id = lep.lep_tarifziffer\n left join katalog_texte AS ktzTxt on ktz.kat_name = ktzTxt.ktx_id and ktzTxt.sprachnbr = 2055\n  inner join katalog_ort_bfs kobfs \n    on Case \n     when @gemeinde = 'Alle' AND kobfs.kat_id=rec_restfinanzierung_traegergemeinde then 1\n     when kobfs.kat_id=rec_restfinanzierung_traegergemeinde AND kobfs.kat_bfs_nr=@traegerGmdeZHbfsNr then 1\n  else 0\n  End = 1\nwhere \n\n rec.rec_jahr * 100 + rec.rec_monat BETWEEN datepart(year, @von) * 100 + datepart(month, @von) \nAND datepart(year, @bis) * 100 + datepart(month, @bis)\n and rec.rec_storno_datum is null   \n --and klt.kat_nummer between '1300' and '1399'\n\n-- ---------------------------------------------------------------------------------------ab hier Kundenspezifisch für Kinderspitex\n-- and\n--(\n--        @useIV = 0 \n--             AND (klt.kat_nummer not between '1300' and '1399')\n--        ) \n--        OR \n--        (@useIV = 1 \n--             AND (klt.kat_nummer between '1300' and '1399')\n--      )\n-- ---------------------------------------------------------------------------------------bis hier Kundenspezifisch\n\n group by rec.rec_id, rec.rec_kli_id, convert(date,rez.rez_datum),  rez.rez_preis, csm.set_text, ktzTxt.ktx_text, lep.lep_vollkosten, lep.lep_id\n, rez.rez_leiart_kat_id\norder by convert(varchar(100),rec.rec_kli_id), convert(date,rez.rez_datum), ktzTxt.ktx_text\n\n\nupdate #rec set ktx_text = 'N-KLV' where ktx_text is null\nupdate #rec set ktx_text = UPPER(ktx_text)\nupdate #rec set ktx_text = 'N-KLV' where ktx_text not in ('N-KLV', 'KLV A', 'KLV B', 'KLV C', 'N-KLV', 'AÜP A', 'AÜP B', 'AÜP C', 'KLV A UV/MV', 'KLV B UV/MV', 'KLV C UV/MV')\nupdate #rec set ktx_text = 'N-KLV' where ktx_text not in ('N-KLV', 'KLV A', 'KLV B', 'KLV C', 'N-KLV', 'AÜP A', 'AÜP B', 'AÜP C', 'KLV A UV/MV', 'KLV B UV/MV', 'KLV C UV/MV')\n\n\n-- Einsatzgrund ermitteln\n\ndeclare @rec_id  uniqueidentifier\ndeclare @rez_datum datetime\ndeclare @einsatzgrund nvarchar(120)\ndeclare @rec_kli_id uniqueidentifier\ndeclare @rez_leiart_kat_id uniqueidentifier\ndeclare @tarifbez varchar(50)\n\ndeclare c0 cursor local forward_only for \nselect rec_id, rez_datum, rec_kli_id, t.tarifcode, t.kat_id\nfrom #rec \ninner join #tmp_leistungstarife t on t.kat_id = rez_leiart_kat_id\n\nopen c0\n\nfetch next from c0 into @rec_id, @rez_datum, @rec_kli_id, @tarifbez, @rez_leiart_kat_id\nwhile @@fetch_status = 0\nbegin\n\n\n\n update #rec set ktx_text = isnull(@tarifbez,'') where rez_datum = @rez_datum and rec_id = @rec_id and rec_kli_id = @rec_kli_id and rez_leiart_kat_id = @rez_leiart_kat_id\n\n fetch next from c0 into @rec_id, @rez_datum, @rec_kli_id, @tarifbez, @rez_leiart_kat_id\nend\n\nclose c0\ndeallocate c0\n\n\n-- Ende Einsatzgrund\n\n\n-- patientenbeteiligung ermitteln\nif object_id('tempdb.dbo.#patbet') is not null drop table #patbet\nselect rec.rec_kli_id, convert(date,rez.rez_datum) as rez_datum, rez.rez_preis, csm.set_text zsrNr, ktzTxt.ktx_text, lep.lep_vollkosten, rez.rez_preis as lep_preis, lep.lep_id\n , \n max(rec.rec_krankenkasse_nr) as rec_krankenkasse_nr, max(rec.rec_krankenkasse) as rec_krankenkasse,\n\n (isnull(sum(rez_anzahl),0))  vStd\n\n , isnull(sum(rez.rez_patientenbeteiligung_total_betrag),0) patBet\n , isnull(sum(rez.rez_restfinanzierung_total_betrag),0) restFin\ninto #patbet\nfrom rechnung_rec rec\n inner join rechnung_zeilen_rez rez on rez.rez_rec_id=rec.rec_id and rez.rez_show = 1\n inner join c_settings_mand AS csm ON csm.set_oe_owner = rec.rec_oe_rechnung and csm.set_name='Konkordatsnummer'\n inner join katalog_leistungstarif AS klt ON klt.kat_id = rez.rez_leiart_kat_id and klt.kat_leistungstarif_typ IN (@ltt_Pflege, @ltt_AUP, @ltt_HW) \n inner join leistung_preis_lep AS lep on rez.rez_leiart_kat_id = lep.lep_leistungstarif \n                and convert(date, lep.lep_datum_von) <= convert(date, rez.rez_datum) \n                and (ISNULL(convert(date,lep.lep_datum_bis), convert(date,rez.rez_datum)) >= convert(date,rez.rez_datum)) \n                and dbo.checkOE(rez.rez_oe_erfassung, lep.lep_oe_visibility) = 1\n left join katalog_tarifziffer AS ktz on ktz.kat_id = lep.lep_tarifziffer\n left join katalog_texte AS ktzTxt on ktz.kat_name = ktzTxt.ktx_id and ktzTxt.sprachnbr = 2055\n  inner join katalog_ort_bfs kobfs \n    on Case \n     when @gemeinde = 'Alle' AND kobfs.kat_id=rec_restfinanzierung_traegergemeinde then 1\n     when kobfs.kat_id=rec_restfinanzierung_traegergemeinde AND kobfs.kat_bfs_nr=@traegerGmdeZHbfsNr then 1\n  else 0\n  End = 1\nwhere rec.rec_jahr * 100 + rec.rec_monat BETWEEN datepart(year, @von) * 100 + datepart(month, @von) \nAND datepart(year, @bis) * 100 + datepart(month, @bis)\n and rec.rec_storno_datum is null \n \nand isnull(rez_use_only_patbet,0) = 1\n  \ngroup by rec.rec_kli_id, convert(date,rez.rez_datum),  rez.rez_preis, csm.set_text, ktzTxt.ktx_text, lep.lep_vollkosten, lep.lep_id\norder by convert(varchar(100),rec.rec_kli_id), convert(date,rez.rez_datum), ktzTxt.ktx_text\n\nupdate #rec set patBet = #patbet.patBet\nfrom #rec \ninner join #patbet on #patbet.rec_kli_id = #rec.rec_kli_id and #patbet.rez_datum = #rec.rez_datum and #patbet.lep_id = #rec.lep_id\n\n --geleistete Stunden ermitteln\nif object_id('tempdb.dbo.#ler') is not null drop table #ler\nselect ler.ler_kli_id, convert(date,ler_tag) as ler_tag, isnull(sum(ler_dauer),0) gStd, ktzTxt.ktx_text, lep.lep_vollkosten, lep.lep_preis, lep.lep_id\ninto #ler\nfrom leistungstarif_erfassung_ler ler\n inner join katalog_leistungstarif klt on klt.kat_id=ler.ler_leistungstarif\n inner join leistung_preis_lep AS lep on ler.ler_leistungstarif = lep.lep_leistungstarif \n                and convert(date, lep.lep_datum_von) <= convert(date, ler.ler_tag) \n                and (ISNULL(convert(date,lep.lep_datum_bis), convert(date,ler.ler_tag)) >= convert(date,ler.ler_tag) )\n                and dbo.checkOE(ler.ler_oe_erfassung, lep.lep_oe_visibility) = 1\n left join katalog_tarifziffer AS ktz on ktz.kat_id = lep.lep_tarifziffer\n left join katalog_texte AS ktzTxt on ktz.kat_name = ktzTxt.ktx_id and ktzTxt.sprachnbr = 2055\nwhere convert(date, ler_tag) between @von and @bis\n and ler_kli_id is NOT NULL\ngroup by ler.ler_kli_id, convert(date,ler_tag), ktzTxt.ktx_text, lep.lep_vollkosten, lep.lep_preis, lep.lep_id\norder by convert(varchar(100),ler.ler_kli_id), convert(date,ler_tag), ktzTxt.ktx_text\n\n-- Strasse und Nr aus Adresse extrahieren\nif object_id('tempdb.dbo.#tk') is not null drop table #tk\nselect distinct uid_klient_pk, str_klient_adresse1, convert(int,0) pos, convert(nvarchar(max),'') strasse, convert(nvarchar(20),'') nr\ninto #tk\nfrom tbl_klienten\nwhere uid_klient_pk in (select rec_kli_id from #rec)\n\nupdate #tk set pos = PATINDEX('%[0-9]%', str_klient_adresse1) -- alles bis zum Beginn der HausNr wird als Strasse angesehen\nupdate #tk set pos = len(str_klient_adresse1) where pos = 0  -- keine HausNr vorhanden also ist alles Strasse\nupdate #tk set strasse = ltrim(rtrim(left(str_klient_adresse1, pos-1))), nr=substring(str_klient_adresse1,pos,20) where pos > 0\n\nalter table #tk add constraint PKtk primary key (uid_klient_pk)\n\nupdate #rec set lep_vollkosten = lep_preis where lep_vollkosten = 0.00\n\n---- Statistik Stadt ZH\nselect kli.kli_traegergemeinde_bfs_nr AS 'BfS Gemeinde-Nr.'\n , rec.zsrNr 'ZSR-Nr. Leistungs-erbringer'\n , datepart(yyyy,rec.rez_datum) 'Abrechnungs-Jahr', datepart(mm,rec.rez_datum) 'Fakturierungs-Monat'\n , tkli.str_klient_nachname AS 'Nachname 1',\n '' AS 'Nachname 2',\n tkli.str_klient_vorname AS 'Vorname',\n CASE tkli.int_klient_geschlecht_fk WHEN 2 THEN 'w' WHEN 1 THEN 'm' ELSE '' END AS 'Geschlecht',\n convert(VARCHAR, tkli.dat_klient_geburtsdatum, 104) AS 'Geburtsdatum',\n isnull(prs.prs_nnss,'') AS 'Neue AHV-Nr.',\n ISNULL(prs.prs_ahv,'') AS 'Alte AHV-Nr.',\n tk.Strasse, tk.nr AS 'Nr.',\n isnull(tkli.str_klient_adresse2,'') AS 'Adresszusatz',\n tkli.str_klient_plz AS 'PLZ',\n tkli.str_klient_ort AS 'Ort',\n convert(VARCHAR, rec.rez_datum, 104) AS 'Leistungsdatum',\n rec.ktx_text AS 'Leistungsart',\n \n  CASE rec.ktx_text  WHEN 'N-KLV' THEN (rec.lep_preis) ELSE 0.00 END AS 'N-KLV-Tarif',\n  \n case When @useUnroundedHours = 1 THEN ler.gStd else null end AS 'Geleistete Stunden',\n rec.vStd AS 'Verrechnete Stunden',\n rec.patBet AS 'Pat.bet',(rec.lep_vollkosten - rec.lep_preis) AS 'Restfinanzierung',\n \n (rec.lep_vollkosten - rec.lep_preis) * rec.vStd AS 'TotalMitPatBet',\n ((rec.lep_vollkosten - rec.lep_preis) * rec.vStd) - rec.patBet AS 'TotalOhnePatBet'\n\nfrom #rec rec\n inner join #tk tk on tk.uid_klient_pk=rec.rec_kli_id\n inner join tbl_klienten tkli on tkli.uid_klient_pk=rec.rec_kli_id\n inner join klient kli on kli.kli_id=rec.rec_kli_id\n inner join person_prs AS prs ON prs.prs_id = kli.kli_prs_id\n inner join #ler ler on ler.ler_kli_id=rec.rec_kli_id and convert(date, rec.rez_datum) = convert(date, ler.ler_tag) and rec.lep_id = ler.lep_id\n\nORDER BY datepart(yyyy,rec.rez_datum), datepart(mm,rec.rez_datum), rec.zsrNr, \n tkli.str_klient_nachname,\n tkli.str_klient_vorname\n\n\ndrop table #tk\ndrop table #ler\ndrop table #rec\ndrop table #patbet\ndrop table #tmp_leistungstarife\n"]