/******************************************************************************************************************************* Diese Datei wurde automatisch erstellt. Bitte nicht verändern. Importieren Sie diese Datei mit der Funktion des Abfrageassistenten. Projekt: pm Abschnitt: Abfrageassistent, Abfrage Personalabgänge (für die Personalkommission II) Mandant: 20010 auf KS30\I2017.pm Version: 28.05.2019 11:57 ********************************************************************************************************************************/ insert into flex_abfragen ( abfrSQL, abfrOrdnung, abfrGruppe, abfrInhalt, abfrBeschreibung, abfrStandard, abfrThemen, abfrNutzer, abfrMandant, abfrAnwendung, abfrVorlage, abfrXLVorlage, abfrSQL_DW) values ( -305 , 0 , §Statistiken§ , §§ , §Personalabgänge (für die Personalkommission II)§ , §F§ , §§ , @abfrNutzer , @abfrMandant , 0 , null , null , §release 17; datawindow(units=0 timer_interval=0 color=1073741824 processing=1 print.margin.bottom=150 print.margin.left=300 print.margin.right=150 print.margin.top=150 print.preview.buttons=no ) table( column=(type=char(10) updatewhereclause = yes name=ds dbname="DS") column=(type=char(80) updatewhereclause = yes name=name dbname="Name") column=(type=datetime updatewhereclause = yes name=geboren dbname="Geboren") column=(type=datetime updatewhereclause = yes name=ausgeschiedenseit dbname="AusgeschiedenSeit") column=(type=long identity=yes updatewhereclause = yes name=jahr dbname="Jahr") column=(type=char(255) updatewhereclause = yes name=begründung dbname="Begründung") column=(type=char(13) updatewhereclause = yes name=altersgruppe dbname="Altersgruppe") column=(type=char(10) updatewhereclause = yes name=laufbahn dbname="Laufbahn") column=(type=char(1) updatewhereclause = yes name=status dbname="Status") column=(type=char(80) updatewhereclause = yes name=beschverh dbname="BeschVerh") column=(type=datetime updatewhereclause = yes name=regelruhestand dbname="Regelruhestand") column=(type=long updatewhereclause = yes name=p_pid dbname="P_PID") retrieve="Select DS as [DS],P_Name as [Name],P_Geboren as [Geboren],OrgZ_Von as [AusgeschiedenSeit],datepart(year, OrgZ_Von) as [Jahr],OrgZ_Begründung as [Begründung],' > ' + ltrim(str( (dbo.f_alter(dateadd(day, 1, P_Geboren), OrgZ_Von) / 5) * 5 )) as [Altersgruppe],P_Lbg as [Laufbahn],case when dbo.f_roMitBeamtenstatus(OrgZ_Von, t_roID) = 'T' then 'B' else 'A' end as [Status],dbo.f_roBV(t_roID, OrgZ_Von) as [BeschVerh],dbo.f_pRegelruhestandAb(t_pID, null, 'F') as [Regelruhestand],P_PID From #aa order by DS ASC, P_Name ASC") header(height=120) footer(height=80) detail(height=80 height.autosize=yes color="536870912 ") column(band=detail id=1 x="9" y="8" height="64" width="320" name=ds height.autosize=yes visible="1" format="[general]" edit.limit=10 alignment="0" font.face="Tahoma" font.height="-8" font.weight="400" font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0" edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no ) text(band=header text="DS" x="9" y="0" height="52" width="320" font.face="Tahoma" font.height="-8" font.weight="700" height.autosize=yes font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="1" tooltip.isbubble="0" alignment="0" name=ds_t ) column(band=detail id=2 x="338" y="8" height="64" width="530" name=name height.autosize=yes visible="1" format="[general]" edit.limit=80 alignment="0" font.face="Tahoma" font.height="-8" font.weight="400" font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0" edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no ) text(band=header text="Name" x="338" y="0" height="52" width="530" font.face="Tahoma" font.height="-8" font.weight="700" height.autosize=yes font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="1" tooltip.isbubble="0" alignment="0" name=name_t ) column(band=detail id=3 x="878" y="8" height="64" width="603" name=geboren height.autosize=yes visible="1" format="[general]" alignment="0" font.face="Tahoma" font.height="-8" font.weight="400" font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0" edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no ) text(band=header text="Geboren" x="878" y="0" height="52" width="603" font.face="Tahoma" font.height="-8" font.weight="700" height.autosize=yes font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="1" tooltip.isbubble="0" alignment="0" name=geboren_t ) column(band=detail id=4 x="1490" y="8" height="64" width="538" name=ausgeschiedenseit height.autosize=yes visible="1" format="[general]" alignment="0" font.face="Tahoma" font.height="-8" font.weight="400" font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0" edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no ) text(band=header text="AusgeschiedenSeit" x="1490" y="0" height="52" width="539" font.face="Tahoma" font.height="-8" font.weight="700" height.autosize=yes font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="1" tooltip.isbubble="0" alignment="0" name=ausgeschiedenseit_t ) column(band=detail id=5 x="2039" y="8" height="64" width="202" name=jahr height.autosize=yes visible="1" format="[general]" alignment="0" font.face="Tahoma" font.height="-8" font.weight="400" font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0" edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no ) text(band=header text="Jahr" x="2039" y="0" height="52" width="201" font.face="Tahoma" font.height="-8" font.weight="700" height.autosize=yes font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="1" tooltip.isbubble="0" alignment="0" name=jahr_t ) column(band=detail id=6 x="2249" y="8" height="64" width="763" name=begründung height.autosize=yes visible="1" format="[general]" edit.limit=255 alignment="0" font.face="Tahoma" font.height="-8" font.weight="400" font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0" edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no ) text(band=header text="Begründung" x="2249" y="0" height="52" width="763" font.face="Tahoma" font.height="-8" font.weight="700" height.autosize=yes font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="1" tooltip.isbubble="0" alignment="0" name=begründung_t ) column(band=detail id=7 x="3022" y="8" height="64" width="389" name=altersgruppe height.autosize=yes visible="1" format="[general]" edit.limit=13 alignment="0" font.face="Tahoma" font.height="-8" font.weight="400" font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0" edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no ) text(band=header text="Altersgruppe" x="3022" y="0" height="52" width="389" font.face="Tahoma" font.height="-8" font.weight="700" height.autosize=yes font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="1" tooltip.isbubble="0" alignment="0" name=altersgruppe_t ) column(band=detail id=8 x="3419" y="8" height="64" width="320" name=laufbahn height.autosize=yes visible="1" format="[general]" edit.limit=10 alignment="0" font.face="Tahoma" font.height="-8" font.weight="400" font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0" edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no ) text(band=header text="Laufbahn" x="3419" y="0" height="52" width="320" font.face="Tahoma" font.height="-8" font.weight="700" height.autosize=yes font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="1" tooltip.isbubble="0" alignment="0" name=laufbahn_t ) column(band=detail id=9 x="3749" y="8" height="64" width="188" name=status height.autosize=yes visible="1" format="[general]" edit.limit=1 alignment="0" font.face="Tahoma" font.height="-8" font.weight="400" font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0" edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no ) text(band=header text="Status" x="3749" y="0" height="52" width="187" font.face="Tahoma" font.height="-8" font.weight="700" height.autosize=yes font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="1" tooltip.isbubble="0" alignment="0" name=status_t ) column(band=detail id=10 x="3945" y="8" height="64" width="379" name=beschverh height.autosize=yes visible="1" format="[general]" edit.limit=80 alignment="0" font.face="Tahoma" font.height="-8" font.weight="400" font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0" edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no ) text(band=header text="BeschVerh" x="3945" y="0" height="52" width="379" font.face="Tahoma" font.height="-8" font.weight="700" height.autosize=yes font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="1" tooltip.isbubble="0" alignment="0" name=beschverh_t ) column(band=detail id=11 x="4334" y="8" height="64" width="461" name=regelruhestand height.autosize=yes visible="1" format="[general]" alignment="0" font.face="Tahoma" font.height="-8" font.weight="400" font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0" edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no ) text(band=header text="Regelruhestand" x="4334" y="0" height="52" width="462" font.face="Tahoma" font.height="-8" font.weight="700" height.autosize=yes font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="1" tooltip.isbubble="0" alignment="0" name=regelruhestand_t ) column(band=detail id=12 x="4805" y="8" height="64" width="329" name=p_pid height.autosize=yes visible="1" format="[general]" alignment="0" font.face="Tahoma" font.height="-8" font.weight="400" font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0" edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no ) text(band=header text="P_PID" x="4805" y="0" height="52" width="329" font.face="Tahoma" font.height="-8" font.weight="700" height.autosize=yes font.charset="0" font.pitch="2" font.family="2" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="1" tooltip.isbubble="0" alignment="0" name=p_pid_t ) htmltable(border="0" cellpadding="1" cellspacing="1" generatecss="no" nowrap="no") sparse(names="")§ ) insert into flex_where_klausel (whAbfragen, whKlammerAuf, whColumn, whOperator, whParameter, whKlammerZu, whLogical, whOrdnung, whMandant) values ( -1 , §§ , §P_IstAktiv§ , §=§ , §'nein'§ , §§ , §AND§ , §1§ , @abfrMandant ) insert into flex_where_klausel (whAbfragen, whKlammerAuf, whColumn, whOperator, whParameter, whKlammerZu, whLogical, whOrdnung, whMandant) values ( -1 , §§ , §OrgZ_Von§ , §BETWEEN§ , §t_von and t_bis§ , §§ , §AND§ , §2§ , @abfrMandant ) insert into flex_where_klausel (whAbfragen, whKlammerAuf, whColumn, whOperator, whParameter, whKlammerZu, whLogical, whOrdnung, whMandant) values ( -1 , §§ , §[dbo].[f_roBefristungAm] (t_roID, dateadd(day, -1, OrgZ_Von)) § , §NOT LIKE§ , §'befristet%'§ , §§ , §§ , §3§ , @abfrMandant ) insert into flex_having_klausel (havAbfragen, havKlammerAuf, havAggregat, havColumn, havOperator, havParameter, havKlammerZu, havLogical, havMandant) values ( -1 , §§ , §§ , §§ , §§ , §§ , §§ , §§ , @abfrMandant )