[mssql]limiteren van subquery lukt niet

[FaLc0n]

Hallo allemaal,

Ik wil een rapport draaien uit een personeelsinformatiesysteem (MSSQL) maar kom er niet helemaal uit

De bedoeling is om een overzicht te krijgen van alle medewerkers die deze maand 1 jaar (of 2,3,4...) niet ziek zijn geweest. Dus mensen waarvan de laatste hersteldatum in de database de maand april bevat. Het probleem is nu dat ik per medewerker een totaaloverzicht krijg van alle herstelmeldingen, dit moet gelimiteerd worden op de laatste hersteldatum waar de maand gelijk is aan de huidige maand en de datum minimaal een jaar terug is (geen mensen die dus deze maand nog hersteld zijn). Volgens mij kan dit dan met een subquery alleen loop ik hier op vast. Hopelijk weet iemand een oplossing, alvast bedankt voor de moeite!

De tabellen zien er (eenvoudige weergave met relevante velden) als volgt uit:

medewerkers
- mid
- naam

verzuimmeldingen
- vid
- mid
- datum_herstel

[ Voor 11% gewijzigd door FaLc0n op 06-04-2007 13:31 ]

[Robtimus]

Even uit de losse pols:

SQL:

SELECT *
FROM medewerkers
WHERE mid IN
(
    SELECT mid
    FROM verzuimmeldingen
    GROUP BY mid
    HAVING DATEDIFF(m, MAX(datum_herstel), GETDATE()) = 12
)

DATEDIFF is een grote vriend van je, je kunt kijken naar verschillen in dagen (d), maanden (m), jaren (j of y), secondes, noem het maar op.

[Creepy]

Dit is meer een vraag voor PRG.
Daarnaast is het natuurlijk niet de bedoeling dat je verteld wat je wilt hebben en dat wij het voor je gaan maken. Ik neem aan dat je zelf ook al wat queries hebt geprobeerd?

[FaLc0n]

Creepy schreef op zaterdag 07 april 2007 @ 11:39:
Dit is meer een vraag voor PRG.
Daarnaast is het natuurlijk niet de bedoeling dat je verteld wat je wilt hebben en dat wij het voor je gaan maken. Ik neem aan dat je zelf ook al wat queries hebt geprobeerd?

Ik heb wel degelijk van alles geprobeerd maar kwam er niet uit... subquery had ik gericht op de datum ipv medewerker id .Niet zo flauw doen joh

IceManX, ontzettend bedankt voor je hulp, hier kom ik denk ik een stuk verder mee!

[ Voor 15% gewijzigd door FaLc0n op 07-04-2007 17:22 ]

[Pete]

Waarom een subquery?

als ik bijv denk aan:

SQL:

SELECT m.naam, (YEAR(NOW())-YEAR(MAX(v.datum_herstel))) AS aantaljaar
FROM  medewerkers m JOIN verzuimmeldingen v ON (m.mid = v.mid)
GROUP BY m.mid
HAVING MONTH(MAX(v.datum_herstel)) = MONTH(NOW())

volgens mij doet dit precies wat jij zoekt

(dit is gewoon een kwestie van de MySQL handleiding lezen, effe nadenken en wat proberen) wat niet impliceert dat deze oplossing door mij getest is

@TS: Creepy wil graag dat ff de queries laat zien die je geprobeert hebt

[ Voor 18% gewijzigd door Pete op 07-04-2007 19:17 . Reden: ff netjes de keywords geuppercased (waarom doet die parser dat niet voor mij :+ (lui)) ]

[Verwijderd]

IceManX en phsmit, jullie houden geen rekening met medewerkers die nog nooit ziek zijn geweest...

SQL:

SELECT * FROM medewerkers
WHERE NOT EXISTS
(SELECT mid FROM verzuimmeldingen
 WHERE mid = medewerkers.mid 
 AND datum_herstel > DATEADD(y, GETDATE(), -1))
/* AND datum_in_dienst <= DATEADD(y, GETDATE(), -1) */

[FaLc0n]

Bedankt voor de reacties, geweldig! Phsmit; bedankt voor de code, dat is inderdaad exact wat ik voor elkaar probeer te krijgen. Ik heb het inmiddels getest en het werkt prima De query houdt inderdaad geen rekening met mensen die nog nooit ziek zijn geweest maar dat fix ik wel (bedankt voor de info Afterlife).

