SQL Query - Ausgabe der Kundenbenutzer mit Summe der aktuell offenen Tickets

[christian]

Hallo zusammen,


wir möchten in der Firma gern bei einem eingehenden Anruf sehen z. B. wie viele Tickets der Anrufer aktuell offen hat. U. a. dazu nutzen wir die CTI-Lösung ProCall von estos in Verbindung mit dem MetaDirectory. Der ProCall Client zeigt bei einem eingehenden Anruf ein Gesprächsfenster an in das man diverse Dinge einbauen kann.


Ich bin also auf der Suche nach einer SQL Query welche mir die im System angelegten gültigen Kundenbenutzer (also den kompletten Inhalt der Table "customer_user" abzüglich derer im Status "ungültig") und in der letzten Spalte die aktuell offenen Tickets (bzw. die Tickets mit bestimmten ticket_state_id) dieses Kundenbenutzers als Summe anzeigt.


Diese Query wird vom MetaDirectory dann alle 15min ausgeführt und das Ergebnis importiert. Somit verfügt MetaDirectory über eine "Kontaktdatenbank" inkl. der zum Zeitpunkt der Abfrage offenen Tickets pro Kundenbenutzer. Auf diese Datenbank greift das ProCall Gesprächsfenster zu und zeigt uns die Daten an (Anruferinformationen und offene Tickets)


So sollte das Query-Ergebnis beispielsweise aussehen (hier angegeben sind nur die Spaltenüberschriften der gewünschten Tabelle):


ID
LOGIN
EMAIL
CUSTOMER_ID
PW
TITLE
FIRST_NAME
LAST_NAME
PHONE
FAX
MOBILE
STREET
ZIP
CITY
COUNTRY
COMMENTS
VALID_ID
CREATE_TIME
CREATE_BY
CHANGE_TIME
CHANGE_BY
OPEN_TICKETS


Ich muss gestehen, dass ich nicht wirklich Ahnung von SQL Querys habe und schonmal ein bisschen mit SUM und COUNT "experimentiert" habe. Allerdings ohne Erfolg ;)


Bin auf eure Ideen gespannt! Vielen Dank schonmal für jegliche Unterstützung.

[Jörg Brückner]

Hallo Christian,

ich habe hier mal ein SQL Statment ausgearbeitet, wo Du die Daten wie gewünscht abfragen kannst. Ich hoffe das hilft Dir so zunächst weiter.

Hier die Abfrage:


select * from (
select * from customer_user where customer_id = '456 GmbH'
) x, (
select count(*) from customer_user INNER JOIN ticket ON customer_user.customer_id = ticket.customer_id where (ticket.ticket_state_id = 4 or ticket.ticket_state_id = 1) and customer_user.customer_id = '456 GmbH'
) y



den Parameter "ticket_state_id" müsstest Du noch entsprechend anpassen. Ich habe jetzt 2 Status definiert, der eine Status hat die ID1, der andere die 4. Das kann in Deinem KIX natürlich anders sind. Daher bitte anpassen.

Was noch von Deiner Seite aus zu klären wäre, woher kommt die "customer_id" aus dem CIT in das SQL Statment. Das müsstest Du dann ggf. Über einen Platzhalter regeln, bzw. muss das System ja aus der Telefonnummer des Anrufers die ID herausfinden und diese in dem SQL Statment ersetzen.

Aber mit dieser Abfrage, solltest Du erst einmal weiterkommen.
Viel Erfolg
Jörg

[christian]

Hallo Jörg,


vielen lieben Dank für die Abfrage. Sie funktioniert soweit. Ich vermute aber, dass ich meine Anforderung nicht 100% verständlich beschrieben habe.


Deine Abfrage gibt mir für eine bestimmte Kundennummer (customer_id) die dazu angelegten Kunden-Benutzer aus. Ich sehe dann allerdings bei jedem Kunden-Benutzer (also in jeder Zeile der Ausgabe) in der Spalte "COUNT" die selbe Anzahl (offener) Tickets. Es scheint so, als ob die Abfrage auf offene Tickets pro Kundennummer (customer_id) abstellt. Ich benötige aber die (offenen) Tickets pro Kunden-Benutzer.

