# Daten

Abfrage:
SHAPE {Select TBL_CONTACT.CONTACTID As hide_ContactID, TBL_CONTACT.FULLNAME As Kontaktname, TBL_CONTACT.CONTACTID As showactcontact_Kontakt, TBL_CONTACT.COMPANYNAME As Firmenname, TBL_CONTACT.CATEGORY As [Status1 (ID/Status)], TBL_CONTACT.DEPARTMENT As [Status2 (Abteilung)], TBL_CONTACT.CREATEDATE As [formatshortdate_Erstellt am], TBL_ACCESSOR.NAME As [Erstellt von] From TBL_CONTACT Inner Join TBL_ACCESSOR On TBL_CONTACT.CREATEUSERID = TBL_ACCESSOR.ACCESSORID Where TBL_CONTACT.CREATEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And TBL_CONTACT.CREATEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And TBL_CONTACT.ISPRIVATE = 0 And TBL_CONTACT.TYPENUM = 0 Order By [formatshortdate_Erstellt am] Desc}  AS Zwei_Kontakte APPEND (( SHAPE {Select TBL_CONTACT.CONTACTID As hide_ContactID, TBL_HISTORY.HISTORYID As hide_HistoryID, TBL_HISTORY.STARTTIME As Datum, TBL_USER.USERLOGIN As Benutzer, IsNull(TBL_ACTIVITYTYPE.NAME, 'Sonstige') As Typ, TBL_HISTORY.REGARDING As Betreff, TBL_HISTORYTYPE.NAME As Ergebnis From TBL_HISTORY Inner Join TBL_HISTORYTYPE On TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID Inner Join TBL_USER On TBL_HISTORY.MANAGEUSERID = TBL_USER.USERID Inner Join TBL_ACTIVITYTYPE On TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID Inner Join TBL_CONTACT_HISTORY On TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID Inner Join TBL_CONTACT On TBL_CONTACT_HISTORY.CONTACTID = TBL_CONTACT.CONTACTID Where TBL_CONTACT.CREATEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And TBL_CONTACT.CREATEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) Order By Datum Desc}  AS Drei_Protokoll APPEND ({Select TBL_HISTORY.HISTORYID As hide_HistoryID, TBL_HISTORY.DETAILS As rtf2text_Details From TBL_HISTORY Inner Join TBL_CONTACT_HISTORY On TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID Inner Join TBL_CONTACT On TBL_CONTACT.CONTACTID = TBL_CONTACT_HISTORY.CONTACTID Where TBL_CONTACT.CREATEDATE >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) 
And TBL_CONTACT.CREATEDATE <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And Len(TBL_HISTORY.DETAILS) >= 1}  AS Vier_Details RELATE 'hide_HistoryID' TO 'hide_HistoryID') AS Vier_Details) AS Drei_Protokoll RELATE 'hide_ContactID' TO 'hide_ContactID') AS Drei_Protokoll


