[SQL] Complexe query

[Verwijderd]

Voor m'n werk ben ik bezig het gehele personeelsbestand in een database te plaatsen. Vanwege de beperkte mogelijkheden en een weigerachtige automatiseringsafdeling(ik ben daar niet aangenomen voor de IT, maar vanwege enige ervaring en gerelateerde studie ben ik gepromoveerd tot technicus) ben ik genoodzaakt dit in Access te doen (wat overigens best voldoet voor de situatie).

Nu heb ik de basis van database-ontwerp (normalisatie, SQL etc.) redelijk onder de knie. Echter, ik loop nu tegen een query aan die ik nog niet de baas ben.

Wat is het probleem:

Het bedrijf waar het om gaat is een call-center. De medewerkers (Agents) worden om de zoveel tijd beoordeeld. De gegevens van de Agents worden opgeslagen in de tabel Agent. Voor het gemak beperken we die gegevens tot Voornaam en Achternaam De primaire sleutel is AgentID. Beoordelingen worden opgeslagen in de tabel Beoordelingen (verassend ) met als primaire sleutel BeoordelingID. Verder staat er in die tabel Cijfer, Datum en heel belangrijk: AgentID.

Voor de bedrijfsvoering is het belangrijk dat we weten wanneer de laatste beoordeling van een Agent geweest is. In de tabel Beoordelingen staan dus verschillende beoordelingen met een identiek AgentID en verschillende Datum. Deze laatste beoordelingen per agent moeten in een lijst komen te staan voor alle agents, met de minst recente beoordeling bovenaan. Deze moet immers het snelst weer beoordeeld worden.

De query moet dus de voornaam en de achternaam van de Agents, en de datum van de laatste beoordeling geven.

Zoals:

code:

Pietje Pietersen   12-3-01
Frits Arends     15-5-01

De volgende query had ik daarvoor bedacht:

code:

SELECT a.Achternaam, a.Voornaam, max(b.Datum)
FROM Agent a, Beoordelingen b
WHERE a.AgentID = b.AgentID
GROUP BY a.Achternaam, a.Voornaam, b.Datum
SORT BY b.Datum ASC
;

Dit werkt echter niet, nu krijg ik nog steeds meerdere data voor een agent. Toevoegen van keyword DISTINCT brengt ook geen verlichting...

Wie o wie helpt me????

[Dash2in1]

niet groeperen op elke datum?

[Verwijderd]

code:

SELECT
   a.Achternaam,
   a.Voornaam,
   b.Datum
  FROM
   Agent a,
   Beoordelingen b
  WHERE
   b.BeoordelingID IN
   (
     SELECT
      BeoordelingID,
      MAX(datum)
     FROM
      Beoordelingen
     GROUP BY
        BeoordelingID
   )
  GROUP BY...
  SORT BY....
;

Zo misschien?? (let op de subquery)

[Verwijderd]

nee, niet zo.
het moet in ieder geval op zijn minst group by agentID zijn (in die subquery dus)

BeoordelingID is nml. PK, dus groupen daarop heeft weinig zin aangezien alle waardes uniek zijn.

[edit3]
edit 1 en 2 weggehaald, wegens fouten

[Verwijderd]

Op vrijdag 03 mei 2002 23:07 schreef deur het volgende:
nee, niet zo.
het moet in ieder geval op zijn minst group by agentID zijn (in die subquery dus)

BeoordelingID is nml. PK, dus groupen daarop heeft weinig zin aangezien alle waardes uniek zijn

Idd, je hebt helemaal gelijk... met agentID zou t wel moeten...

code:

   CREATE TABLE test
   (
    a SMALLINT,
    b SMALLINT
   );
   INSERT INTO test(a,b) VALUES (1,1);
   INSERT INTO test(a,b) VALUES (1,3);
   INSERT INTO test(a,b) VALUES (2,3);
   INSERT INTO test(a,b) VALUES (2,5);
   SELECT a, MAX(b) FROM TEST GROUP BY a;

Resultaat:

code:

 a | max
---+-----
 1 |   3
 2 |   5

[EDIT]
En nu ik begin te ontwaken, zie ik dat mijn leuke stukje code sowieso niet gaat werken als er per dag meerdere beoordelingen worden gemaakt.

[Verwijderd]

dit werkt bij mij in access 2000

code:

SELECT
   a.Achternaam,
   a.Voornaam,
   max(b.Datum)
  FROM
   Agent a,
   Beoordelingen b
  WHERE
   b.agentID=a.AgentID
  group by
   a.Achternaam,
   a.Voornaam,
   b.agentID

[Verwijderd]

Op vrijdag 03 mei 2002 22:56 schreef Dash2in1 het volgende:
niet groeperen op elke datum?

Als ik niet groepeer op datum kan ik niet sorteren op datum (AFAIK) en dat is wel essentieel in dit opzicht.

[Verwijderd]

je wil de lijst ook nog gesorteerd hebben?

[Verwijderd]

Op vrijdag 03 mei 2002 23:23 schreef deur het volgende:
dit werkt bij mij in access 2000

code:

SELECT
   a.Achternaam,
   a.Voornaam,
   max(b.Datum)
  FROM
   Agent a,
   Beoordelingen b
  WHERE
   b.agentID=a.AgentID
  group by
   a.Achternaam,
   a.Voornaam,
   b.agentID

Hmm ok. Jij groepeert dus niet op datum maar op AgentID. Zoals ik hierboven al zei, is het dan nog mogelijk om te sorteren op Datum? Kan natuurlijk ook nog een query maken die deze query aanroept en dan sorteert, dus is dat trouwens van latere zorg.

Zit nu trouwens thuis, en kan het niet uitproberen aangezien ik m'n werk zomin mogelijk mee naar huis probeer te nemen! (oeps.. mislukt! )

Op vrijdag 03 mei 2002 23:26 schreef deur het volgende:
je wil de lijst ook nog gesorteerd hebben?

Op vrijdag 03 mei 2002 22:22 schreef DaJaN het volgende:

code:

SELECT a.Achternaam, a.Voornaam, max(b.Datum)
FROM Agent a, Beoordelingen b
WHERE a.AgentID = b.AgentID
GROUP BY a.Achternaam, a.Voornaam, b.Datum
SORT BY b.Datum ASC <----
;

Yep!

[Verwijderd]

ik dacht hij alle Beoordelingen liet zien in zijn query, en de nieuwste bovenaan

deze regel toevoegen:

code:

order by max(b.datum) asc

[edit]

SORT BY b.Datum ASC

huh?

[Verwijderd]

Op vrijdag 03 mei 2002 23:34 schreef deur het volgende:


huh?

Oeps! Voor mij is het ook al een lange dag... Tuurlijk moet het ORDER BY zijn. In de Query heb ik dat overigens wel goed gedaan.

/me is een beetje

Ga het overigens morgenvroeg meteen proberen. ZOu geweldig zijn als dit het wel doet.

Wordt namelijk helemaal gek van de personeelsbestanden op m'n werk. Ze zijn er begonnen om alle data in Excel werkbladen bij te houden. Adresgegevens, beoordelingsgegeven, ziektegegevens, planningen etc allemaal op andere werkbladen en niets gekoppeld. Nog nooit zo redundantie gezien!

Met 6 werknemers is dat nog wel te behappen, maar inmiddels werken er 250 en gaat er regelmatig iets mis. 3 weken geleden was voor mij de grens bereikt en ben ik maar met Access aan de slag gegaan!

[Verwijderd]

Op vrijdag 03 mei 2002 23:23 schreef deur het volgende:
dit werkt bij mij in access 2000

code:

SELECT
   a.Achternaam,
   a.Voornaam,
   max(b.Datum)
  FROM
   Agent a,
   Beoordelingen b
  WHERE
   b.agentID=a.AgentID
  group by
   a.Achternaam,
   a.Voornaam,
   b.agentID

Tried and failed...

Probleem bij deze query is dat een bepaalde agent nog steeds vaker dan een keer voorkomt. Een agent wordt meerdere malen beoordeeld, wat ik wil is dat de meest recente beoordeling wordt weergegeven voor elke agent die beoordeeld is.

[Dash2in1]

klopt je tabel dan wel met voornaam en achternaam?

[Verwijderd]

werkt LIMIT 1 alleen in MySQL?

[The - DDD]

Wel wakker blijven mensen:

code:

SELECT
   a.Achternaam,
   a.Voornaam,
   max(b.Datum)
  FROM
   Agent a,
   Beoordelingen b
  WHERE
   b.agentID=a.AgentID
  group by
   a.Achternaam,
   a.Voornaam,
   b.agentID

Die group by...

Wie is er zo dom om daarin op achternaam, dan op voornaam en dan op agentID te groupen?