Ich benötige die Abfrage also wie folgt angepasst:

- Ausgabe ALLER gültigen (das müsste das Feld "valid_id" sein) Kunden-Benutzer  (unabhängig von der Kundennummer)
- in der COUNT-Spalte Ausgabe der Tickets (die der angegeben Status-ID entsprechen) pro Kunden-Benutzer


Wir wollen quasi bei einem eingehenden Anruf die Anzahl der offenen Tickets dieses speziellen Kunden-Benutzers (nicht der kompletten Kunden-Firma) anzeigen lassen. Das Matching machen wir in der CTI-Software über die eingehende Telefonnummer. Diese ist ja auch im KIX bei dem Kunden-Benutzer hinterlegt und so haben wir unser Matching.



Der Transfer der Daten in die CTI-Applikation erfolgt durch einen Import der Daten alle 5 Minuten. D. h. der CTI-Server führt die SQL-Abfrage im 5-Minuten-Takt aus, erhält dadurch das Ergebnis und importiert es in die CTI-Software. Dort erfolgt auch die Feld-/Wertezuordnung.


Ich hoffe so ist es verständlich. Falls du noch Infos brauchst einfach melden.


DANKE DANKE DANKE


Ergänzung:


Ich habe die Anfrage gerade mal umgebaut und die Bedingung Kundennummer (customer_id) entfernt:



select * from (
select * from customer_user
) x, (
select count(*) from customer_user INNER JOIN ticket ON customer_user.customer_id = ticket.customer_id where (ticket.ticket_state_id = 4 or ticket.ticket_state_id = 1)
) y


Dann gibt er mir in der Count-Spalte ebenfalls bei allen Zeilen (also Kunden-Benutzern) die gleiche Zahl aus.

[Jörg Brückner]

Hallo Christian,


leider verstehe ich noch nicht so ganz, wie genau in der Ausgabe dann aussehen soll.
Eine  "- Ausgabe ALLER gültigen (das müsste das Feld "valid_id" sein) Kunden-Benutzer  (unabhängig von der Kundennummer)"

ist mit folgenden SQL-Statment möglich:
select * from customer_user where valid_id = 1



Und wenn ich die Abfrage nicht auf Kundennummern Ebene durchführen möchte, kann ich ja auch aus der DB statt die "customer_id" einen anderen Wert nutzen.
Zum Besipiel die "email" oder die Spalte "login". Auch Vor oder Nachname ist möglich, nur kann es zu Dubletten führen.

Hier nochmal ein Bsp. aus der Datenbank in der Anlage.

[christian]

Hallo Jörg,


danke für deine Antwort. Die Anforderung ist nicht einfach nur alle "gültigen" Kunden-Benutzer abzufragen, sondern alle gültigen Kunden-Benutzer inkl. der zum Zeitpunkt der Abfrage "offenen" (also alle Stati außer erfolgreich / erfolglos geschlossen) Tickets der Kundenbenutzer.

Die Tabellenausgabe müsste dann also um eine Spalte erweitert werden in der dann einfach eine Zahl steht. Diese Zahl ist die Anzahl der offenen Tickets dieses Kundenbenutzers.


Danke und viele Grüße

[Jörg Brückner]

Hallo Christian,

ich habe nun eine Spalte ergänzt in der dann einfach eine Zahl steht. Diese Zahl ist die Anzahl der offenen Tickets dieses Kundenbenutzers.
SQL Statment:


select * from (
select * from customer_user where customer_id = '456 GmbH'
) x, (
select count(*) from customer_user INNER JOIN ticket ON customer_user.customer_id = ticket.customer_id where (ticket.ticket_state_id = 4 or ticket.ticket_state_id = 1) and customer_user.customer_id = '456 GmbH'
) y, (
select count(*) from customer_user INNER JOIN ticket ON customer_user.customer_id = ticket.customer_id where (ticket.ticket_state_id = 4 or ticket.ticket_state_id = 1) and customer_user.login = 'lila') z

[christian]

Hallo Jörg,


danke das ist soweit verstanden. Ich brauche aber nicht nur einen Kunden-Benutzer in der Liste sondern ALLE (egal welcher Kunde).


