[mysql 3.23] eerstkomende data waarop iemand jarig zal zijn

[BetuweKees]

Hoi,

Ik gebruik onderstaand stukje code om verjaardagen te kunnen uitvragen uit een leden database. Dit scriptje werkt prima, maar toen ik zojuist zag dat het een lege output teruggaf omdat er de komende dagen niemand jarig is, realiseerde ik me dat het veel netter zou zijn om weer te geven op welke volgende drie dagen (die dus niet per se op een volgend zijn) er iemand jarig is, in plaats van de verjaardagen voor vandaag, morgen en overmorgen weet te geven zoals nu het geval is.

In eerste instantie lijkt mij dit iets wat je kan doen doormiddel van een GROUP BY op sort_date icm een LIMIT 3 oid, echter het probleem is dan dat er slechts een jarige per dag weergegeven wordt. Ik zou natuurlijk mbv een stukje programeren een loopje kunnen maken, maar netter is het om alles in een query te houden.

Hoe kan ik mijn query zodanig aanpassen dat ik dit kan doen, of loop ik hier gewoon weer tegen een tekortkoming van MySQL aan mbt subqueries ed?

code:

 SELECT 
    leden.leden_id,
    CONCAT_WS(' ', leden.voornaam, leden.tussen, leden.achternaam) AS naam, 
    DATE_FORMAT(leden.geboortedatum, '%d.%m') AS date, 
    DATE_ADD(
        leden.geboortedatum, 
        INTERVAL (
            // bereken hoe oud lid op volgende verjaardag is..
            YEAR(CURRENT_DATE()) 
            - YEAR(leden.geboortedatum) 
            - (RIGHT(CURRENT_DATE(), 5) <= RIGHT(leden.geboortedatum, 5)) 
            + 1
        ) YEAR
        // .. en tel dit op bij geboortedatum om te kunnen sorteren
    ) AS sort_date 
 FROM 
    leden 
 WHERE 
    leden.lid = 'ja' 
 HAVING 
    // verjaardagen voor vandaag, morgen en overmorgen
    sort_date 
        BETWEEN 
            CURRENT_DATE() 
        AND 
            DATE_ADD(
                CURRENT_DATE(), 
                INTERVAL 2 DAY
            ) 
 ORDER BY 
    sort_date ASC, 
    naam ASC;

[beetle71]

Volgens mij moet je gewoon zoiets doen:

SQL:

SELECT 
     DATE_FORMAT(leden.geboortedatum, '%d.%m') AS date,
     CONCAT_WS(' ', leden.voornaam, leden.tussen, leden.achternaam) AS naam,
     (DAYOFYEAR(leden.geboortedatum)-DAYOFYEAR(now())) as verschil
     /* ,etc. */
FROM 
     leden 
WHERE  
     (DAYOFYEAR(leden.geboortedatum)-DAYOFYEAR(now()))>0 
ORDER BY
     verschil
LIMIT 3

[BetuweKees]

beetle71 schreef op 27 juli 2004 @ 16:10:
Volgens mij moet je gewoon zoiets doen:

volgens mij werkt dat niet want:
- overgang van 31 dec naar 1 jan gaat dan mis
- je krijgt nog steeds maar maximaal 3 personen per query naar buiten; dan klopt het script dus niet meer als er meerdere mensen op de zelfde dag jarig zijn.

[ekoopman]

kleine opmerking, DAYOFYEAR() gaat mis met schrikkeljaren enzo . Dayofyear(31-12-2000)!=Dayofyear(31-12-2001) bijvoorbeeld. Wel even goed op letten dus.

[beetle71]

BetuweKees schreef op 27 juli 2004 @ 16:36:
[...]


volgens mij werkt dat niet want:
- overgang van 31 dec naar 1 jan gaat dan mis
- je krijgt nog steeds maar maximaal 3 personen per query naar buiten; dan klopt het script dus niet meer als er meerdere mensen op de zelfde dag jarig zijn.

Okee, vwb het eerste heb je gelijk, wat je dan kunt doen is 366 optellen (we houden ook rekening met schrikkeljaren ;-) ) bij het verschil indien dat negatief is.

SQL:

SELECT 
     DATE_FORMAT(leden.geboortedatum, '%d.%m') AS date,
     CONCAT_WS(' ', leden.voornaam, leden.tussen, leden.achternaam) AS naam,
     if(   
           DAYOFYEAR(leden.geboortedatum)-DAYOFYEAR(now())<0,
           DAYOFYEAR(leden.geboortedatum)-DAYOFYEAR(now())+366,
           DAYOFYEAR(leden.geboortedatum)-DAYOFYEAR(now())

     ) as verschil
     /* ,etc. */
FROM 
     leden 
ORDER BY
     verschil
LIMIT 3

