SQL-Expressions für SQL-Ordner

Sammlung von SQL-Expressions für SQL-Ordner

Standard

|

Expert

CLOUD ABO

|

ON-PREMISES

Leistung & CRM

Budget & Teilprojekt

Fremdkosten

Ressourcenplanung

Business Intelligence

Erstellt: 11.07.2006
Aktualisiert: 15.05.2023 | Dokumentation, Struktur und Screenshots aktualisiert.

Übersicht

Vergleiche

Sortierung

|| Operator / + Operator

Cast-Operator

Abfragen

Abfragebeispiele

Vergleiche

Die Vergleiche in SQL erfolgen auf drei Arten

  • Exakter Vergleich: ='\1'. Der gesamte eingegebene Wert muss mit dem gesuchten Wert übereinstimmen. Wird vor allem für Zahlen verwendet.
  • Zeichenvergleich, Anfang: like '\1%'. Der eingegebene Wert entspricht dem Anfang des gesuchten Wertes.
  • Zeichenvergleich, Teil: like '%\1%'. Der eingegebene Wert entspricht einem Teil des gesuchten Wertes.

Die Ziffer 1 ist der Platzhalter für die Abfrage, die vom Benutzer mittels Dialog abgefragt wird. Sind mehrere Suchfelder definiert, werden die Ziffern in der Reihenfolge 1,2,3...9 (es können maximal 9 Felder definiert werden) verwendet in der entsprechenden Reihenfolge der Suchfelder. Wenn Sie ohne Abfragedialog arbeiten, verwenden Sie statt des Platzhalters einfach Ihr Kriterium, das Sie abfragen wollen.

Sortierung

Die Sortierung im SQL-Ordner erfolgt über die Listeneinstellungen. In der SQL-Expression sollte keine Sortierung verwendet werden, da die Daten bei der Anzeige durch die Listeneinstellungen wieder umsortiert werden.

|| Operator (Firebird)

Der || Operator wird im Firebird verwendet, um Textelemente zusammenzuhängen. Jeweils das Element vor und nach dem || werden zusammengehängt.

extract(month from datum) || '.' || extract(year from datum)='\1'

Im obigen Beispiel wird zuerst der extrahierte Monat (z.B. 08) genommen, mit einem Punkt zusammengehängt und dann das extrahierte Jahr (z.B. 2016) ebenfalls angehängt. Das ergibt dann einen String in der Form Monat.Jahr (z.B. 08.2016).

+ Operator (MS SQL)

Der + Operator wird auf MS SQL Server verwendet, um Textelemente zusammenzuhängen. Jeweils das Element vor und nach dem + werden zusammengehängt.

(cast(datepart(month,datum) as varchar)+'.'+cast(datepart(year,datum) as varchar)= '\1')

Im obigen Beispiel wird zuerst der extrahierte Monat (z.B. 08) genommen, mit einem Punkt zusammengehängt und dann das extrahierte Jahr (z.B. 2016) ebenfalls angehängt. Das ergibt dann einen String in der Form Monat.Jahr (z.B. 08.2016).

Cast-Operator

Der Cast-Operator wird verwendet, um einen Wert in einen bestimmten Datentyp umzuwandeln. Das wird vor allem dann verwendet, wenn mit dem Resultat weitergearbeitet werden muss, z.B. mit Zahlen gerechnet oder zu einem Datum einen Wert dazugezählt. Die Syntax lautet wie folgt:

CAST (value AS datatype)

Geläufige Datentypen sind:

  • timestamp (oder date)
  • varchar
  • integer
  • numeric

String in einen Integer umwandeln

Der cast-Operator kann dazu verwendet werden, einen String in einen numerischen Wert umzuwandeln, z.B. eine PLZ auf einer Adresse:

cast(standardplz as integer)

Das geht aber nur, wenn der Inhalt wirklich nur nummerische Werte enthält, sonst gibt's eine Fehlermeldung. Deshalb sollte man gleichzeitig auf numerischen Inhalt prüfen. Das ist im Moment nur mit MS SQL Server möglich, für Firebird existiert leider keine isnumeric Funktion. Beispiel MS SQL Server:

(isnumeric(standardplz)=1 and cast(standardplz as integer) >= 8000)

Abfragen

Abfrage des eingeloggten Benutzers

Mit dem String \login_id kann innerhalb eines SQL-Ordners auf die ID des Logins zugegriffen werden. So kann man zum Beispiel einen SQL-Ordner machen, mit dem der Benutzer in seinen betreuten Adressen suchen kann:

  • Klasse: Adresseintrag
  • SQL: (Name like '%\1%' OR Alias like '%\1%') AND betreuer=\login_id