Wat als je jan jansen en bas jansen hebt? Is dus niet goed.

Oplossing? Die agentID als eerste in je group by clause zetten. Zodoende wordt er op elke unieke agent gegrouped, vanwege de join zijn er namelijk meerdere gelijke agentID's.

Door de max worden van de group by alleen de korts geleden beoordelings data weergegeven. En dat wil je.

Verder, laat die voor en achternaam in je group by staan, anders kan je die niet weergeven middels je select.

1 probleem met deze query... IEdereen die nog nooit een beoordeling heeft gehad, zal niet te zien zijn.

[Verwijderd]

Op zaterdag 04 mei 2002 19:25 schreef The - DDD het volgende:
Wel wakker blijven mensen:

code:

SELECT
   a.Achternaam,
   a.Voornaam,
   max(b.Datum)
  FROM
   Agent a,
   Beoordelingen b
  WHERE
   b.agentID=a.AgentID
  group by
   a.Achternaam,
   a.Voornaam,
   b.agentID

Die group by...

Wie is er zo dom om daarin op achternaam, dan op voornaam en dan op agentID te groupen?

Wat als je jan jansen en bas jansen hebt? Is dus niet goed.

Oplossing? Die agentID als eerste in je group by clause zetten. Zodoende wordt er op elke unieke agent gegrouped, vanwege de join zijn er namelijk meerdere gelijke agentID's.

Door de max worden van de group by alleen de korts geleden beoordelings data weergegeven. En dat wil je.

Verder, laat die voor en achternaam in je group by staan, anders kan je die niet weergeven middels je select.

1 probleem met deze query... IEdereen die nog nooit een beoordeling heeft gehad, zal niet te zien zijn.

SELECT
a.Achternaam,
a.Voornaam,
b.Datum
FROM
Agent a,
Beoordelingen b
WHERE
b.agentID=a.AgentID
and b.datum=(select max(datum) from beoordelingen where a.agentid=agentid)

[Verwijderd]

Op zaterdag 04 mei 2002 19:25 schreef The - DDD het volgende:
Wel wakker blijven mensen:

code:

SELECT
   a.Achternaam,
   a.Voornaam,
   max(b.Datum)
  FROM
   Agent a,
   Beoordelingen b
  WHERE
   b.agentID=a.AgentID
  group by
   a.Achternaam,
   a.Voornaam,
   b.agentID

Die group by...

Wie is er zo dom om daarin op achternaam, dan op voornaam en dan op agentID te groupen?

Wat als je jan jansen en bas jansen hebt? Is dus niet goed.

Oplossing? Die agentID als eerste in je group by clause zetten. Zodoende wordt er op elke unieke agent gegrouped, vanwege de join zijn er namelijk meerdere gelijke agentID's.

Door de max worden van de group by alleen de korts geleden beoordelings data weergegeven. En dat wil je.

Verder, laat die voor en achternaam in je group by staan, anders kan je die niet weergeven middels je select.

1 probleem met deze query... IEdereen die nog nooit een beoordeling heeft gehad, zal niet te zien zijn.

OK, het GROUP BY eerst op AgentID klinkt zeer aannemelijk. Ik hoop echter wel dat er dan voor elke agentID ook maar een datum weergegeven wordt, dat was tot nu namelijk de bottleneck. De agents die nog nooit beoordeeld zijn daar kom ik wel uit denk ik zo.

Jullie horen het nog!

[Verwijderd]

Op zondag 05 mei 2002 12:03 schreef rara het volgende:
SELECT
a.Achternaam,
a.Voornaam,
b.Datum
FROM
Agent a,
Beoordelingen b
WHERE
b.agentID=a.AgentID
and b.datum=(select max(datum) from beoordelingen where a.agentid=agentid)

Hmm, in die subquery, mag je daar a.AgentID wel gelijkstellen aan AgentID? Aangezien de subquery niets "weet" van een tabel Agent. In het FROM statement is namelijk geen Agent a gegeven. Ziet er ook wel aannemelijk uit deze oplossing! Ga ik ook proberen.

[Verwijderd]

Ok, met goede hulp van jullie de oplossing gevonden , dus die plaats ik hier ook maar even:

code:

SELECT a.Achternaam, a.Voornaam, max(b.Datum)
FROM Agent a, Beoordelingen b
WHERE a.AgentID = b.AgentID
GROUP BY a.AgentID, a.Achternaam, a.Voornaam
ORDER BY max(b.Datum)
;