[SQL] Correlated subquery

[sjaakie]

Hallo!

Ik kom even niet uit een query en wellicht kan iemand een nieuw inzicht geven. We hebben een tijdregistratie (soort prikklok) systeem en we willen daar een lijst met alle werknemers uit halen met hun laatste "prik-actie". Even een vereenvoudigde weergave van het datamodel:

Employee
- EmployeeID
- Name

Clocking
- EmployeeID
- Date
- Time
- ....

Waarom de bouwer date en time gesplitst heeft is mij even een raadsel maar dat maakt verder ook niet uit.

Het doel is dus een lijst met alle medewerkers en hun datum + tijd van de laatste clock actie. Ik heb later nog meer gegevens nodig uit Clocking, maar dat komt dan wel. Het gaat overigens om een SQL Server database.

Wie kan me helpen?

[RobIII]

sjaakie schreef op woensdag 24 augustus 2011 @ 14:42:
Wie kan me helpen?

Laten we beginnen met Kan iemand even...? en onze Quickstart. Zoals je in die laatste zult lezen verwachten we namelijk wat eigen inzet en zien we dus graag wat je zelf al hebt geprobeerd/gezocht/gevonden etc. En daar zie ik niets van terug. En dat brengt je topic meteen weer bij de eerste link.

Je zult eens moeten gaan kijken naar Aggregate functies en Hoe werkt dat GROUP BY nu eigenlijk? mocht je dat nog niet gedaan hebben.

[ Voor 20% gewijzigd door RobIII op 24-08-2011 14:44 ]

[Motrax]

sjaakie schreef op woensdag 24 augustus 2011 @ 14:42:
Waarom de bouwer date en time gesplitst heeft is mij even een raadsel maar dat maakt verder ook niet uit.

Sommige databases kennen alleen een datum veld, zonder tijd en een los tijd veld. Bijvoorbeeld DB2. Ook voor performance redenen kan het beter zijn om datum en tijd apart op te slaan, als je veel vaker queriet op datum dan op datumtijd. Als ontwikkelaar vind ik wel dat mijn leven moeilijker wordt gemaakt als datum en tijd los van elkaar wordt opgeslagen

Datum en tijd bij elkaar voegen is niet zo spannend, even googlen:

SQL:

to_date(TO_CHAR(Date, 'yyyymmdd')||TO_CHAR(Time, 'hh24miss'),'yyyymmddhh24miss'); 

[daan.timmer]

@Motrax, enige jammere daarvan is, is dat je geen gebruik kan maken van indices die op de date en time velden staan.

Stel je bedrijf heeft 50medewerkers, ze werken allen 52weken per jaar 5dagen per week en klokken 's ochtends en 's middags in dan zit je na een jaar op 26000 records.
Afhankelijk van je query en server kan dit al een PITA zijn om te sorteren/zoeken/groupen zonder index.

Waarom doe je niet een pseudosql:

code:

select *, (select doehierdatetimecombineren from clocktimes where clocktimes.userid =users.userid orderby date,time DESC LIMIT 1) AS latesttime from users

[sjaakie]

daan.timmer schreef op woensdag 24 augustus 2011 @ 15:04:
Waarom doe je niet een pseudosql:

code:

select *, (select doehierdatetimecombineren from clocktimes where clocktimes.userid =users.userid orderby date,time DESC LIMIT 1) AS latesttime from users

Zo had ik het in eerste instantie ook bedacht, maar aangezien ik meer kolommen nodig heb uit de kloktijden gaat dit niet werken aangezien je hier een single (scalar) value terug krijgt en niet de complete row.

Ik weet dat hier 2 methodes voor zijn; Correlated subqueries of met een join. Onder MySQL krijg ik dat meestal ook wel voor elkaar, maar onder SQL server werkt dat net allemaal ff anders.

[RobIII]

Ik zou gewoon een view maken als de 2 gescheiden velden je probleem is...

[ Voor 39% gewijzigd door RobIII op 24-08-2011 15:39 ]

[Tom-my]

max en group by's? of zit ik echt te slapen

[daan.timmer]

Dan doe je het anders (ik heb alleen kennis van MySql) geen idee heo het in (MS)SQLServer werkt:

code:

SELECT  users.*
        somekindoftablename.*
FROM
    users,
    (
        SELECT
            date+time AS datetime,
            jeandere,
            velden
        FROM
            clocktijdentabel
        WHERE
            clcoktijdentabel.user_id = users.user_id
        ORDER BY
            date DESC, time DESC
        LIMIT 1
    ) AS somekindoftablename
ORDER BY
    somekindoftablename.datetime DESC

komt ipc op het zelfde neer als het goed is?

of dit inderdaad:

Tom-my schreef op woensdag 24 augustus 2011 @ 15:42:
max en group by's? of zit ik echt te slapen

[ Voor 15% gewijzigd door daan.timmer op 24-08-2011 15:44 ]

[Tom-my]

ohja, mysql en limits, was het bijna vergeten , dense_ranken !

[GlowMouse]

Motrax schreef op woensdag 24 augustus 2011 @ 14:50:
[...]
Ook voor performance redenen kan het beter zijn om datum en tijd apart op te slaan, als je veel vaker queriet op datum dan op datumtijd.

Je index wordt dan inderdaad kleiner. Een andere performancereden is dat je tijd apart kunt indexeren.