Abfrage von normalen Feldern

Wenn normale Felder abgefragt werden, geht das am Einfachsten über den Button Feld einfügen... im SQL-Dialog. Normale Felder werden mit einem der obengenannten Vergleiche abgefragt. Beispiel:

Betreffend like '%\1%'

Abfrage von Membern

SQL-Ordner, der beispielsweise alle offenen Leistungen eines Mitarbeiters anzeigt. Nach diesem Mitarbeiter soll gesucht werden.

  • Klasse: OffeneLeistung
  • Abfrage-Bezeichnung (frei): Bearbeiter
  • SQL-Expression: Bearbeiter in (SELECT Bold_ID from Projektbearbeiter WHERE Name like '%\1%')

Abfrage von Blob-Feldern

BLOB's werden für Felder verwendet, die langen Text enthalten, wie bspw. der Text von Aktivitäten.

  • Klasse: Aktivitaet
  • Abfrage-Bezeichnung (frei): Text
  • SQL-Expression Firebird: text containing '\1'
    (Das Statement muss von Hand eingefügt werden, da der Auswahldialog das 'containing' nicht standardmässig anbietet.)
  • SQL-Expression MS SQL Server: text like '%\1%'
    (Mit MS SQL Server werden nicht BLOB, sondern VARCHAR(MAX) Felder verwendet.)

Diese Suche ist Case-Insensitive, das heisst, es findet Gross-, Kleinbuchstaben und beliebige Kombinationen von beiden.

Abfrage von Datumsfeldern

Normale Datumsfelder, wie sie zum Beispiel auf der Rechnung, bei Leistungen etc. vorkommen, können wie folgt abgefragt werden:

  • Datum='\1'
  • Datum<'\1'
  • Datum>'\1'

Heutiges Datum

Firebird MS SQL

Abfrage des heutigen Datums via cast('TODAY' as timestamp), z.B. welche Einträge wurden seit X Tagen erfasst:

CreationDateTime>=cast('TODAY' as timestamp)-\1

Abfrage des heutigen Datums via GETDATE(), z.B. welche Einträge wurden seit X Tagen erfasst:

CreationDateTime>=GETDATE()-\1

Extrahieren von Tag, Monat, Jahr

Firebird MS SQL

Syntax: extract(element from feld)

  • element: Die wichtigsten, vordefinierten Elemente sind:
    • YEAR
    • MONTH
    • DAY
  • feld: Datumsfeld in der Datenbank, aus welchem das entsprechende Element extrahiert werden soll.

Als Beispiel wird year aus dem Feld datum extrahiert und mit dem eingegebenen Jahr verglichen (= '1'):

extract(year from datum)=\1

Syntax: datepart(element, feld)

  • element: Die wichtigsten, vordefinierten Elemente sind:
    • YEAR
    • MONTH
    • DAY
  • feld: Datumsfeld in der Datenbank, aus welchem das entsprechende Element extrahiert werden soll.

Als Beispiel wird year aus dem Feld datum extrahiert und mit dem eingegebenen Jahr verglichen (= '1'):

cast(datepart(year,datum) as varchar) = '\1'

Einschränkung: Bei einer Suche nach Objekten eines Monats, z.B. Rechnungen vom Juli 17, muss die Eingabe 7.2017 sein und darf NICHT 07.2017 sein.

Datumsdifferenz

Firebird MS SQL

Keine spezielle Funktion für eine Datumsdifferenz.

Beispiel: Leistungen seit X Tagen bis heute; X kann vom User eingegeben werden:

datum>=cast('TODAY' as date)-\1

DATEDIFF(datepart, startdate, enddate)

Beispiel: Leistungen seit X Tagen bis heute; X kann vom User eingegeben werden:

(DATEDIFF(day, datum, getdate()) < \1) and (datum <= getdate())

Datumsfelder mit Zeitteil (CreationDateTime, ModifiedDateTime)

Daneben gibt es in Vertec spezielle Datumsfelder, welche zusätzlich noch einen Zeitteil gespeichert haben. Das sind zum Beispiel die CreationDateTime oder ModifiedDateTime, die auf jedem Eintrag gespeichert werden.

Einen genauen Datumsvergleich kann man hier nicht machen, weil ein Datum mit Zeitteil stets grösser ist als dasselbe Datum ohne Zeitteil. Dies kann man wie folgt umgehen: Man zählt beim eingegebenen Bis-Datum einen Tag dazu und macht einen < Vergleich. Beim Von-Datum muss man nichts dazuzählen. So erhält man als Resultat einen einzelnen Tag (wenn man wissen möchte, welche Einträge an welchem Tag erzeugt oder verändert wurden). Das Datum wird vom Benutzer abgefragt.