Voor wat betreft het laten zien van de komende drie dagen met 1 of meerdere verjaardagen.. Tsja, je zou de tabel kunnen groupen op het verschil, maar dan heb je er geen namen bij en die kun je er niet tegen joinen, dat gaat alleen lukken met een subquery omdat je de limit 3 nodig hebt om je tot 3 dagen te beperken.

Je kunt dan beter de hele recordset ophalen en dat in php (?) fixen.

Of je beperken tot de eerstvolgende x-jarigen

[ Voor 24% gewijzigd door beetle71 op 27-07-2004 17:33 ]

[BetuweKees]

beetle71 schreef op 27 juli 2004 @ 17:29:
[...]


Okee, vwb het eerste heb je gelijk, wat je dan kunt doen is 366 optellen (we houden ook rekening met schrikkeljaren ;-) ) bij het verschil indien dat negatief is.

dat klopt..
je gaat echter voorbij aan het feit dat dit totaal niet de strekking van mijn vraag was, aangezien ik geen problemen had met een goed overzicht te maken van wie er wanneer jarig was. het komt er dus op neer dat je nu bezig bent een oplossing te verzinnen voor een probleem dat niet bestaat. verder wel aardig van je maar niet echt nuttig dus

Voor wat betreft het laten zien van de komende drie dagen met 1 of meerdere verjaardagen.. Tsja, je zou de tabel kunnen groupen op het verschil, maar dan heb je er geen namen bij en die kun je er niet tegen joinen, dat gaat alleen lukken met een subquery omdat je de limit 3 nodig hebt om je tot 3 dagen te beperken.

Je kunt dan beter de hele recordset ophalen en dat in php (?) fixen.

Of je beperken tot de eerstvolgende x-jarigen

hier komen we idd bij de kern, dit was waar mijn vraag over ging inderdaad.
al het bovenstaande had ik echter zelf ook al geconcludeerd in mijn TS, precies zoals jij het zegt:
- GROUP BY kan, maar dan wordt er dus maximaal een jarige per dag weergegeven
- eerste x-aantal jarigen is vrij evil
- PHP oplossing is mogelijk maar niet echt 'mooi'

is er nog iemand anders die evt suggesties heeft voor een complete sql oplossing voor dit probleem (behalve migratie naar 4.1 natuurlijk) of zal ik toch echt moeten gaan scripten en een stukje php code moeten toevoegen?

[P_de_B]

Ik ken het SQL Dialect van MYSQL niet goed genoeg, maar in T-SQL zal het zo zijn:

code:

SELECT TOP 3 * FROM Tabel WHERE VerjaardagDatum > GetDate() ORDER By VerjaardagDatum

GetDate() geeft de datum van vandaag, terwijl TOP 3 het equivalent van Limit is.

edit: hmmzzz, je wilt niet de volgende 3 verjaardagen, maar dagen. Ik moet leren lezen

[ Voor 16% gewijzigd door P_de_B op 27-07-2004 18:51 ]

[Verwijderd]

P_de_B schreef op 27 juli 2004 @ 18:49:
Ik ken het SQL Dialect van MYSQL niet goed genoeg, maar in T-SQL zal het zo zijn:

code:

SELECT TOP 3 * FROM Tabel WHERE VerjaardagDatum > GetDate() ORDER By VerjaardagDatum

GetDate() geeft de datum van vandaag, terwijl TOP 3 het equivalent van Limit is.

Dit geeft als het goed is nooit enig resultaat terug.

[ Voor 11% gewijzigd door Verwijderd op 27-07-2004 21:22 ]

[Verwijderd]

pseudo

code:

 SELECT achternaam
 , leden_id
 , (VervangJaardoorHuidigeJaar(geboortedatum)) AS datum
 FROM leden
WHERE (geboortedatum IS NOT NULL)
AND (leden.lid='ja')
AND (VervangJaardoorHuidigeJaar(geboortedatum) >= GETDATE())
UNION ALL
 SELECT achternaam
 , leden_id
 , (VervangJaardoorHuidigeJaar+1(geboortedatum)) AS datum
 WHERE (geboortedatum IS NOT NULL)
 AND (leden.lid='ja')
 ORDER BY datum ASC

Eventuele tijdwaarden uit datum strippen.
De eerste records uit het resultaat moet je zelf ff filteren d.m.v. loopje ofzo.

[SuperRembo]

• Bepaal de leeftijd in dagen
• Bepaal daarmee de leeftijd in jaren, naar boven afronden
• Tel de leeftijd op bij de geboortedatum, dan heb je de eerstvolgende datum waarop iemand jarig is

T-SQL, getest (SqlServer, Northwind database)

SQL:

SELECT
    FirstName, 
    LastName, 
    BirthDate, 
    DATEADD(year, CEILING((DATEDIFF(d, BirthDate, GETDATE())-1)/365.25), BirthDate) AS NextBirthDate
FROM Employees
ORDER BY NextBirthDate

In MySql, niet getest:

SQL:

DATE_ADD(BirthDate, INTERVAL CEILING(( TO_DAYS(CURDATE()) - TODAYS(BirthDate) - 1 ) / 365.25) YEAR)

[beetle71]

Okee, zowel ikzelf als superRembo en Sinaasappelsap hebben het probleem niet (direct) begrepen.

Wat jij wil gaat niet met mysql<4.1. dat wil zeggen, niet in 1 query.
Het probleem zit 'm in het feit dat je je query wilt 'limit'-en op 3 dagen, maar vervolgens wil je voor elk van die drie dagen de jarige of jarigen(!) hebben, en dat levert dus mogelijkerwijs meer dat 3 rijen op.
Met een subquery zou dat wel kunnen.

Maar in dit geval heb je die mogelijkheid dus niet. Dus volgens mij is de enige oplossing (los van updaten naar 4.1 of oracle of zo ) om twee queries te gebruiken.
In de eerste zoek je de drie komende verjaardag-dagen op, en in de tweede query gebruik je de resultaten (lees datums) uit de eerste query om de jarigen voor die drie dagen te vinden.

[SuperRembo]

Ok, ik snap het (al).
Dan lijkt mij de simpelste oplossing om een select te doen gesorteerd op eerstevolgende verjaardag. Bij het weergeven hou je bij hoeveel unieke datums je het gehad, en na 3 houd je op.

[SuperRembo]

Maar zonder sub-query kan het ook. In T-SQL:

SQL:

SELECT 
    e1.EmployeeID, 
    e1.FirstName, 
    e1.LastName, 
    e1.BirthDate, 
    DATEADD(yyyy, CEILING((DATEDIFF(d, e1.BirthDate, GETDATE()) - 1) / 365.25), e1.BirthDate) AS NextBirthDay
FROM Employees e1 
LEFT OUTER JOIN Employees e2 
    ON DATEADD(yyyy, CEILING((DATEDIFF(d, e1.BirthDate, GETDATE()) - 1) / 365.25), e1.BirthDate) > 
        DATEADD(yyyy, CEILING((DATEDIFF(d, e2.BirthDate, GETDATE()) - 1) / 365.25), e2.BirthDate)
GROUP BY 
    e1.EmployeeID, 
    e1.FirstName, 
    e1.LastName, 
    e1.BirthDate, 
    DATEADD(yyyy, CEILING((DATEDIFF(d, e1.BirthDate, GETDATE()) - 1) / 365.25), e1.BirthDate)
HAVING 
    COUNT(DISTINCT DATEADD(yyyy, CEILING((DATEDIFF(d, e2.BirthDate, GETDATE()) - 1) / 365.25), e2.BirthDate)) < 3
ORDER BY 
    DATEADD(yyyy, CEILING((DATEDIFF(d, e1.BirthDate, GETDATE()) - 1) / 365.25), e1.BirthDate)

Maar echt overzichtelijker wordt het er niet van, zeker als je nog extra voorwaarden toe gaat voegen als leden.lid = 'ja'.

[BetuweKees]

ok heb toch besloten eea op te lossen door gebruik te maken van twee losse queries; eerst de data uitvragen, en dan de namen erbij. werkt prima, en tot mijn verbazing nog praktisch zonder performance verlies ook

toch zou ik het tof vinden als die mysql 4.1 nou eindelijk eens uit gaat komen..

[Verwijderd]

beetle71 schreef op 28 juli 2004 @ 10:04:
Okee, zowel ikzelf als superRembo en Sinaasappelsap hebben het probleem niet (direct) begrepen.

Hé, laat mij er buiten.

SuperRembo schreef op 28 juli 2004 @ 10:46:
Ok, ik snap het (al).
Dan lijkt mij de simpelste oplossing om een select te doen gesorteerd op eerstevolgende verjaardag. Bij het weergeven hou je bij hoeveel unieke datums je het gehad, en na 3 houd je op.

Dit is precies wat ik eerder bedoelde en opschreef...

[Olaf van der Spek]

SuperRembo schreef op 28 juli 2004 @ 10:46:
Ok, ik snap het (al).
Dan lijkt mij de simpelste oplossing om een select te doen gesorteerd op eerstevolgende verjaardag. Bij het weergeven hou je bij hoeveel unieke datums je het gehad, en na 3 houd je op.

Zonder limit?

[SuperRembo]

OlafvdSpek schreef op 28 juli 2004 @ 20:04:
[...]
Zonder limit?

Ja, zonder limit. In PHP (of wat je ook gebruikt om de boel te presenteren) bepaal je hoeveel je laat zien. Niet de mooiste oplossing, wel een simpele.

[Annie]

Verwijderd schreef op 27 juli 2004 @ 21:22:
[...]


Dit geeft als het goed is nooit enig resultaat terug.

offtopic:
verjaardagdatum <> geboortedatum
er zullen dus wel resultaten terugkomen

maar het was dus niet helemaal wat de TS bedoelde.