SQL Kunden sollen nur einmal ausgegeben werden

[Krik]

Moin

Verwendeter SQL-Server ist MS SQL 2005.

Ich habe zwei Tabellen T_KND für Kunden und T_PRJ für den Kunden zugeordnete Projekte.
Ich will Kunden finden, deren letztes Projekt vor einem spezifizierten Datum liegt und/oder deren Projekte alle als 'Abgeschlossen' eingetragen sind.

Dafür habe ich mir diese Abfrage zusammengebastelt:

SELECT T_KND.Nummer AS 'Kundennummer', T_PRJ.Nummer AS 'Projektnummer',
       T_PRJ.PROJEKTSTATUS AS 'Projektstatus', T_PRJ.DATUM AS 'Projektdatum'
FROM T_KND, T_PRJ
WHERE T_PRJ.Mandant_ID = '1'
  AND T_KND.Mandant_ID = T_PRJ.Mandant_ID
  AND T_KND.Nummer = T_PRJ.KUNDENNummer
  AND T_PRJ.PROJEKTSTATUS = 'Abgeschlossen'
  AND T_PRJ.DATUM < '15.09.2010'
ORDER BY T_KND.Nummer ASC, T_PRJ.DATUM ASC

Die Sache ist jetzt, der Kunde soll nur mit seinem neusten Projekt ausgegeben werden und nicht mit jedem Projekt, dass er mal bearbeitet hat.
Im Moment wird jedes Projekt des Kunden aufgelistet.

Mandant_ID wird von mir vorher festgelegt. Es zeigt an, für welchen Mandanten/User der Kunde und das Projekt sichtbar ist, da mehrere Parteien auf die DB zugreifen.

Weiß jemand Rat?
Gruß, Laurin

 

[capilano]

Hast du es schon mit dem Befehlzusatz 'DISTINCT' versucht?

SELECT DISTINCT blablabla

 

[steff0rn]

jau, auch mein tipp: direkt nach dem select ein distinct, dann sollte es klappen

 

[Devil24]

Ein Distinct allein reicht da nicht, da ja das Datum auch mit ausgegeben werden soll.
Wenn man nur das letzte Projekt sehen will, sollte man die Datensätze nach Kunde gruppieren und nur das letzte Datum mit Max ausgeben.

SELECT T_KND.Nummer AS 'Kundennummer', Max(T_PRJ.Nummer) AS 'Projektnummer',
Max(T_PRJ.PROJEKTSTATUS) AS 'Projektstatus', Max(T_PRJ.DATUM) AS 'Projektdatum'
FROM T_KND, T_PRJ
WHERE T_PRJ.Mandant_ID = '1'
AND T_KND.Mandant_ID = T_PRJ.Mandant_ID
AND T_KND.Nummer = T_PRJ.KUNDENNummer
AND T_PRJ.PROJEKTSTATUS = 'Abgeschlossen'
AND T_PRJ.DATUM < '15.09.2010'
GROUP BY T_KND.Nummer

 

[Krik]

DISTINCT macht leider keinen Unterschied bei der Ausgabe. Die Kunden werden trotzdem mehrfach ausgegeben.

@Devil24
Es funktioniert perfekt. So schnell habe eine Lösung gar nicht erwartet.


Eine Frage habe ich noch. Werden nur Kunden ausgegeben, deren Projekte alle als 'Abgeschlossen' markiert wurden?

 

[MacGyver]

Die Sache ist jetzt, der Kunde soll nur mit seinem neusten Projekt ausgegeben werden und nicht mit jedem Projekt, dass er mal bearbeitet hat.

Dann musst du das Datum über eine Aggregatfunktion selektieren. MAX(Datum) oder so. Dann wird nur das neueste Datum ausgegeben und nicht alle. Damit bekommst du dann automatisch auch nur 1x den Kunden.
Es sollte gehen, auch wenn ich es nicht komplett überblicken kann. Eventuell müsste man noch einen Gruppenwechsel anhängen und statt WHERE dann HAVING nehmen.

 

[tm0975]

distinct ist etwas anderes. bei einem distinct werden doppelte datensätze entfernt. das ist hie nicht der fall, denn es handelt sich um mehrere unterschiedliche projekte. hier ist mit group by zu verfahren.

 

[Devil24]

Nein es werden nur die Kunden ausgegeben, wo zumindest ein Projekt als Abgeschlossen gekennzeichnet ist.
Die anderen Projekte werden in der WHERE Bedingung ja ignoriert.

 

[Krik]

wo zumindest ein Projekt als Abgeschlossen gekennzeichnet ist.

Das interpretiere ich jetzt so, dass alle Kunden angezeigt werden, die mindestens ein abgeschlossenes Projekt haben. Das schließt aber nicht aus, dass da auch Projekte dazwischen sind, die zb als 'Neu' eingetragen sind, aber durch GROUP BY untergehen. Hm...

Allmählich glaube ich, es wäre besser, sich erst alle Kundenummern zu besorgen und getrennt für jeden Kunde schauen, ob er nur abgeschlossene Projekte hat. Ich habe gehofft, das mit einer gescheiten SQL-Abfrage in einem Rutsch hinzubekommen.

 

[Devil24]

So hab ichs gemeint
Genauergesagt: die WHERE Bedingung greift früher als das Group by, d.h. es werden nur die abgeschlossenen Projekte in der Ergebnismenge vorhanden sein, die dann noch gruppiert werden.

 

[Krik]

Ah, perfekt. Super, danke an alle, die mir geholfen haben.

 

[Devil24]