Firebird MS SQL

Einzelner Tag:

(CreationDateTime >= '\1') AND (CreationDateTime < CAST('\1' 
AS TIMESTAMP) + 1)

Datumsintervall:

('\1' = '' OR CreationDateTime >= '\1') AND ('\2' = '' OR 
(CreationDateTime < CAST('\2' AS TIMESTAMP) + 1))

Beispiel der Abfrage, welche Einträge nicht an einem bestimmten Datum erfasst worden sind (bspw. nützlich bei Migrationen, wenn die nicht migrierten Einträge angezeigt werden sollen):

CreationDateTime < '\1' OR CreationDateTime >= '\1'+1

Einzelner Tag:

creationdatetime>='\1' and creationdatetime<cast('\1' as 
datetime)+1

Datumsintervall:

creationdatetime>='\1' and creationdatetime<cast
('\2' as datetime)+1

Beispiel der Abfrage, welche Einträge nicht an einem bestimmten Datum erfasst worden sind (bspw. nützlich bei Migrationen, wenn die nicht migrierten Einträge angezeigt werden sollen):

creationdatetime<'\1' or creationdatetime>=cast('\1' as datetime)+1

Abfrage von Zusatzfeldern

Die Abfrage von Zusatzfeldern via SQL-Ordner erfolgt je nach Zusatzfeldtyp nach folgendem Schema:

  • SQL-Expression: bold_id IN (SELECT usereintrag FROM zusatzfeld WHERE metazusatzfeld=[Id des Zusatzfeldes] AND <Ausdruck><=Vergleich>)

Diese SQL-Expression kann für alle Zusatzfelder gleich verwendet werden. Die Teile in <eckigen Klammern> müssen jeweils angepasst werden:

  • <Id des Zusatzfeldes>: Dabei handelt es sich um die interne Id des Zusatzfeldes:
  • <Ausdruck>: Je nach Feldtyp muss die Abfrage anders formuliert werden. Eine vollständige Auflistung der Abfragen finden Sie hier im Artikel zu den Zusatzfeldern anschliessend.
  • <Vergleich>: Die Vergleiche können auf drei Arten erfolgen: =\1, like '\1%' und like '%\1%'. Der Platzhalter 1 repräsentiert jeweils den vom Benutzer im Suchdialog eingegeben Wert. Wenn Sie einen SQL-Ordner ohne Suchdialog anzeigen, müssen Sie hier einen konkreten Wert eingeben. Wann Sie welche Art von Vergleich verwenden müssen, finden Sie weiter oben in diesem Artikel im Abschnitt Vergleiche.

Abfrage von Stichwörtern

Stichwörter sind Ordner, deren Überordner als Stichwort Ordner bezeichnet ist. Die Abfrage sucht in diesen Unterordnern nach der Bezeichnung. Für die Abfrage muss die interne Id des Stichwort Ordners, also des Überordners angegeben werden, in folgendem Beispiel Projekte nach Filialen:

Die Vergleiche können auf drei Arten erfolgen: =\1like '\1%' und like '%\1%'. Der Platzhalter 1 repräsentiert jeweils den vom Benutzer im Suchdialog eingegeben Wert. Bei einem SQL-Ordner ohne Suchdialog muss hier ein konkreter Wert eingegeben werden. Wann welche Art von Vergleich verwendet werden muss, siehe weiter oben den Abschnitt Vergleiche.

Im Beispiel befindet sich der Stichwort-Suchordner im Rootordner Öffentliche Ordner:

  • Klasse: Projekt
  • Feldbezeichnung: Filiale
  • Feldtyp: Zeichen
  • SQL-Expression: bold_id IN (select ordeintraege from ordnerlink where ordner in (select bold_id from abstractordner where parentordner = 14729 AND bezeichnung like '%\1%'))

Abfrage von Tags

Es können auch Tags in SQL-Ordnern abgefragt werden. Die Abfrage erfolgt dabei gleich wie bei den anderen Feldern mit einem der oben genannten Vergleiche:

tags like '%\1%'

Abfrage von Custom-Links

