advies beste methode voor het het datum selecteren van datum/data
Ik zou door het ingeven van een mysql select een correctiefactor willen krijgen uit de database van het aantal dagen welke zich bevinden in het bereik van id 1 t/m id 3 (tabel A en B) maal tabel C
Een voorbeeld :
Ik geef een startdatum bv 2014-01-05 en een stopdatum 2014-03-10
Mijn database heeft tabel met kolom A B en C (correctietabel)
A B C
2014-01-01 2014-02-10 95
2014-02-11 2014-02-28 80
2014-03-01 2014-03-10 85
Oplossing die ik zoek
id 1 = 26 dagen x 95 2470
id 2 = 28 dagen x 80 2240
id 3 = 10 dagen x 85 850
totaal 64 dagen 5560/64 = 86,875 correctiefactor
Wat is de beste manier om dit met een query te bereiken.? graag een advies
Zelf dacht ik aan diversen counts om later te vermenigvuldigen met tabel C of een gemiddelde
Dit is nogal omslachtig Thanks
Gewijzigd op 28/11/2014 23:14:46 door Peter Turksma
Code (php)
1
2
3
4
5
6
2
3
4
5
6
$sum = 0;
foreach (ABC as $range){
$sum = $sum + ((range[einddatum] - range[startdatum]) * range[correctie]);
}
echo 'Totaal is '.$sum;
foreach (ABC as $range){
$sum = $sum + ((range[einddatum] - range[startdatum]) * range[correctie]);
}
echo 'Totaal is '.$sum;
ABC is uiteraard het queryresultaat van de tabel ABC
Je zegt: "Mijn database heeft tabel A B en C".
Je bedoelt: "Mijn tabel heeft kolom A B en C". (Althans, dat denk/hoop ik.)
Wat ik uit je verhaal begrijp, is dat je het aantal dagen wilt berekenen dat ligt tussen de twee data in kolom A en B. Als dat zo is, dan heb je me mooi in verwarring weten te brengen doordat de getallen in je voorbeeld niet kloppen...
Het verschil tussen twee datumvelden kun je uitrekenen met SELECT DATEDIFF(B,A) FROM tabel;
Vermenigvuldigen met de correctiefactor: SELECT DATEDIFF(B,A)*C FROM tabel;
Het optellen van al die getallen: SELECT SUM(DATEDIFF(B,A)*C) FROM tabel;
Het berekenen van de nieuwe correctiefactor: SELECT SUM(DATEDIFF(B,A)*C) / SUM(DATEDIFF(B,A)) FROM tabel;
Zo simpel kan SQL zijn. ;-)
Willem vp op 28/11/2014 22:50:06:
Je zegt: "Mijn database heeft tabel A B en C".
Je bedoelt: "Mijn tabel heeft kolom A B en C". (Althans, dat denk/hoop ik.)
Je bedoelt: "Mijn tabel heeft kolom A B en C". (Althans, dat denk/hoop ik.)
aaah ... Nu kan ik het al beter volgen :-)
Jij doet vaker cryptogrammen Willem? :p
Gewijzigd op 28/11/2014 23:03:55 door Frank Nietbelangrijk
Mijn database heeft inderdaad een tabel met de kolomen A,B en C
Je voorbeeld is me duidelijk maar het is niet wat ik bedoel volgens mij krijg ik hier het aantal dagen uit de database ik wil weten van de opgegeven start en stop datum hoeveel hiervan in id-1 zitten x kolom C
id 2 enz enz als we hier WHERE date BETWEEN start AND STOP toevoegen hebben we mogelijk het juiste resultaat
De oplossing van Pipo moet ik nog even bestuderen maar deze loopt volgens mij wel door alle records heen
maar dan moeten alle gegvens uit Mysql opgehaald worden.
>> ik wil weten van de opgegeven start en stop datum hoeveel hiervan in id-1 zitten x kolom C
Dan komen meerdere start/stop-datums overeen met één waarde in kolom C?
Als dat het geval is, hoort kolom C in een andere tabel.
Ik ben inderdaad op zoek naar het aantal dagen dat voorkomt in de range van bv id 1
ik zou dus twee tabellen moeten maken een om de aantallen te bepalen en de andere met de correctie faktoren
Hoe koppel ik de tabellen zodat altijd de juiste aantallen bij de juiste correctiefaktoren komen. (id ?)
Die vermenigvuldig je vervolgens met één correctiefactor uit de tweede tabel. Dat kan met één query door een JOIN te gebruiken tussen de twee tabellen.
THX
Ward van der Put op 28/11/2014 23:18:08:
Laat eens de tabeldefinitie zien inclusief echte data?
>> ik wil weten van de opgegeven start en stop datum hoeveel hiervan in id-1 zitten x kolom C
Dan komen meerdere start/stop-datums overeen met één waarde in kolom C?
Als dat het geval is, hoort kolom C in een andere tabel.
>> ik wil weten van de opgegeven start en stop datum hoeveel hiervan in id-1 zitten x kolom C
Dan komen meerdere start/stop-datums overeen met één waarde in kolom C?
Als dat het geval is, hoort kolom C in een andere tabel.
Wat je dan gaat doen is een tabel bijmaken met daarin een PK en de bijhorende correctie factor. Er van uitgaande dat die correctie factor uniek is zou die als PK ingesteld kunnen (ik vind moeten) worden, dus krijg je een tabel met één kolom.
Lijkt mij weinig zinvol.
Wat hier nodig is een (virtuele) tabel met de datums tussen 2014-01-05 en 2014-03-10.
Dit doe je als volgt:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
@dateval := @dateval + INTERVAL 1 DAY dateval
FROM
(SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) a
CROSS JOIN
(SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) b
CROSS JOIN
(SELECT @dateval := CAST('2014-01-05' AS DATE) - INTERVAL 1 DAY) c
WHERE
@dateval <= CAST('2014-03-10' AS DATE) - INTERVAL 1 DAY
@dateval := @dateval + INTERVAL 1 DAY dateval
FROM
(SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) a
CROSS JOIN
(SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) b
CROSS JOIN
(SELECT @dateval := CAST('2014-01-05' AS DATE) - INTERVAL 1 DAY) c
WHERE
@dateval <= CAST('2014-03-10' AS DATE) - INTERVAL 1 DAY
De twee eerste subquery's zijn om een voldoende aantal rijen te creëren (die kan je eventueel vervangen door een reeds bestaande tabel met voldoende records), de laatste om een waarde toe te kennen aan @dateval.
Dit levert op als resultaat (ingekort):
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
+ ------------ +
| dateval |
+ ------------ +
| 2014-01-05 |
| 2014-01-06 |
| 2014-01-07 |
| ..... |
| 2014-03-08 |
| 2014-03-09 |
| 2014-03-10 |
+ ------------ +
| dateval |
+ ------------ +
| 2014-01-05 |
| 2014-01-06 |
| 2014-01-07 |
| ..... |
| 2014-03-08 |
| 2014-03-09 |
| 2014-03-10 |
+ ------------ +
Op dit resultaat moeten we de correctie tabel joinen, dus bovenstaande query als subquery inbouwen:
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
SELECT
COUNT(1) days,
SUM(c.factor) days_corrected,
/* oops SUM(c.factor) / COUNT(1) correctie: */
AVG(c.factor) overall_factor
FROM
(
SELECT
@dateval := @dateval + INTERVAL 1 DAY dateval
FROM
(SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) a
CROSS JOIN
(SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) b
CROSS JOIN
(SELECT @dateval := CAST('2014-01-05' AS DATE) - INTERVAL 1 DAY) u
WHERE
@dateval <= CAST('2014-03-10' AS DATE) - INTERVAL 1 DAY
) d
JOIN
corrections c
ON d.dateval BETWEEN c.start_date AND c.end_date
COUNT(1) days,
SUM(c.factor) days_corrected,
/* oops SUM(c.factor) / COUNT(1) correctie: */
AVG(c.factor) overall_factor
FROM
(
SELECT
@dateval := @dateval + INTERVAL 1 DAY dateval
FROM
(SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) a
CROSS JOIN
(SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) b
CROSS JOIN
(SELECT @dateval := CAST('2014-01-05' AS DATE) - INTERVAL 1 DAY) u
WHERE
@dateval <= CAST('2014-03-10' AS DATE) - INTERVAL 1 DAY
) d
JOIN
corrections c
ON d.dateval BETWEEN c.start_date AND c.end_date
Voor resultaat zie SQL Fiddle
Gewijzigd op 29/11/2014 17:39:49 door Ger van Steenderen
Ger van Steenderen op 29/11/2014 12:00:47:
Wat je dan gaat doen is een tabel bijmaken met daarin een PK en de bijhorende correctie factor. Er van uitgaande dat die correctie factor uniek is zou die als PK ingesteld kunnen (ik vind moeten) worden, dus krijg je een tabel met één kolom.
Lijkt mij weinig zinvol.
Lijkt mij weinig zinvol.
Eens, daar heeft Ger echt een punt.
We moeten eerst weten wát die correctiefactor nu eigenlijk corrigeert. Of anders gezegd: er zit kennelijk iets "scheef" in de oorspronkelijke data dat moet worden "rechtgetrokken" in de uiteindelijke data.
Dat kan echter van alles zijn. Bijvoorbeeld het aantal dagen per maand (28, 29, 30 of 31), maar ook het aantal zaterdagen en zondagen in de opgegeven periode.
Dus een concrete vraag: wat corrigeert de correctiefactor in de datums?
Ik heb in de prijstabel maar 1 prijs (dit is 100%)
Hiernaast heb ik nog tweede correctie midweek 90% en 120% (week is 100%)
invoer admin kant:
01-08-2014 tot 31-08-2014 100 % 90 % 100 % 120 % Wijzigen | Wissen
01-09-2014 tot 30-09-2014 60 % 50 % 100 % 120 % Wijzigen | Wissen
01-10-2014 tot 31-10-2014 75 % 90 % 100 % 120 % Wijzigen | Wissen
01-11-2014 tot 30-11-2014 60 % 90 % 100 % 120 % Wijzigen | Wissen
01-12-2014 tot 20-12-2014 60 % 90 % 100 % 120 % Wijzigen | Wissen
Website kant :
Verblijf Midweek Weekend Week
01-08-2014 t/m 31-08-2014 € 432.00 € 432.00 € 840.00
01-09-2014 t/m 30-09-2014 € 144.00 € 259.20 € 504.00
01-10-2014 t/m 31-10-2014 € 324.00 € 324.00 € 630.00
01-11-2014 t/m 30-11-2014 € 259.20 € 259.20 € 504.00
01-12-2014 t/m 20-12-2014 € 259.20 € 259.20 € 504.00
21-12-2014 t/m 02-01-2015 € 432.00 € 432.00 € 840.00
Het kan hier gaan om verhuur voor 3 dagen maar ook voor 6 maanden.
Door EEN correctie faktor te maken kun je jaarlijks alles eenvoudig updaten terwijl je verchillende prijs heb.
Als de correctie faktor wordt berekend door te kijken hoeveel dagen een bepaalde correctie faktor wordt gebruikt en dit voor alle ranges te doen om zo de gemiddelde faktor te bepalen is het hierna eenvoudig prijs * correctie = nieuwe prijs voor het totaal aantal geboekte dagen.
Toevoeging op 29/11/2014 13:44:01:
Hoop dat bovenstaande duidelijk genoeg is.
Het invoeren en het zichtbaar maken is gelukt, maar de berekening van de totaalprijs is lastiger dan ik dacht
ik programeer als hobby en zocht naar een opzet om dit aan te pakken maar het is lastiger dan ik dacht.
Wat ik uiteindelijk wil is een correctiefaktor X price voor de totale verblijf periode.
Thx
Toevoeging op 29/11/2014 14:00:23:
TOP GER
Ik was op zoek naar hoe ik het moest aanpakken maar jouw code is de oplossing werkt perfect.
Thanks