Abfrage:
SHAPE {SELECT DISTINCT TBL_HISTORY.MANAGEUSERID AS hide_ManageUserID, TBL_CONTACT.FULLNAME AS Benutzer, COUNT(DISTINCT TBL_CONTACT_HISTORY.CONTACTID) AS right_Kontaktanzahl, COUNT(DISTINCT TBL_HISTORY.HISTORYID) AS right_Protokollanzahl, TBL_CONTACT.LASTNAME AS hide_Nachname, MAX(TBL_HISTORY.STARTTIME) AS [right_formatlocaldate_Letztes Protokoll] FROM TBL_HISTORY INNER JOIN TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN TBL_CONTACT_HISTORY ON TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID INNER JOIN TBL_USER ON TBL_HISTORY.MANAGEUSERID = TBL_USER.USERID INNER JOIN TBL_CONTACT ON TBL_USER.CONTACTID = TBL_CONTACT.CONTACTID LEFT OUTER JOIN TBL_ACTIVITYTYPE ON TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID WHERE (TBL_HISTORY.STARTTIME >= CONVERT (Datetime, '{!RegistryValue, WP_Start}', 104)) AND (TBL_HISTORY.STARTTIME <= CONVERT (Datetime, '{!RegistryValue, WP_Ende}', 104)) AND (TBL_HISTORY.ISPRIVATE = 0) GROUP BY TBL_HISTORY.MANAGEUSERID, TBL_CONTACT.FULLNAME, TBL_CONTACT.LASTNAME}  AS Null_Benutzer APPEND (( SHAPE {SELECT TBL_HISTORY.MANAGEUSERID AS hide_ManageUserID, ISNULL(TBL_ACTIVITYTYPE.NAME, 'Sonstige') AS Protokolltyp, COUNT(DISTINCT TBL_HISTORY.HISTORYID) AS [right_Anzahl Protokolltyp], MAX(TBL_HISTORY.STARTTIME) AS right_formatlocaldate_Zuletzt, COUNT(DISTINCT TBL_CONTACT.CONTACTID) AS [right_Betroffene Kontakte] FROM TBL_HISTORY INNER JOIN TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN TBL_CONTACT_HISTORY ON TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID INNER JOIN TBL_CONTACT ON TBL_CONTACT_HISTORY.CONTACTID = TBL_CONTACT.CONTACTID LEFT OUTER JOIN TBL_ACTIVITYTYPE ON TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID WHERE (TBL_HISTORY.STARTTIME >= CONVERT (Datetime, '{!RegistryValue, WP_Start}', 104)) AND (TBL_HISTORY.STARTTIME <= CONVERT (Datetime, '{!RegistryValue, WP_Ende}', 104)) AND (TBL_HISTORY.ISPRIVATE = 0) GROUP BY TBL_HISTORY.MANAGEUSERID, TBL_ACTIVITYTYPE.NAME}  AS Eins_Protokolltypen APPEND (( SHAPE {SELECT DISTINCT TBL_HISTORY.MANAGEUSERID AS hide_ManageUserID, ISNULL(TBL_ACTIVITYTYPE.NAME, 'Sonstige') AS hide_ActivityTypeName, TBL_CONTACT_HISTORY.CONTACTID AS hide_ContactID, TBL_CONTACT.FULLNAME AS Kontaktname, TBL_CONTACT.CONTACTID AS showactcontact_Kontakt, TBL_CONTACT.COMPANYNAME AS Firmenname, TBL_CONTACT.CATEGORY AS Status1, TBL_CONTACT.DEPARTMENT AS Status2, COUNT(DISTINCT TBL_HISTORY.HISTORYID) AS [right_Protokolle Gesamt] FROM TBL_HISTORY INNER JOIN TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN TBL_CONTACT_HISTORY ON TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID INNER JOIN TBL_CONTACT ON TBL_CONTACT_HISTORY.CONTACTID = TBL_CONTACT.CONTACTID LEFT OUTER JOIN TBL_ACTIVITYTYPE ON TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID WHERE (TBL_HISTORY.STARTTIME >= CONVERT (Datetime, '{!RegistryValue, WP_Start}', 104)) AND (TBL_HISTORY.STARTTIME <= CONVERT (Datetime, '{!RegistryValue, WP_Ende}', 104)) AND (TBL_HISTORY.ISPRIVATE = 0) GROUP BY TBL_HISTORY.MANAGEUSERID, ISNULL(TBL_ACTIVITYTYPE.NAME, 'Sonstige'), TBL_CONTACT_HISTORY.CONTACTID, TBL_CONTACT.FULLNAME, TBL_CONTACT.CONTACTID, TBL_CONTACT.COMPANYNAME, TBL_CONTACT.CATEGORY, TBL_CONTACT.DEPARTMENT ORDER BY TBL_HISTORY.MANAGEUSERID, ISNULL(TBL_ACTIVITYTYPE.NAME, 'Sonstige'), TBL_CONTACT.COMPANYNAME}  AS Zwei_Kontakte APPEND (( SHAPE {SELECT TBL_HISTORY.STARTTIME AS hide_Datum, TBL_HISTORY.MANAGEUSERID AS hide_ManageUserID, TBL_CONTACT.CONTACTID AS hide_ContactID, TBL_HISTORY.STARTTIME AS formatlocaldate_Datumseintrag, '' + ISNULL(TBL_ACTIVITYTYPE.NAME, 'Sonstige') + '' AS Typ, TBL_HISTORYTYPE.NAME AS Ergebnis, TBL_HISTORY.REGARDING AS Betreff, TBL_HISTORY.HISTORYID AS hide_HistoryID, ISNULL(TBL_ACTIVITYTYPE.NAME, N'Sonstige') AS hide_ActivityTypeName FROM TBL_HISTORY INNER JOIN TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN TBL_CONTACT_HISTORY ON TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID INNER JOIN TBL_CONTACT ON TBL_CONTACT_HISTORY.CONTACTID = TBL_CONTACT.CONTACTID LEFT OUTER JOIN TBL_ACTIVITYTYPE ON TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID WHERE (TBL_HISTORY.STARTTIME >= CONVERT (Datetime, '{!RegistryValue, WP_Start}', 104)) AND (TBL_HISTORY.STARTTIME <= CONVERT (Datetime, '{!RegistryValue, WP_Ende}', 104)) AND (TBL_HISTORY.ISPRIVATE = 0) ORDER BY TBL_HISTORY.STARTTIME DESC}  AS Drei_Protokolle APPEND ({SELECT DISTINCT TBL_HISTORY.STARTTIME AS hide_Starttime, CONVERT(nvarchar(MAX),TBL_HISTORY.DETAILS) AS rtf2text_Details, TBL_HISTORY.HISTORYID AS hide_HistoryID FROM TBL_HISTORY INNER JOIN TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN TBL_CONTACT_HISTORY ON TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID INNER JOIN TBL_CONTACT ON TBL_CONTACT_HISTORY.CONTACTID = TBL_CONTACT.CONTACTID LEFT OUTER JOIN TBL_ACTIVITYTYPE ON TBL_HISTORYTYPE.ACTIVITYTYPEID = TBL_ACTIVITYTYPE.ACTIVITYTYPEID WHERE TBL_HISTORY.STARTTIME >= CONVERT(Datetime,'{!RegistryValue, WP_Start}',104) AND TBL_HISTORY.STARTTIME <= CONVERT(Datetime,'{!RegistryValue, WP_Ende}',104) AND TBL_HISTORY.ISPRIVATE = 0 AND DataLength(TBL_HISTORY.DETAILS) > 0}  AS Vier_Details RELATE 'hide_HistoryID' TO 'hide_HistoryID') AS Vier_Details) AS Drei_Protokolle RELATE 'hide_ManageUserID' TO 'hide_ManageUserID','hide_ContactID' TO 'hide_ContactID','hide_ActivityTypeName' TO 'hide_ActivityTypeName') AS Drei_Protokolle) AS Zwei_Kontakte RELATE 'hide_ManageUserID' TO 'hide_ManageUserID','Protokolltyp' TO 'hide_ActivityTypeName') AS Zwei_Kontakte) AS Eins_Protokolltypen RELATE 'hide_ManageUserID' TO 'hide_ManageUserID') AS Eins_Protokolltypen