Als Beispiel wird der Custom-Link-Typ Verwaltungsräte-VRMandate (Person-Firma) verwendet. Es wird ein SQL-Ordner erstellt, bei dem VRMandate nach Verwaltungsräten gesucht werden können:

  • Klasse: Firma
  • Abfrage-Bezeichnung (Bsp): Verwaltungsrat
  • SQL-Expression:

    BOLD_ID in (SELECT CustomLinks FROM CustomLink WHERE FromCustomLinks in (SELECT BOLD_ID FROM Adresseintrag WHERE Name like '\1%') AND Linktyp = 28987)

  • Wenn der Linktyp VRMandate-Verwaltungsräte heisst, die Link-Rollen also umgekehrt definiert sind, lautet die Expression stattdessen:

    BOLD_ID in (SELECT FromCustomLinks FROM CustomLink WHERE CustomLinks in (SELECT BOLD_ID FROM Adresseintrag WHERE Name like '\1%') AND Linktyp = 28987)

Diese Abfrage liefert alle Firmen zurück, mit denen die abgefragte Person verbunden ist. Die Bold_ID entspricht der internen Id, die im Eigenschaften-Dialog zu finden ist.

Custom-Links von normalen Klassen zu Zusatzklassen

Die Abfrage von Custom-Links von normalen Klassen zu Zusatzklassen und umgekehrt erfolgt genau gleich. Etwas speziell ist die zusätzliche Abfrage von Zusatzfeldern auf solchen Zusatzklassen.

Als Beispiel wird die Zusatzklasse12 verwendet, mit welcher über einen Custom-Link die installierte Version beim Kunden (Adresseintrag) hinterlegt wird. Der Custom-Link sieht wie folgt aus:

Um alle Kundenadressen anzuzeigen, die eine Version haben, lautet die Abfrage:

BOLD_ID in (SELECT CustomLinks FROM CustomLink WHERE Linktyp = 58973)

Um alle Objekte der Zusatzklasse12 anzeigen, denen ein Kunde zugeordnet ist, lautet die Abfrage:

BOLD_ID in (SELECT FromCustomLinks FROM CustomLink WHERE Linktyp = 58973)

Als BOLD_ID muss die interne Id des Linktyps angegeben werden.

Zusätzliche Abfrage von Zusatzfeldern auf der Zusatzklasse

Grundsätzlich werden Zusatzfelder auf Zusatzklassen gleich abgefragt wie Zusatzfelder auf normalen Klassen - als Klasse wird dafür einfach die entsprechende Zusatzklasse (z.B. Zusatzklasse12) angegeben.

Um obiges Beispiel weiterzuführen, wird folglich ein Beipsiel beschrieben, mit dem alle Adressen gefunden werden können, die eine bestimmte Version installiert haben. Dafür wird zusätzlich im Zusatzfeld Version auf der Zusatzklasse gesucht:

BOLD_ID in (SELECT CustomLinks FROM CustomLink WHERE ((Linktyp = 58973) and (FromCustomLinks in 
(SELECT usereintrag FROM zusatzfeld WHERE metazusatzfeld=58974 AND wertinteger=124))))

Wenn das Zusatzfeld definiert ist, kann es wie die normalen Zusatzfelder (siehe Abschnitt Abfrage von Zusatzfeldern) abgefragt werden, hier zum Beispiel mit wertinteger (SQL Ausdruck je nach Zusatzfeldtyp), weil es ein Ganzzahl-Feld ist.

Obiges Beispiel gibt alle Adressen zurück, die die Version 124 installiert haben. Die BOLD_ID des Metazusatzfeldes entspricht der internen Id des Zusatzfeldes Version:

Umgebungsbeschreibung

Zusatzklasse: Zusatzklasse12

  • Name: 'Installierte Versionen'

Linktyp: Zusatzklasse12 - Adresseintrag

  • Von:
    • Bezeichnung: Installierte Versionen
    • Klasse: Zusatzklasse12
  • Nach:
    • Bezeichnung: Kundenadresse
    • Klasse: Adresseintrag

Zusatzfeld auf der Zusatzklasse

  • Name: Version
  • Feldtyp: Ganzzahl
  • Klassen: Zusatzklasse12

Abfragebeispiele

Adressen

Gegenparteien

Im Suchfeld wird der Name der Firma abgefragt, für welche die Gegenparteien aufgelistet werden sollen.

  • Suchdialog anzeigen: ja
  • Klasse: Adresseintrag
  • SQL-Expression:
    bold_id in (select gegenprojekte from Projektgegenparteilink where gegenparteien in (select bold_id from adresseintrag adr where adr.name like '%\1%'))

Rechnungen

