advies beste methode voor het het datum selecteren van datum/data

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Peter Turksma

Peter Turksma

28/11/2014 22:07:03
Quote Anchor link
Mede Formleden graag een advies.

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
 
PHP hulp

PHP hulp

28/03/2024 12:17:48
 
Pipo Clown

Pipo Clown

28/11/2014 22:43:23
Quote Anchor link
even een klein en snel gedachtenspinsel:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
$sum = 0;
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
 
Willem vp

Willem vp

28/11/2014 22:50:06
Quote Anchor link
Allereerst wil ik beginnen met een correctie op je terminologie. Als je niet over hetzelfde praat, kun je mekaar niet begrijpen. ;-)

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. ;-)
 
Frank Nietbelangrijk

Frank Nietbelangrijk

28/11/2014 23:03:15
Quote Anchor link
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.)


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
 
Peter Turksma

Peter Turksma

28/11/2014 23:10:07
Quote Anchor link
Hi Willem bedankt voor de snelle reactie.

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.
 
Ward van der Put
Moderator

Ward van der Put

28/11/2014 23:18:08
Quote Anchor link
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.
 
Peter Turksma

Peter Turksma

28/11/2014 23:28:36
Quote Anchor link
Ward,

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 ?)
 
Ward van der Put
Moderator

Ward van der Put

28/11/2014 23:37:52
Quote Anchor link
Een SELECT COUNT(*) AS total geeft je één getal terug voor de periode uit de eerste tabel.
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.
 
Peter Turksma

Peter Turksma

29/11/2014 00:25:36
Quote Anchor link
Bedankt hiermee ga ik aan de slag het is me duidelijk

THX
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

29/11/2014 12:00:47
Quote Anchor link
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.

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)
PHP script in nieuw venster Selecteer het PHP script
1
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

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)
PHP script in nieuw venster Selecteer het PHP script
1
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   |
+ ------------ +

Op dit resultaat moeten we de correctie tabel joinen, dus bovenstaande query als subquery inbouwen:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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

Voor resultaat zie SQL Fiddle
Gewijzigd op 29/11/2014 17:39:49 door Ger van Steenderen
 
Ward van der Put
Moderator

Ward van der Put

29/11/2014 12:34:02
Quote Anchor link
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.

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?
 
Peter Turksma

Peter Turksma

29/11/2014 13:39:49
Quote Anchor link
Ik wil de prijs van verhuur corrigeren aan de hand van de verhuurde 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
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.