Wenn ich deine Abfrage so verwende erhalte ich:

   
• In der Tabelle nur die angelegten Kunden-Benutzer dieses einen Kunden -> ich benötige aber ALLE (egal welcher Kunde) angelegten Benutzer im System (also auch die der anderen Kunden)
• -- das würde ich durch Entfernung der Bedingung "where customer_id = '456 GmbH'" erreichen
• In der ersten Count-Spalte die Anzahl der Tickets die nicht im Status "erfolgreich geschlossen" oder "erfolglos geschlossen" dieses einen Kunden summiert über alle Kunden-Benutzer dieses einen Kunden
• -- das kann von mir aus so bleiben wenn sich der Wert in der Spalte dann entsprechend auch verändert (also wenn dann in der Ausgabetabelle wirklich ALLE Benutzer im System angezeigt werden sind dort natürlich auch Benutzer verschiedener Kunden drin - und jeder Kunde (also Kunden-Firma) hat natürlich eine andere Gesamtzahl Tickets in den entsprechenden Stati)
• In der zweiten Count-Spalte die Anzahl der Tickets die nicht im Status "erfolgreich geschlossen" oder "erfolglos geschlossen" dieses einen (in der Abfrage definierten) Kunden-Benutzers
• -- hier ist allerdings das Fehlverhalten, dass die Tabelle ja alle Kunden-Benutzer (momentan noch lediglich einer einzigen "Kunden-Firma") ausgibt aber in der zweiten Count-Spalte steht immer die gleiche Zahl -> das kann ja nicht sein weil nicht jeder Kunden-Benutzer die gleiche Anzahl offener Tickets im System hat
• in der Abfrage fehlt glaube ich generell der Filter, dass nur gültige Kunden-Benutzer ausgegeben werden sollen
• -- derzeit sehe ich auch die ungültigen - das soll nicht sein

Ich füge mal einen Screenshot bei wie es jetzt aussieht und wie es sein soll.


Wenn du weitere Erklärungen o.ä. benötigst bitte Bescheid geben.


Viele Grüße
Christian

[Jörg Brückner]

Hallo Christian,


ich denke so sollte es doch passen:


select * from (
select * from customer_user
) x, (
select count(*) from customer_user INNER JOIN ticket ON customer_user.customer_id = ticket.customer_id where (ticket.ticket_state_id = 4 or ticket.ticket_state_id = 1) and customer_user.customer_id = '456 GmbH'
) y, (
select count(*) from customer_user INNER JOIN ticket ON customer_user.customer_id = ticket.customer_id where (ticket.ticket_state_id = 4 or ticket.ticket_state_id = 1) and (customer_user.login = 'lila' and customer_user.valid_id = '1')) z



neu:
- nur gültige Kunden-Benutzer (hier ggf noch die IDś anpassen. In meinem Testsystem ist die 1 gültig und die 0 ungültig)
- Abfrage aller Kunden (ohne Beschränkung)


Was die korrekten Status angeht, das musst Du, wie schon einmal beschreiben, selbst anpassen, da ich nicht weiß, welche IDś die Status Eures Systemes besitzen. Deswegen hatte ich pauschal mal ID 1 und 4 genommen. Das musst Du selbstständig ändern oder ergänzen!

Was die Formatierung angeht, bestimmt sollte es noch möglich sein eine Summenspalte zu ergänzen, aber in der Liste die Einträge zu entfernen ist mit einem reinen SQL Statment nicht möglich. Hier bedarf es etwas zusätzliche Programmierleistung. Sowas müssten wir dann in der Entwicklung als Aufwandsschätzung anfragen.
Ein SQL Statment ist ja in erster Linie dazu da entsprechende Werte auch einer DB zu liefern. Schön machen muss man das dann im entsprechenden System, oder in einem individuellen Bericht programmieren.
Dazu kannst Du im Support ein Ticket öffnen oder über den Vertrieb eine entsprechende Anfrage aufgeben.


An den Stellen wo jetzt im SQL Statment Werte stehen, diese müssten entsprechend mit Platzhaltern oder Daten aus dem System kommen, damit die Abfrage entsprechende Werte liefert. Das muss dann aber im CTI/ProCall/... angepasst werden.

Herzliche Grüße
Jörg