Rechnungen des aktuellen Monats

  • Suchdialog anzeigen: nein
  • Klasse: Rechnung
  • SQL-Expression Firebird:
    (extract (month from datum) = extract (month from cast('TODAY' as date))) and (extract (year from datum) = extract (year from cast('TODAY' as date)))
    SQL-Expression MS SQL Server:
    (cast(datepart(month, datum) as integer) = cast(datepart(month, GETDATE()) as integer)-1) AND (cast(datepart(year, datum) as integer) = cast(datepart(year, GETDATE()) as integer))

Rechnungen des letzten Monats

  • Suchdialog anzeigen: nein
  • Klasse: Rechnung
  • SQL-Expression Firebird:
    (extract (month from datum) = extract (month from cast('TODAY' as date))-1) and (extract (year from datum) = extract (year from cast('TODAY' as date)))
  • SQL-Expression MS SQL Server:
    (cast(datepart(month, datum) as integer) = cast(datepart(month, GETDATE()) as integer)-1) AND (cast(datepart(year, datum) as integer) = cast(datepart(year, GETDATE()) as integer))

Rechnungen nach Valutadatum

Wenn die Systemeinstellung Bis Datum für Rechnung Valutadatum berücksichtigen = True:

  • Suchdialog anzeigen: ja
  • Klasse: Rechnung
  • SQL-Expression: (case when xvalutadatum is null THEN (case when bis is null THEN datum else bis END) ELSE xvalutadatum END) <='\1'

Wenn die Systemeinstellung Bis Datum für Rechnung Valutadatum berücksichtigen = False:

  • Suchdialog anzeigen: ja
  • Klasse: Rechnung
  • SQL-Expression: (case when xvalutadatum is null THEN datum else xvalutadatum END) <='\1'

Unbezahlte Rechnungen seit...

  • Suchdialog anzeigen: ja
  • Klasse: Rechnung
  • SQL-Expression Firebird: (bezahlt = 0) and (datum<(cast('TODAY' as date)-'\1'))
  • SQL-Expression MS SQL Server: (bezahlt = 0) and (datum<(getdate()-'\1'))

Rechnungen nach Monat.Jahr

  • Suchdialog anzeigen: ja
  • Klasse: Rechnung
  • SQL-Expression Firebir: extract(month from datum) || '.' || extract(year from datum)=\1
  • SQL-Expression MS SQL Server: cast(datepart(month, datum) as varchar) + '.' + cast(datepart(year,datum) as varchar) = '\1'

Rechnungen nach Projekt

Die Rechnung muss zu einem Projekt mit bestimmtem Codemuster gehören. Der Code wird abgefragt.

  • Suchdialog anzeigen: ja
  • Klasse: Rechnung
  • SQL-Expression: projekt in (select bold_id from projekt where code like '%\1%')

Rechnungen von Projekten eines bestimmten Typs, unter Ausschluss eines bestimmten Codes

Im ersten Teil wird vom Projekt auf den Projekttyp zugegriffen und im zweiten Teil ist wird ein bestimmter Projektcode ausgeschlossen. Da bei diesen Abfragen das SQL not like nicht funktioniert, wird mit einer Unterabfrage der entsprechende Teil ausgeschlossen.

  • Suchdialog anzeigen: nein
  • Klasse: Rechnung
  • SQL-Expression: projekt in (Select bold_id from projekt where projekt.typ = 18710) AND projekt not in (select bold_id from projekt where code like 'PD%')

wobei die Zahl 18710 im Beispiel der internen Id des entsprechenden Projekttyps entspricht. PD% bedeutet, dass alle Projekte mit einem Code beginnend mit PD ausgeschlossen werden.

Rechnungen mit Abfrage Projektleiter

Es sollen alle Rechnungen eines Projektleiters angezeigt werden. Im Beispiel abgefragt wird das Kürzel des Projektleiters.

  • Suchdialog anzeigen: ja
  • Klasse: Rechnung
  • SQL-Expression: projekt in (SELECT bold_id from Projekt WHERE Projektleiter in (select bold_id from Projektbearbeiter where kuerzel like '%\1%'))
  • SQL-Expression wenn nur die offenen Rechnungen eines Projektleiters angezeigt werden sollen: projekt in (SELECT bold_id from Projekt WHERE Projektleiter in (select bold_id from Projektbearbeiter where kuerzel like '%\1%')) and verrechnet=0

Leistungen

Leistungen nach Wochentagen