Creepy: Ik zal in het vervolg voor de duidelijkheid ook aantonen wat ik zelf heb geprobeerd in de vorm van een stukje code.

EDIT:
Ik heb een query gemaakt voor de mensen die nog nooit ziek zijn geweest, is deze ook samen te voegen met de query van Phsmit?

SQL:

SELECT naam, (YEAR( NOW( ) ) - YEAR( MAX( medewerkers.datum_indienst ) ) ) AS aantaljaar
FROM medewerkers, verzuimmeldingen
WHERE medewerkers.mid NOT IN (
SELECT mid
FROM verzuimmeldingen
)
GROUP BY medewerkers.mid
HAVING MONTH( MAX( medewerkers.datum_indienst ) ) = MONTH( NOW( ) ) 

[ Voor 33% gewijzigd door FaLc0n op 08-04-2007 17:32 ]

[Verwijderd]

Kijk nog 's naar mijn query? Die houdt wel rekening met de supergezonde medewerkers...

[Pete]

Verwijderd schreef op zondag 08 april 2007 @ 14:26:
IceManX en phsmit, jullie houden geen rekening met medewerkers die nog nooit ziek zijn geweest...

Niet? Een medewerker die nog nooit is ziek geweest heeft geen record in de verzuimmeldingen tabel. Dus de inner join zorgt dat in dat product de medewerkers die nooit ziek geweest zijn al niet meegenomen worden. Mijn query werkt dus volledig zoals ie er staat.

Verwijderd schreef op zondag 08 april 2007 @ 20:27:
Kijk nog 's naar mijn query? Die houdt wel rekening met de supergezonde medewerkers...

Dat geld dus ook voor die van mij.

[ Voor 20% gewijzigd door Pete op 08-04-2007 21:52 ]

[Robtimus]

Bij mij was dat expres, want de TS vroeg om

Dus mensen waarvan de laatste hersteldatum in de database de maand april bevat.

[FaLc0n]

Het komt in mijn verhaal niet duidelijk naar voren (excuus), maar iemand die nog nooit ziek is geweest zou ook in het rapport moeten verschijnen wanneer hij/zij in de betreffende maand x jaar niet ziek is geweest. Hier wordt dan gekeken naar de datum in dienst (medewerkerstabel) i.p.v. hersteldatum. Dit heb ik al verwerkt in een query die ik heb gemaakt op basis van jullie input, ik krijg het alleen nog niet goed voor elkaar om deze samen te voegen met de andere query waarbij alleen rekening wordt gehouden met mensen die al wél een keer ziek zijn geweest.

IceManX schreef op zondag 08 april 2007 @ 22:36:
Bij mij was dat expres, want de TS vroeg om

[...]

[Pete]

Als je zorgt dat je een query maakt die dezelfde velden heeft kun je deze met union er gewoon aan vastplakken.

bijv.

SQL:

 SELECT m.naam, (YEAR(NOW())-YEAR(MAX(v.datum_herstel))) AS aantaljaar
FROM  medewerkers m JOIN verzuimmeldingen v ON (m.mid = v.mid)
GROUP BY m.mid
HAVING MONTH(MAX(v.datum_herstel)) = MONTH(NOW())
UNION
SELECT m.naam, (YEAR(NOW())-YEAR(m.datum_indienst)) AS aantaljaar
FROM medewerkers m LEFT OUTER JOIN verzuimmeldingen v on (m.mid = v.mid)
WHERE v.mid IS NULL AND MONTH(NOW()) = MONTH(m.datum_indienst)

[FaLc0n]

Super! Hier moet het mee lukken, bedankt!

phsmit schreef op maandag 09 april 2007 @ 09:23:
Als je zorgt dat je een query maakt die dezelfde velden heeft kun je deze met union er gewoon aan vastplakken.

bijv.

SQL:

 SELECT m.naam, (YEAR(NOW())-YEAR(MAX(v.datum_herstel))) AS aantaljaar
FROM  medewerkers m JOIN verzuimmeldingen v ON (m.mid = v.mid)
GROUP BY m.mid
HAVING MONTH(MAX(v.datum_herstel)) = MONTH(NOW())
UNION
SELECT m.naam, (YEAR(NOW())-YEAR(m.datum_indienst)) AS aantaljaar
FROM medewerkers m LEFT OUTER JOIN verzuimmeldingen v on (m.mid = v.mid)
WHERE v.mid IS NULL AND MONTH(NOW()) = MONTH(m.datum_indienst)

