[SQL] Aanwezigheidslijst meerder dagen voor meerdere persone

[Bicky]

Ik ben bezig met een kloksysteem.
Momenteel klokt iedereen s'morgens in en s'avonds terug uit.
In DB komt dan
DATETIMEIN - DATETIMEOUT - userid - STATUS
STATUS is een veld dat bijhoud of de persoon is ingelogd, uitgelogd, in verlof, ziek is, ...

Ik wil nu een lijst trekken van de laatste 5 dagen voor alle personen wat hun status was.
vb:
maandag dinsdag woensdag donderdag vrijdag
pers1 ok ok ok ok ok
pers2 ok ok ok ok ok
pers3 ok ok verlof ok ok
pers4 ok ziek ziek ok ok

kan dit in 1 query?

[mrFoce]

Een beetje meer zelfstudie mag wel. Maar je kan prima op 'dataranges' zoeken. -> http://ayende.com/Blog/ar...2/16/UsingDataRanges.aspx

[ Voor 31% gewijzigd door mrFoce op 18-03-2009 07:59 ]

[Verwijderd]

Ja dat kan.

SQL:

select USERID, STATUS, DATETIMEIN, DATETIMEOUT from <tabel>
where DATETIMEIN >= <startdatum> and DATETIMEOUT < <einddatum + 1 dag>
order by USERID, DATETIMEIN

Je krijgt dan de resultaten per persoon onder elkaar die je in je presentatielaag netjes in een tabel kunt zetten.
Wil je de query al 5 dagen op 1 regel laten teruggeven, dan kom je uit op pivot table oplossingen zoals in de link van mrFoce. Maar dan wordt 't wat ingewikkelder en meer afhankelijk van wat je database aankan, en meestal is het handiger / onderhoudbaarder om de opmaak in je presentatielaag te regelen en niet in je query.

[Bicky]

de ingegeven waarden van de week selecteren had ik reeds gevonden.
Ik geef een willekeurig dag in de week en krijg de 5 dagen van de werkweek terug

WHERE daystartok >= DATE_SUB('2009-03-18', INTERVAL weekday('2009-03-18') day) AND daystartok<= DATE_ADD('2009-03-18', INTERVAL 7-weekday('2009-03-18') day)

Maar het omvormen naar een tabel volgens vooropgegeven formaat lukt mij niet.
Er moet ook rekening worden gehouden dat een persoon geen gegevens heeft voor een bepaalde dag.
vb: Ik wil de volledige week, morgen en overmorgen ook. maar zonder velden dan..

Ik kan moeilijk voor ieder veld in een query uitvoeren en controleren of die persoon er die dag is.

De dagen van de week heb ik ook in vbscript. dus die kunnen ook in de query worden opgebouwd/
zoiets als
WHERE date = "2009-03-16" OR date="2009-03-17" OR .....

[Coltrui]

Je kan je querystring dynamisch opbouwen met subselects, maar erg performant en netjes zal het niet zijn...

Kan je deze tabelstructuur niet in je GUI creëren aan de hand van je resultset?

[ Voor 8% gewijzigd door Coltrui op 18-03-2009 11:32 ]

[Bicky]

Heb het gevonden. Bedankt voor jullie hulp

Dit is het geworden:

SQL:

SELECT userid, concat(naam, ' ', voornaam) as naam, 
max(IF(daystartok>'2009-3-16' AND daystartok<'2009-3-16 23:59:00', status, NULL)) as '2009-3-16', 
max(IF(daystartok>'2009-3-17' AND daystartok<'2009-3-17 23:59:00', status, NULL)) as '2009-3-17', 
max(IF(daystartok>'2009-3-18' AND daystartok<'2009-3-18 23:59:00', status, NULL)) as '2009-3-18', 
max(IF(daystartok>'2009-3-19' AND daystartok<'2009-3-19 23:59:00', status, NULL)) as '2009-3-19', 
max(IF(daystartok>'2009-3-20' AND daystartok<'2009-3-20 23:59:00', status, NULL)) as '2009-3-20' from 
tbl_users LEFT JOIN tbl_flexhours on tbl_users.id = tbl_flexhours.userid 
group by userid

De datums bouw ik dynamisch op.

output:

ASP:

do until rs.EOF
            if vorigewaarde <> rs("userid") then
                response.Write("</tr><tr>")
                Response.Write("<td>" & rs("naam") & "</td>")
                if isnull(rs(maandag)) then
                    response.Write("<td align=center>x</td>")
                else
                    response.Write("<td>" & appelclass.statuslist.item(cint(rs(maandag))) & "</td>")
                end if
                if isnull(rs(dinsdag)) then
                    response.Write("<td align=center>x</td>")
                else
                response.Write("<td>" & appelclass.statuslist.item(cint(rs(dinsdag))) & "</td>")
                end if
                if isnull(rs(woensdag)) then
                    response.Write("<td align=center>x</td>")
                else
                response.Write("<td>" & appelclass.statuslist.item(cint(rs(woensdag))) & "</td>")
                end if
                if isnull(rs(donderdag)) then
                    response.Write("<td align=center>x</td>")
                else
                response.Write("<td>" & appelclass.statuslist.item(cint(rs(donderdag))) & "</td>")
                end if
                if isnull(rs(vrijdag)) then
                    response.Write("<td align=center>x</td>")
                else
                response.Write("<td>" & appelclass.statuslist.item(cint(rs(vrijdag))) & "</td>")
                end if
            end if

invullen van de dagen van de week via asp

ASP:

        maandag   = ReFormatDate(date-Weekday(date,2)+1, "yyyy-mm-dd")
        dinsdag   = ReFormatDate(date-Weekday(date,2)+2, "yyyy-mm-dd")
        woensdag  = ReFormatDate(date-Weekday(date,2)+3, "yyyy-mm-dd")
        donderdag = ReFormatDate(date-Weekday(date,2)+4, "yyyy-mm-dd")
        vrijdag   = ReFormatDate(date-Weekday(date,2)+5, "yyyy-mm-dd")

reformatdate is eigen functie om datums om te vormen naar eigen formaat

[sopsop]

ik zou die query toch zo doen:

SQL:

SELECT userid, concat(naam, ' ', voornaam) as naam,  
max(IF(daystartok>'2009-3-16' AND daystartok<'2009-3-17', status, NULL)) as '2009-3-16',  
max(IF(daystartok>'2009-3-17' AND daystartok<'2009-3-18', status, NULL)) as '2009-3-17',  
max(IF(daystartok>'2009-3-18' AND daystartok<'2009-3-19', status, NULL)) as '2009-3-18',  
max(IF(daystartok>'2009-3-19' AND daystartok<'2009-3-20', status, NULL)) as '2009-3-19',  
max(IF(daystartok>'2009-3-20' AND daystartok<'2009-3-21', status, NULL)) as '2009-3-20' from  
tbl_users LEFT JOIN tbl_flexhours on tbl_users.id = tbl_flexhours.userid  
group by userid

Zoals jij het nu doet kun je tussen 23:59:00 en 23:59:59 tussen de wal en het schip belanden. Op bovenstaande manier dek je dat af.

[Woy]

Bicky schreef op woensdag 18 maart 2009 @ 11:42:
Heb het gevonden. Bedankt voor jullie hulp

Dit is het geworden:

SQL:

SELECT userid, concat(naam, ' ', voornaam) as naam, 
max(IF(daystartok>'2009-3-16' AND daystartok<'2009-3-16 23:59:00', status, NULL)) as '2009-3-16', 
max(IF(daystartok>'2009-3-17' AND daystartok<'2009-3-17 23:59:00', status, NULL)) as '2009-3-17', 
max(IF(daystartok>'2009-3-18' AND daystartok<'2009-3-18 23:59:00', status, NULL)) as '2009-3-18', 
max(IF(daystartok>'2009-3-19' AND daystartok<'2009-3-19 23:59:00', status, NULL)) as '2009-3-19', 
max(IF(daystartok>'2009-3-20' AND daystartok<'2009-3-20 23:59:00', status, NULL)) as '2009-3-20' from 
tbl_users LEFT JOIN tbl_flexhours on tbl_users.id = tbl_flexhours.userid 
group by userid

Ik vind de manier van Afterlife toch mooier. De SQL die je hebt is overigens niet correct ( al werkt het in MySql waarschijnlijk wel ) aangezien je group by niet compleet is.