Dieser SQL-Ordner zeigt als Resultat eine Liste aller (offenen und verrechneten) Leistungen, welche an einem bestimmten Wochentag erbracht worden sind.

  • Suchdialog anzeigen: nein
  • Klasse: Leistung
  • SQL-Expression Firebird: ((extract (weekday from datum))=6) or((extract (weekday from datum))=0)
    Diese Expression gibt als Beispiel Leistungen zurück, die entweder an einem Samstag oder Sonntag erbracht worden sind.
  • SQL-Expression MS SQL Server: (datepart(weekday,datum)=6)or((datepart(weekday,datum)=7))
    Diese Expression gibt als Beispiel Leistungen zurück, die entweder an einem Samstag oder Sonntag erbracht worden sind.

Die Wochentage in der Abfrage sind folgenden Zahlen zugeordnet:

  • 0 = Sonntag
  • 1 = Montag
  • 2 = Dienstag
  • 3 = Mittwoch
  • 4 = Donnerstag
  • 5 = Freitag
  • 6 = Samstag
  • 7 = Sonntag

Leistungen einer bestimmten Zeitperiode

Dieser SQL-Ordner zeigt als Resultat eine Liste aller Leistungen, welche in einer bestimmten Zeitperiode erbracht worden sind.

  • Suchdialog anzeigen: ja
  • Klasse: Leistung

Leistungen eines bestimmten Tages

  • SQL-Expression: datum = '\1'
  • Eingabe 'dd.mm.yyyy', z.B. '01.01.2014'

Leistungen eines Monats

  • SQL-Expression Firebird: extract(year from datum) = \1
  • SQL-Expression MS SQL Server: cast(datepart(year , datum) as varchar) = '\1'
  • Eingabe 'mm.yyyy', z.B. '01.2014'

Leistungen eines Jahres

  • SQL-Expression Firebird: extract(year from datum) = \1
  • SQL-Expression MS SQL Server: cast(datepart(year , datum) as varchar) = '\1'
  • Eingabe 'yyyy', z.B. '2014'

Leistungen einer bestimmten Tätigkeit

Dieser SQL-Ordner zeigt als Resultat eine Liste aller (offenen und verrechneten) Leistungen, welche einer bestimmten Tätigkeit zugewiesen sind. Im Abfragedialog kann die Tätigkeit angegeben werden.

  • Suchdialog anzeigen: ja
  • Klasse: Leistung
  • SQL-Expression: typ in (select bold_id from projekteintragstyp where (bold_type=22) and (code like '\1%'))

Erfasste Leistungen an Tagen mit Abwesenheiten

Dieser SQL-Ordner zeigt als Resultat eine Liste von offenen und verrechneten Leistungen, welche an Tagen mit Abwesenheiten erfasst wurden.

  • Suchdialog anzeigen: ja
  • Klasse: Leistung
  • Suchfelder: Von (Datum), Bis (Datum)

Suche in ganztägigen Abwesenheiten:

bold_id in (select bold_id from offeneleistung ol where
 datum >= '\1' and datum <= '\2' and
 (
 (select count(*) from abwesenheit abw1 where minutenabwesend is null and ol.bearbeiter = abw1.bearbeiter and ol.datum >= abw1.datum and ol.datum <= abw1.bisdatum) > 0
 or
 (select count(*) from abwesenheit abw3 where
 abw3.minutenabwesend is null and ol.datum >= abw3.datum and ol.datum <= abw3.bisdatum and
 abw3.bearbeiter in (select gruppen from benutzergruppenlink where benutzer = ol.bearbeiter)
 ) > 0
 )
)
or 
bold_id in (select bold_id from verrechneteleistung vl where
 datum >= '\1' and datum <= '\2' and
 (
 (select count(*) from abwesenheit abw2 where minutenabwesend is null and vl.bearbeiter = abw2.bearbeiter and vl.datum >= abw2.datum and vl.datum <= abw2.bisdatum) > 0
 or
 (select count(*) from abwesenheit abw4 where
 abw4.minutenabwesend is null and vl.datum >= abw4.datum and vl.datum <= abw4.bisdatum and
 abw4.bearbeiter in (select gruppen from benutzergruppenlink where benutzer = vl.bearbeiter)
 ) > 0
 )
)

Wenn zusätzlich auch in stundenweisen Abwesenheiten gesucht werden soll:

  • Erfordert ein zusätzliches Suchfeld: Auch in stundenweisen Abwesenheiten suchen? (Boolean)