[Annie]

Deze queries kunnen toch nooit werken in MSSQL? Je vraagt velden op die niet zijn meegenomen in een aggregate en ook niet in de group by.
Bedoel je soms al de hele tijd MySQL? Of zie ik het gewoon fout?

[Pete]

Ik weet niet hoe intelligent MSSQL is, maar m.mid is een primary key (en komt dus in de m tabel altijd maar 1 x voor). Voor de andere velden in m is er dus altijd maar 1 waarde die erbij past. Dus aggregratie is niet nodig. Anders zou er een bepaalde string-aggregratie moeten zijn die met 1 string als input deze ook als output geeft.

[Robtimus]

phsmit schreef op maandag 09 april 2007 @ 14:55:
Ik weet niet hoe intelligent MSSQL is, maar m.mid is een primary key (en komt dus in de m tabel altijd maar 1 x voor). Voor de andere velden in m is er dus altijd maar 1 waarde die erbij past. Dus aggregratie is niet nodig. Anders zou er een bepaalde string-aggregratie moeten zijn die met 1 string als input deze ook als output geeft.

Zo slim is MSSQL niet, en ik vind dat terecht. Het is en blijft incorrecte SQL als een veld wel zonder aggregatie in de SELECT staat maar niet in de GROUP BY. Dat MySQL dat wel pikt maakt voor mij MySQL meer de IE onder de RDBMs: als het niet correct is verzint ie er dingen bij.

[Pete]

En wat is daar dan de directe oplossing voor? Ik denk zelf dan aan m.naam de GROUP BY toevoegen (geeft het goede gewenste resultaat, maar het is zo 'dubbel')

Over het feit dat het incorrecte SQL is: Als het volgens de standaard niet mogelijk is ben ik het gewoon niet helemaal eens met de standaard

[FaLc0n]

Annie schreef op maandag 09 april 2007 @ 12:39:
Deze queries kunnen toch nooit werken in MSSQL? Je vraagt velden op die niet zijn meegenomen in een aggregate en ook niet in de group by.
Bedoel je soms al de hele tijd MySQL? Of zie ik het gewoon fout?

Ik bedoel wel degelijk MSSQL. Ik heb zelf beperkte ervaring met MySQL en heb de voorgestelde oplossingen ook op een MySQL server getest. Morgen kan ik de oplossing pas testen voor MSSQL aangezien ik dan pas weer bij de betreffende applicatie kan.

Hoe zou de query volgens jou wel correct werken voor MSSQL?

[ Voor 5% gewijzigd door FaLc0n op 09-04-2007 16:56 ]

[Robtimus]

Gewoon m.naam ook in de GROUP BY zetten.

[Pete]

Ok, dan ben ik blij dat ik zelf ook op die oplossing gekomen zou zijn (maar nog steeds vind ik het vreemd)

Dit word dus je totaaloplossing

SQL:

SELECT m.naam, (YEAR(NOW())-YEAR(MAX(v.datum_herstel))) AS aantaljaar
FROM  medewerkers m JOIN verzuimmeldingen v ON (m.mid = v.mid)
GROUP BY m.mid, m.naam
HAVING MONTH(MAX(v.datum_herstel)) = MONTH(NOW())
UNION
SELECT m.naam, (YEAR(NOW())-YEAR(m.datum_indienst)) AS aantaljaar
FROM medewerkers m LEFT OUTER JOIN verzuimmeldingen v on (m.mid = v.mid)
WHERE v.mid IS NULL AND MONTH(NOW()) = MONTH(m.datum_indienst)

[ Voor 3% gewijzigd door Pete op 09-04-2007 22:02 ]

[Annie]

phsmit schreef op maandag 09 april 2007 @ 22:02:

Als je UNION ALL gebruikt dan scheelt je dat een DISTINCT op de resultset.

Overigens denk ik dat deze query ook het gewenste resultaat geeft:

SQL:

SELECT m.naam, DATEDIFF(year, MAX(datum_herstel), GETDATE()) AS aantal_jaar
FROM medewerkers m
LEFT OUTER JOIN verzuimmeldingen v
  ON m.mid = v.mid
