Overzicht maken met SUM and ORDER BY
er is mij de vraag gesteld om een tabel te maken
waar de werkuren van onze dienst in staan en dit
te rangschikken volgens maand en naam.
Nu kan ik dit doen in een overzicht per persoon
voor een specifieke maand, maar ik zit vast
om het in een algemeen overzicht te doen.
Ik kan natuurlijk dit eene script kopieren voor elke
persoon en dan voor elke maand maar een snelle telling
zou dit een kleine 5000 regels code opleveren. Dus ergens
moet er een een iets eenvoudigere manier bestaan.
Kort geschetst is het resultaat:
--------------------------------------------------
--------------------------|JANUARY-|FEBRUARY|....
--------------------------------------------------
-NAAM1-----|-te presteren-|---A----|--D---|
-----------|-gepresteerd--|---B----|--E---|
-----------|-saldo--------|-(A-B)--|-(D-F)|
--------------------------------------------------
-NAAM2-----|-te presteren-|---A----|--D---|
-----------|-gepresteerd--|---B----|--E---|
-----------|-saldo--------|-(A-B)--|-(D-F)|
--------------------------------------------------
Nu kan ik voor 1 maand berkenen door volgende code:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?
$jan= '167';
php mysql_select_db($database_vlmocc, $vlmocc);
$query = "SELECT SUM(time)FROM roster WHERE name = 'VAMA' AND maand ='January'";
$time = mysql_query($query) or die(mysql_error());
$time2 = mysql_fetch_assoc($time);
$time3 = $time2['SUM(time)'];
$query = "SELECT SUM(night)FROM roster WHERE name = 'VAMA' AND maand ='January'";
$night = mysql_query($query) or die(mysql_error());
$night2 = mysql_fetch_assoc($night);
$night3 = $night2['SUM(night)'];
$query = "SELECT SUM(sat)FROM roster WHERE name = 'VAMA' AND maand ='January'";
$sat = mysql_query($query) or die(mysql_error());
$sat2 = mysql_fetch_assoc($sat);
$sat3 = $sat2['SUM(sat)'];
$query = "SELECT SUM(sun)FROM roster WHERE name = 'VAMA' AND maand ='January'";
$sun = mysql_query($query) or die(mysql_error());
$sun2 = mysql_fetch_assoc($sun);
$sun3 = $sun2['SUM(sun)'];
$total = $time3 + $night3 + $sun3;
$overtime = $total - $jan;
$?>
$jan= '167';
php mysql_select_db($database_vlmocc, $vlmocc);
$query = "SELECT SUM(time)FROM roster WHERE name = 'VAMA' AND maand ='January'";
$time = mysql_query($query) or die(mysql_error());
$time2 = mysql_fetch_assoc($time);
$time3 = $time2['SUM(time)'];
$query = "SELECT SUM(night)FROM roster WHERE name = 'VAMA' AND maand ='January'";
$night = mysql_query($query) or die(mysql_error());
$night2 = mysql_fetch_assoc($night);
$night3 = $night2['SUM(night)'];
$query = "SELECT SUM(sat)FROM roster WHERE name = 'VAMA' AND maand ='January'";
$sat = mysql_query($query) or die(mysql_error());
$sat2 = mysql_fetch_assoc($sat);
$sat3 = $sat2['SUM(sat)'];
$query = "SELECT SUM(sun)FROM roster WHERE name = 'VAMA' AND maand ='January'";
$sun = mysql_query($query) or die(mysql_error());
$sun2 = mysql_fetch_assoc($sun);
$sun3 = $sun2['SUM(sun)'];
$total = $time3 + $night3 + $sun3;
$overtime = $total - $jan;
$?>
De $total bestaat uit 3 waarden aangezien ik aparte log moet hebben van de zaterdag en zondaguren en deze ook deel uitmaken van het totaal.
Hoe kan ik dit nu vereenvoudigen zodat ik deze code niet moet draaien voor elke
maand apart en elke persoon apart is dan ook de vraag.
Alvast bedankt voor de hulp.
Yannick
Gewijzigd op 06/05/2012 17:12:07 door Yannick Bogaert
Je zou eens kunnen zoeken op ORDER BY en GROUP BY.
Is het een idee om de resultaten in 1 keer op te halen voor elke maand?
Je kunt dan met dit voorbeeld de "verbruikte tijden" ophalen.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?
# Resultaat-array klaarzetten
$statistieken = array();
# Ophalen van de verbruikte tijd per maand
$result = mysql_query("select sum(time) verbruikte_tijd from roster where name = 'VAMA' group by maand");
# Zijn er resultaten?
if (mysql_num_rows($result)) {
# Voor elk resultaat ...
while ($statistiek = mysql_fetch_object($result)) {
$statistieken[$statistiek->maand] = $statistiek->verbruikte_tijd;
}
}
# tmp
echo '<pre>';
print_r($statistieken);
echo '<pre>';
?>
# Resultaat-array klaarzetten
$statistieken = array();
# Ophalen van de verbruikte tijd per maand
$result = mysql_query("select sum(time) verbruikte_tijd from roster where name = 'VAMA' group by maand");
# Zijn er resultaten?
if (mysql_num_rows($result)) {
# Voor elk resultaat ...
while ($statistiek = mysql_fetch_object($result)) {
$statistieken[$statistiek->maand] = $statistiek->verbruikte_tijd;
}
}
# tmp
echo '<pre>';
print_r($statistieken);
echo '<pre>';
?>
Je kunt dan de verbruikte tijd in januari b.v. benaderen;
Groet,
je query zal ook meerdere errors opwerpen.
@yannick, hoe ziet je tabel eruit?
Gewijzigd op 06/05/2012 18:24:02 door Jeroen VD
een mooi lijstje allemaal net gesorteerd.
Code (php)
1
2
3
2
3
<?php
SELECT name,maand,SUM(time),SUM(sun),SUM(night),SUM(sat)FROM roster GROUP BY name, maand
?>
SELECT name,maand,SUM(time),SUM(sun),SUM(night),SUM(sat)FROM roster GROUP BY name, maand
?>
Het probleem is echter dat ik nog apart ook een berekening moet kunnen
maken namelijk: total time = SUM(time) + SUM(sun) + SUM(night) + SUM(sat)
en ook een berekening van de balans: balans = total time - te presteren
@jeroen vd: de tabel is eigenlijk simpel met paar velden:
- name ( vd persoon )
- time ( waarde die berekent werd door begin en einde van elkaar af te trekken)
- night ( waarde door berekening van start of einde - bepaalde waarde )
- sat ( zaterdag uren )
- sun ( zondag uren )
- begin ( datum + uur van begin )
- eind ( datum + uur van einde shift )
- maand ( de huidige maand )
- timestamp
Gewijzigd op 06/05/2012 18:33:15 door Yannick Bogaert
ik denk dat het in dat geval verstandiger is om twee velden te maken;
Code (php)
1
2
2
alter table roster add column startdatum datetime;
alter table roster add column einddatum datetime;
alter table roster add column einddatum datetime;
Ik zie dat je een veld "name" hebt, dat lijkt in deze situatie niet optimaal. Je zou kunnen overwegen om een aparte
tabel "persoon" o.i.d. te maken. (Kijk voor meer informatie hier http://www.phphulp.nl/php/tutorial/overig/normalisatie/426/)
Daarnaast heb je met deze twee nieuwe -vervangende- velden het comfort dat je alle informatie zoals "uren zondag", "uren zaterdag" niet apart hoeft op te slaan.
Je kunt b.v. een dagnaam ophalen met de MySQL functie "dayname()", denk aan;
[code
Code (php)
1
2
3
4
5
6
2
3
4
5
6
<?php
$query = "select timediff(startdatum, einddatum) as verbruikte_tijd from roster
where dayname(startdatum) in ('Saturday','Sunday') group by month(startdatum)
";
;
?>
$query = "select timediff(startdatum, einddatum) as verbruikte_tijd from roster
where dayname(startdatum) in ('Saturday','Sunday') group by month(startdatum)
";
;
?>
Bovenstaande voorbeelden dienen natuurlijk aangepast te worden, en bovenstaande werkt niet wanneer het record een datumverschil van meer
dan 1 dag oplevert (en je weekend wil selecteren).
Suc6!
Gewijzigd op 06/05/2012 18:57:43 door Toine vd P