Abfrage:
Select Berechnung.Minuten As [right_format#,##_Gesamtdauer Minuten], Berechnung.Stunden As [right_format#,##0*000_Gesamtdauer Stunden], Berechnung.Stunden * Verrechnungssatz.Stundensatz As [right_format#,##0*00_Gesamtumsatz] From (Select '{!RegistryValue, Zahl01}' As Stundensatz) Verrechnungssatz, (Select Sum(TBL_HISTORY.DURATION) As Minuten, Sum(TBL_HISTORY.DURATION / 60.0) As Stunden From TBL_ACTIVITYTYPE Inner Join TBL_HISTORYTYPE On TBL_ACTIVITYTYPE.ACTIVITYTYPEID = TBL_HISTORYTYPE.ACTIVITYTYPEID Inner Join TBL_HISTORY On TBL_HISTORYTYPE.HISTORYTYPEID = TBL_HISTORY.HISTORYTYPEID Where TBL_HISTORY.STARTTIME >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And TBL_HISTORY.STARTTIME <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And TBL_ACTIVITYTYPE.NAME = '{!RegistryValue, Suchbegriff01}' Group By TBL_ACTIVITYTYPE.NAME) Berechnung


Abfrage:
Select TBL_CONTACT.FULLNAME As Kontakt, Dauer.CONTACTID As showactcontact_Kontakt, TBL_CONTACT.COMPANYNAME As Firma, Dauer.[Gesamtdauer Minuten] As [right_format#,##_Dauer Minuten], Dauer.[Gesamtdauer Stunden] As [right_format#,##0*000_Dauer Stunden], Dauer.[Gesamtdauer Stunden] * Verrechnungssatz.Stundensatz As [right_format#,##0*00_RG-Betrag] From (Select Sum(TBL_HISTORY.DURATION / 60.0) As [Gesamtdauer Stunden], TBL_CONTACT_HISTORY.CONTACTID, Sum(TBL_HISTORY.DURATION) As [Gesamtdauer Minuten] From TBL_HISTORY Inner Join TBL_CONTACT_HISTORY On TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID Inner Join TBL_HISTORYTYPE On TBL_HISTORYTYPE.HISTORYTYPEID = TBL_HISTORY.HISTORYTYPEID Inner Join TBL_ACTIVITYTYPE On TBL_ACTIVITYTYPE.ACTIVITYTYPEID = TBL_HISTORYTYPE.ACTIVITYTYPEID Where TBL_HISTORY.STARTTIME >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And TBL_HISTORY.STARTTIME <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) And TBL_ACTIVITYTYPE.NAME = '{!RegistryValue, Suchbegriff01}' Group By TBL_CONTACT_HISTORY.CONTACTID, TBL_ACTIVITYTYPE.NAME) Dauer Inner Join TBL_CONTACT On TBL_CONTACT.CONTACTID = Dauer.CONTACTID, (Select '{!RegistryValue, Zahl01}' As Stundensatz) Verrechnungssatz


# Rubriken

Bemerkung:
Neu angelegte Kontakte im Zeitraum: '{!RegistryValue, WP_Start}'  -  '{!RegistryValue, WP_Ende}'  /  Für die Vorgabe des gefilterten Datumsbereiches verwenden Sie das AutoData-Script "SetKIParameter".


Bemerkung:
Protokollaufzeichnung beteiligter Act!-Benutzer im Zeitraum: '{!RegistryValue, WP_Start}'  -  '{!RegistryValue, WP_Ende}'  /  Für die Vorgabe des gefilterten Datumsbereiches verwenden Sie das AutoData-Script "SetKIParameter".


Bemerkung:
Zeitraum: '{!RegistryValue, WP_Start}'  -  '{!RegistryValue, WP_Ende}'    /   Protokolltyp: '{!RegistryValue, Suchbegriff01}'   /   Stundenverrechnungssatz:   '{!RegistryValue, Zahl01}'.

Zeigt alle Kontakte an, für die innerhalb des Zeitraumes die gesuchten Protokolleinträge existieren. Der Umsatz wird mit dem vorgegebenen Stundenverrechnungssatz  berechnet.

Die Festlegungen erfolgen über das AutoData-Script "SetKIParameter".