Das kann man auch in einer Abfrage hinbekommen. Ist nur etwas komplizierter.
Man müsste erst nach Kunden gruppieren über alle Projekte hinweg. Wenn man mehr als ein Ergebnis bekommt hat er Projekte mit unterschiedlichem Status, diese Kunden muss man dann ignorieren.

 

[Krik]

Ich kann dir nicht ganz folgen. Ich gebe zu, dass ich nur recht kurz in der Schule mal mit SQL zu tun hatte und seit dem nie mehr. Einfache SELECT FROM WHERE Sachen bekomme ich hin, aber das, was du da beschreibst, ist mir zu hoch.

 

[Devil24]

Probiere mal das:

SELECT T_KND.Nummer AS 'Kundennummer', Max(T_PRJ.Nummer) AS 'Projektnummer',
Max(T_PRJ.PROJEKTSTATUS) AS 'Projektstatus', Max(T_PRJ.DATUM) AS 'Projektdatum'
FROM T_KND, T_PRJ
WHERE T_PRJ.Mandant_ID = '1'
AND T_KND.Mandant_ID = T_PRJ.Mandant_ID
AND T_KND.Nummer = T_PRJ.KUNDENNummer
AND T_PRJ.PROJEKTSTATUS = 'Abgeschlossen'
AND T_PRJ.DATUM < '15.09.2010'
AND T_KND.Nummer IN (
SELECT T_KND.Nummer
FROM T_KND, T_PRJ
WHERE T_PRJ.Mandant_ID = '1'
AND T_KND.Mandant_ID = T_PRJ.Mandant_ID
AND T_KND.Nummer = T_PRJ.KUNDENNummer
GROUP BY T_KND.Nummer,T_PRJ.PROJEKTSTATUS
HAVING COUNT(*) = 1
)
GROUP BY T_KND.Nummer

Jetzt müsstest Du alle Projekte die abgeschlossen sind bekommen und noch zusätzlich die Kunden die NUR abgeschlossene Projekte haben durch die IN Abfrage.
Das Having Count(*) sorgt dafür dass nur Kunden getroffen werden die alle Pojekte im gleichen Status haben.
Ich weiß ist etwas kompliziert, aber sollte funktionieren....

 

[Krik]

Oha, kompliziert.
Die Abfrage funktioniert fehlerfrei. Ob sie aber das richtige Ergebnis abwirft, kann ich noch nicht sagen. Immerhin spuckt der SQL-Server da >1500 Ergebnisse raus, da muss ich ein paar Stichproben machen. ^^

 

[Devil24]

Ja kann sein dass ich noch was vergessen habe. Kanns ja nicht testen

 

[Krik]

Ich habe versucht, die Abfrage zu verstehen, aber das ist mir echt zu hoch.

SELECT T_KND.Nummer AS 'Kundennummer', Max(T_PRJ.Nummer) AS 'Projektnummer',  // Gib mir die vier Werte aus.
       Max(T_PRJ.PROJEKTSTATUS) AS 'Projektstatus', Max(T_PRJ.DATUM) AS 'Projektdatum'
FROM T_KND, T_PRJ // sie stehen in diesen zwei Tabellen
WHERE T_PRJ.Mandant_ID = '1' // Mandant_ID muss 1 sein
  AND T_KND.Mandant_ID = T_PRJ.Mandant_ID // Die Mandant_ID in beiden Tabellen muss identisch sein.
  AND T_KND.Nummer = T_PRJ.KUNDENNummer // Die Kundennummern müssen übereinstimmen.
  AND T_PRJ.PROJEKTSTATUS = 'Abgeschlossen' // Alle Projekte müssen abgeschlossen sein.
  AND T_PRJ.DATUM < '15.09.2010' // Das letzte Projekt liegt vor diesem Datum.
  AND T_KND.Nummer IN ( // ???
                       SELECT T_KND.Nummer // Gib mir alle Kundenummer zurück
                       FROM T_KND, T_PRJ // was du suchst, steht in diesen zwei Tabellen
                       WHERE T_PRJ.Mandant_ID = '1' // Mandant_ID muss 1 sein.
                         AND T_KND.Mandant_ID = T_PRJ.Mandant_ID // Die Mandant_ID muss in beiden Tabellen identisch sein.
                         AND T_KND.Nummer = T_PRJ.KUNDENNummer // Die Kundenummern müssen ebenfalls übereinstimmen.
                       GROUP BY T_KND.Nummer, T_PRJ.PROJEKTSTATUS // Sollten Kunden mehrfach aufgeführt sein, fasse sie zusammen. Projektstatus?
                       HAVING COUNT(*) = 1) // kein Plan
GROUP BY T_KND.Nummer // Fasse Kunden zusammen, sofern sie mehrfach auftauchen.
ORDER BY T_KND.Nummer ASC // Sortiere aufsteigen nach Kundenummern.

Mir ist nicht klar, wofür die Abfrage in der Abfrage da ist. Sie gibt einem ja nur alle Kunden zurück.

 

[AlbertLast]

das innere select wird zu erst quasie ausgeführt und
ist quasi eine vorauswahl

 

[Devil24]

Das innere Select kann man auch einzeln ausführen.
Das sollte die Kunden selektieren, bei denen alle Projekte den gleichen Status haben.
Das Ergebnis des Inneren Select schränkt dann die Ergebnismenge noch weiter ein.

 

[Krik]

Ich habe das Ergebnis stichprobenartig getestet und leider gibt er Kunden aus, bei denen nicht alle Projekte abgeschlossen sind. Irgendwo ist da noch der Wurm drin.