bold_id in (select bold_id from offeneleistung ol where
 datum >= '\1' and datum <= '\2' and
 (
 (select count(*) from abwesenheit abw1 where (minutenabwesend is null or minutenabwesend < (CASE WHEN \3=1 THEN 9999 ELSE 0 END)) and ol.bearbeiter = abw1.bearbeiter and ol.datum >= abw1.datum and ol.datum <= abw1.bisdatum) > 0
 or
 (select count(*) from abwesenheit abw3 where
 (abw3.minutenabwesend is null or abw3.minutenabwesend < (CASE WHEN \3=1 THEN 9999 ELSE 0 END)) and ol.datum >= abw3.datum and ol.datum <= abw3.bisdatum and
 abw3.bearbeiter in (select gruppen from benutzergruppenlink where benutzer = ol.bearbeiter)
 ) > 0
 )
)
or 
bold_id in (select bold_id from verrechneteleistung vl where
 datum >= '\1' and datum <= '\2' and
 (
 (select count(*) from abwesenheit abw2 where (minutenabwesend is null or minutenabwesend < (CASE WHEN \3=1 THEN 9999 ELSE 0 END)) and vl.bearbeiter = abw2.bearbeiter and vl.datum >= abw2.datum and vl.datum <= abw2.bisdatum) > 0
 or
 (select count(*) from abwesenheit abw4 where
 (abw4.minutenabwesend is null or abw4.minutenabwesend < (CASE WHEN \3=1 THEN 9999 ELSE 0 END)) and vl.datum >= abw4.datum and vl.datum <= abw4.bisdatum and
 abw4.bearbeiter in (select gruppen from benutzergruppenlink where benutzer = vl.bearbeiter)
 ) > 0
 )
)

Projekte

Projekte mit offenen Leistungen bis Datum

SQL-Ordner, der alle Projekte ausgibt, die bis zu einem bestimmten Datum noch offene Leistungen haben.

  • Klasse: Projekt
  • Suchdialog anzeigen: ja
  • Expression:
    bold_id in (select projekt from offeneLeistung where Datum<='\1')

Achtung: Bereits verrechnete Leistungen werden nicht berücksichtigt, selbst wenn die Leistung zum eingegebenen Datum noch offen war.

Aktive Projekte ohne Leistungen

SQL-Ordner, der alle Projekte findet, die seit einem bestimmten Datum weder offene noch verrechnete Leistungen hatten, aber noch aktiv sind. Im Abfragedialog wird das Stichdatum angegeben.

  • Klasse: Projekt
  • Suchdialog anzeigen: ja
  • Expression:
    aktiv=1 and bold_id not in (select projekt from offeneLeistung where Datum>='\1') and bold_Id not in (select projekt from verrechneteLeistung where Datum >='\1')

Projekte mit produktiven Projekttypen und offenen Leistungen

SQL-Ordner, der alle produktiven Projekte ausgibt, die noch offene Leistungen haben.

  • Klasse: Projekt
  • Suchdialog anzeigen: nein
  • Firebird und MS SQL Server:
    typ in (select bold_id from projekttyp where produktiv=1) and bold_id in (SELECT projekt from offeneleistung)

Phasen

Phasen und Subphasen eines Projekts

SQL-Ordner, der alle Phasen und Subphasen eines Projekts anzeigt. Im Beispiel wird der Projekt-Code abgefragt.

  • Klasse: Projektphase
  • Suchdialog anzeigen: ja
  • Expression:
    (projekt in (select bold_id from projekt where code like '%\1%')) or (parentphase in (select ph.bold_id from projektphase ph where ph.projekt in (select bold_id from projekt where code like '%\1%')))

Diese Expression funktioniert nur für die erste Ebene von Subphasen. Subphasen von Subphasen werden nicht berücksichtigt.

Die Schwierigkeit besteht darin, dass Subphasen nur die Phase kennen, an der sie hängen, nicht aber das Projekt. Es muss also sowohl nach dem Projekt wie auch nach dem Projekt der Parentphase gesucht werden.

Da zwei mal die gleiche Tabelle (Projektphase) für unterschiedliche Anfragen angesprochen wird, muss mit Aliases gearbeitet werden (select ph.bold_id from projektphase ph where...)

Bearbeiter

Präsenzzeiten nach Bearbeiter von-bis

SQL-Ordner, der die Präsenzzeiten nach Bearbeiter in einem anzugebenden Datumsintervall anzeigt.

Als Defaultwerte werden der aktuell eingeloggte Benutzer sowie der letzte Monat angegeben. Im Suchdialog kann diese Abfrage dann auch anders formuliert werden.

  • Klasse: Praesenzzeit (muss von Hand ins Feld eingetippt werden)
  • Suchdialog anzeigen: ja
  • Expression:
    bold_id in (select bold_id from praesenzzeit where bearbeiter in (select bold_id from projektbearbeiter where UPPER(kuerzel) = UPPER('\1'))) and datum>='\2' and datum<='\3'

