advies beste methode voor het het datum selecteren van datum/data
Door
Peter Turksma
op 28-11-2014 22:07
gewijzigd op 28-11-2014 23:14
1.176 views
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
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;
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 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 ?)
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.
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:
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.
Op dit resultaat moeten we de correctie tabel joinen, dus bovenstaande query als subquery inbouwen:
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 [url=http://sqlfiddle.com/#!2/9ca968/1]SQL Fiddle[/code]
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?