GROUP BY m.naam, m.datum_indienst
HAVING (MONTH(MAX(datum_herstel)) = MONTH(GETDATE())            -- laatste ziekmeldingmaand gelijk aan huidige maand
        AND DATEDIFF(year, MAX(datum_herstel), GETDATE()) >= 1) -- én minimaal 1 jaar terug
    OR (MAX(datum_herstel) IS NULL                              -- of gewoon nooit ziek geweest
        AND DATEDIFF(year, m.datum_indienst, GETDATE()) >= 1)   -- maar dan wel langer dan 1 jaar in dienst

Die HAVING (m.n. de laatste 2 regels) moet je even finetunen adhv je wensen.

[ Voor 12% gewijzigd door Annie op 09-04-2007 23:13 ]

[Pete]

Annie schreef op maandag 09 april 2007 @ 22:56:
[...]

Als je UNION ALL gebruikt dan scheelt je dat een DISTINCT op de resultset.

Overigens denk ik dat deze query ook het gewenste resultaat geeft:

SQL:

SELECT m.naam, DATEDIFF(year, MAX(datum_herstel), GETDATE()) AS aantal_jaar
FROM medewerkers m
LEFT OUTER JOIN verzuimmeldingen v
  ON m.mid = v.mid
GROUP BY m.naam, m.datum_indienst
HAVING (MONTH(MAX(datum_herstel)) = MONTH(GETDATE())            -- laatste ziekmeldingmaand gelijk aan huidige maand
        AND DATEDIFF(year, MAX(datum_herstel), GETDATE()) >= 1) -- én minimaal 1 jaar terug
    OR (MAX(datum_herstel) IS NULL                              -- of gewoon nooit ziek geweest
        AND DATEDIFF(year, m.datum_indienst, GETDATE()) >= 1)   -- maar dan wel langer dan 1 jaar in dienst

Die HAVING (m.n. de laatste 2 regels) moet je even finetunen adhv je wensen.

Ik zie bij mijn query geen DISTINCT staan, en die is volgens mij ook niet nodig (daarvoor is de 'IS NULL' clause in de 2de query). Bij jouw oplossing wordt echter niet weergegeven hoe lang een 'supergezonde' medewerker niet ziek is geweest. (het wordt niet weergegeven dat een medewerker bijv. 2 jaar in dienst is).

Daarnaast denk ik te zien aan de hoeveelheid functies die jij gebruikt (vooral in je having, waarvoor indexen aanmaken niet kan) dat mijn query optimaler werkt. Maar ja, dat is gevoel

[FaLc0n]

Bedankt! Ik ga het morgen direct testen en dan zie ik wel wat het beste werkt i.c.m. MSSQL. Ik update dit bericht nog met mijn bevindingen!

EDIT: Het is allemaal gelukt! Ik heb inderdaad naast het aantal jaren niet ziek ook het aantal jaren in dienst toegevoegd (voor de 'supergezonde' medewerker). Nogmaals bedankt allemaal

[ Voor 40% gewijzigd door FaLc0n op 10-04-2007 23:59 ]

[Annie]

phsmit schreef op maandag 09 april 2007 @ 23:42:
Ik zie bij mijn query geen DISTINCT staan, en die is volgens mij ook niet nodig (daarvoor is de 'IS NULL' clause in de 2de query). Bij jouw oplossing wordt echter niet weergegeven hoe lang een 'supergezonde' medewerker niet ziek is geweest. (het wordt niet weergegeven dat een medewerker bijv. 2 jaar in dienst is).

Ik zeg ook niet dat je een DISTINCT in je query hebt staan Maar het standaard gedrag van een UNION is dat (eventuele) dubbele rijen worden verwijderd (lees: DISTINCT). Vandaar mijn opmerking over de UNION ALL.

Het weergeven van de datum in dienst is er natuurlijk zo bij te klussen. Dat is geen probleem.

Daarnaast denk ik te zien aan de hoeveelheid functies die jij gebruikt (vooral in je having, waarvoor indexen aanmaken niet kan) dat mijn query optimaler werkt. Maar ja, dat is gevoel

Ow, dat zou best wel eens kunnen. Ik zeg ook nergens dat mijn query sneller is. Gewoon een kwestie van testen op je data(base).