In der Abfrage müssen drei Suchfelder definiert werden:

Bezeichnung Feldtyp Defaultwert
Kürzel Zeichen

Timsession.allinstances->first.login.kuerzel

Von (Datum) Datum

date.incMonth(-1).firstofmonth (Erster des letzten Monats)

Bis (Datum) Datum

date.incMonth(-1).lastofmonth (Letzter des letzten Monats)

In den Listeneinstellungen müssen folgende Spalten definiert werden:

Name Expression Erweitere Listeneinstellungen
Bearbeiter

bearbeiter

 
Datum

datum

 
Von

von.formatdatetime('hh:mm')

 
Bis

bis.formatdatetime('hh:mm')

 
Präsenzzeit

minuten

dbmTim.MinuteRenderer, Spalte summieren

Aktivitäten

Suche nach E-Mails (Aktivitäten mit der Kontakt-Art E-Mail):

  • Klasse: Aktivitaet
  • Suchdialog anzeigen: ja
  • (optional) Icon: 82
  • SQL-Expression Firebird:
    KontaktArt=1 and Titel like '%\1%' and text containing '\2' and EmailSender like '%\3%' and EmailRecipients like '%\4%'
  • SQL-Expression MS SQL Server:
    KontaktArt=1 and Titel like '%\1%' and text like '%\2%' and EmailSender like '%\3%' and EmailRecipients like '%\4%'

In der Abfrage müssen drei Suchfelder definiert werden:

Bezeichnung Feldtyp
Titel

Zeichen

Text

Zeichen

Absender

Zeichen

Empfänger

Zeichen

Allgemeine Aktivitätensuche

SQL-Ordner für die Suche nach Aktivitäten mit den Kriterien Titel, Kontakt, Datum von-bis, Dateiname, Projekt (Code, Beschrieb, Betreffend) und Aktivitätstyp.

  • Klasse: Aktivitaet
  • Suchdialog anzeigen: ja
  • Expression: siehe weiter unten
Bezeichnung Feldtyp
Titel Zeichen
Kontakt Zeichen
Datum von Datum
Datum bis Datum
Datei Zeichen
Projekt Zeichen
Aktivitätstyp Zeichen
SQL-Expression für Firebird
Titel containing '\1' and Pfad containing '\5' and
(
BOLD_ID = (case when CHARACTER_LENGTH('\2') = 0 then bold_id else - BOLD_ID end) or
adresseintrag in (select BOLD_ID from Adresseintrag where Alias containing '\2')
)
and
(
BOLD_ID = (case when CHARACTER_LENGTH('\3') = 0 then bold_id else - BOLD_ID end) or
CAST(Datum as Date) >= CAST('\3' as Date)
)
and
(
BOLD_ID = (case when CHARACTER_LENGTH('\4') = 0 then bold_id else - BOLD_ID end) or
CAST(Datum as Date) < CAST('\4' as Date)+1
)
and
(
BOLD_ID = (case when CHARACTER_LENGTH('\6') = 0 then bold_id else - BOLD_ID end) or
projekt in (select BOLD_ID from Projekt where Code containing '\6' or Beschrieb containing '\6' or Betreffend containing '\6')
)
and
(
BOLD_ID = (case when CHARACTER_LENGTH('\7') = 0 then bold_id else - BOLD_ID end) or
typ in (select BOLD_ID from AktivitaetsTyp where bezeichnung containing '\7')
)
SQL-Expression für MS SQL Server
Titel like '%\1%' and Pfad like '%\5%' and
(
BOLD_ID = (case when len('\2') = 0 then bold_id else - BOLD_ID end) or
adresseintrag in (select BOLD_ID from Adresseintrag where Alias like '%\2%')
) 
and 
(
BOLD_ID = (case when len('\3') = 0 then bold_id else - BOLD_ID end) or
Datum >= '\3' or Datum is Null
)
and
(
BOLD_ID = (case when len('\4') = 0 then bold_id else - BOLD_ID end) or
Datum < cast('\4' as datetime)+1 or Datum is null
)
and
(
BOLD_ID = (case when len('\6') = 0 then bold_id else - BOLD_ID end) or
projekt in (select BOLD_ID from Projekt where Code like '%\6%' or Beschrieb like '%\6%' or Betreffend like '%\6%')
)
and 
(
BOLD_ID = (case when len('\7') = 0 then bold_id else - BOLD_ID end) or
typ in (select BOLD_ID from AktivitaetsTyp where bezeichnung like '%\7%')
)
Bitte wählen Sie Ihren Standort