mysql query datum/tijd issues
tabel: cdr
id
rec_num
beg_datetime
duration
cli
call_type
tabel: rate
id
rate_band
dow
day_of_week
begin_rate
end_rate
tabel: tarieven
id
charge_band
rate_band
per_minute
------------invulling--------------
tarieven
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
INSERT INTO `tarieven` (`id`, `charge_band`, `rate_band`, `per_minute`) VALUES
(1, 'NL to Gabon', 'Peak', 200),
(2, 'NL to Gabon', 'Offpeak', 100),
(3, 'NL to Pakistan', 'Peak', 200),
(4, 'NL to Pakistan', 'Offpeak', 100),
(5, 'NL to Gabon', 'Weekend_And_Night', 50),
(6, 'NL to Pakistan', 'Weekend_And_Night', 50);
(1, 'NL to Gabon', 'Peak', 200),
(2, 'NL to Gabon', 'Offpeak', 100),
(3, 'NL to Pakistan', 'Peak', 200),
(4, 'NL to Pakistan', 'Offpeak', 100),
(5, 'NL to Gabon', 'Weekend_And_Night', 50),
(6, 'NL to Pakistan', 'Weekend_And_Night', 50);
cdr
Code (php)
1
2
3
4
2
3
4
INSERT INTO `cdr` (`id`, `rec_num`, `beg_datetime`, `duration`, `cli`,`call_type`) VALUES
(1, 24034244225, '2007-07-24 11:03:57', 62312, '31243523847', 'NL to Gabon'),
(2, 24034259231, '2007-07-26 08:02:11', 10, '31206856312','70 EUROcent per call'),
(3, 24034265714, '2007-07-27 23:08:21', 305, '31356094537','NL to Pakistan');
(1, 24034244225, '2007-07-24 11:03:57', 62312, '31243523847', 'NL to Gabon'),
(2, 24034259231, '2007-07-26 08:02:11', 10, '31206856312','70 EUROcent per call'),
(3, 24034265714, '2007-07-27 23:08:21', 305, '31356094537','NL to Pakistan');
rate
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
INSERT INTO `rate` (`id`, `rate_band`, `dow`, `begin_rate`, `end_rate`) VALUES
(1, 'Peak', '2', '08:00:00', '18:59:59'),
(2, 'Offpeak', '2', '19:00:00', '23:59:59'),
(3, 'Weekend_And_Night', '2', '00:00:00', '07:59:59'),
(4, 'Peak', '3', '08:00:00', '18:59:59'),
(5, 'Peak', '4', '08:00:00', '18:59:59'),
(6, 'Peak', '5', '08:00:00', '18:59:59'),
(7, 'Peak', '6', '08:00:00', '18:59:59'),
(8, 'Offpeak', '3', '19:00:00', '23:59:59'),
(9, 'Offpeak', '4', '19:00:00', '23:59:59'),
(10, 'Offpeak', '5', '19:00:00', '23:59:59'),
(11, 'Offpeak', '6', '19:00:00', '23:59:59'),
(12, 'Weekend_And_Night', '7', '00:00:00', '23:59:59'),
(13, 'Weekend_And_Night', '1', '00:00:00', '23:59:59'),
(14, 'Weekend_And_Night', '3', '00:00:00', '07:59:59'),
(15, 'Weekend_And_Night', '4', '00:00:00', '07:59:59'),
(16, 'Weekend_And_Night', '5', '00:00:00', '07:59:59'),
(17, 'Weekend_And_Night', '6', '00:00:00', '07:59:59');
(1, 'Peak', '2', '08:00:00', '18:59:59'),
(2, 'Offpeak', '2', '19:00:00', '23:59:59'),
(3, 'Weekend_And_Night', '2', '00:00:00', '07:59:59'),
(4, 'Peak', '3', '08:00:00', '18:59:59'),
(5, 'Peak', '4', '08:00:00', '18:59:59'),
(6, 'Peak', '5', '08:00:00', '18:59:59'),
(7, 'Peak', '6', '08:00:00', '18:59:59'),
(8, 'Offpeak', '3', '19:00:00', '23:59:59'),
(9, 'Offpeak', '4', '19:00:00', '23:59:59'),
(10, 'Offpeak', '5', '19:00:00', '23:59:59'),
(11, 'Offpeak', '6', '19:00:00', '23:59:59'),
(12, 'Weekend_And_Night', '7', '00:00:00', '23:59:59'),
(13, 'Weekend_And_Night', '1', '00:00:00', '23:59:59'),
(14, 'Weekend_And_Night', '3', '00:00:00', '07:59:59'),
(15, 'Weekend_And_Night', '4', '00:00:00', '07:59:59'),
(16, 'Weekend_And_Night', '5', '00:00:00', '07:59:59'),
(17, 'Weekend_And_Night', '6', '00:00:00', '07:59:59');
waar rate.dow de dag van de week (dayofweek) is.
------------problems---------------
Dit zijn overzichten met gesprekken en tarieven. Ik wil uitrekenen met een query voor hoeveel er totaal is verbelt.
Nu werkt de onderstaande query in principe als een zonnetje. Maar dan moet je wel in 1 tariefsgebied blijven. Begin je in een Peak en einding je je gesprek in een Offpeak, gaat het fout.
Ik heb een berg mogelijkheden in mn hoofd de revu laten passeren maar ik kom telkens op een dead-end uit. Is er iemand die me de goede richting in kan douwen.
(voorbeeld is het gesprek NL to Gabon, die over 2 'tarieven' loopt, waar NL to Pakistan precies binnen 1 tarief valt. Deze gaat dus wel goed, gabon niet)
------------huidige query-------------
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
29
30
31
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
29
30
31
SELECT
c.rec_num,
c.cli,
c.call_type,
r.begin_rate,
r.end_rate,
r.dow,
r.rate_band,
c.beg_datetime,
DATE_ADD(c.beg_datetime,INTERVAL c.duration SECOND) AS end_datetime,
t.per_minute,
((c.duration / 60) * t.per_minute) AS prijs_centen,
(((c.duration / 60) * t.per_minute) / 100) AS prijs_euros
FROM
cdr c,
rate r,
tarieven t
WHERE
c.call_type = t.charge_band
AND
t.rate_band = r.rate_band
AND
DAYOFWEEK(c.beg_datetime) = r.dow
AND
(
TIME(DATE_ADD(c.beg_datetime,INTERVAL c.duration SECOND)) <= r.end_rate
AND
TIME(c.beg_datetime) >= r.begin_rate
)
ORDER BY
c.rec_num
c.rec_num,
c.cli,
c.call_type,
r.begin_rate,
r.end_rate,
r.dow,
r.rate_band,
c.beg_datetime,
DATE_ADD(c.beg_datetime,INTERVAL c.duration SECOND) AS end_datetime,
t.per_minute,
((c.duration / 60) * t.per_minute) AS prijs_centen,
(((c.duration / 60) * t.per_minute) / 100) AS prijs_euros
FROM
cdr c,
rate r,
tarieven t
WHERE
c.call_type = t.charge_band
AND
t.rate_band = r.rate_band
AND
DAYOFWEEK(c.beg_datetime) = r.dow
AND
(
TIME(DATE_ADD(c.beg_datetime,INTERVAL c.duration SECOND)) <= r.end_rate
AND
TIME(c.beg_datetime) >= r.begin_rate
)
ORDER BY
c.rec_num
Gewijzigd op 01/01/1970 01:00:00 door - wes -
'NL to Gabon' zijn bijvoorbeeld 2 gegevens in 1 kolom. Het lijkt mij dat dit een 'van' en 'naar' moet worden. Uiteraard verwijs je dan naar een landen-tabel, je gaat geen 200x NL of Gabon opslaan, hooguit de id's van de landen.
Daarnaast heb je de situatie, dat 1 combinatie van landen, bv. van NL naar Gabon, meerdere tarieven kent. Dat zet je dus weer in een aparte tabel, of waarschijnlijker, aparte tabellen. Meervoud.
De tarieven zijn (waarschijnlijk) allemaal opgebouwd uit de blokken 'peak', 'offpeak' en 'Weekend_And_Night'. Dat zet je dus in een apart tabelletje, kun je later eenvoudig nog meer blokken aanmaken, zonder direct alles overhoop te moeten halen.
Wanneer je dit klaar hebt, ga je de daadwerkelijke tarieven invoeren in een koppeltabel die de van-naar en het tarief-blok koppelt:
van_naar_id, tarief_id, prijs.
Wanneer iemand nu gaat bellen, pakt hij 1 van_naar (bv. NL en Gabon) en wellicht meerdere tarieven, afhankelijk van de tijden.
Ik denk dat je hiermee het probleem van de selectie ook wel kunt oplossen.
NL to Gabon is een charge_band. Hierin staat gewoon wat voor een type gesprek het is. Dus geen van.. naar verhaal.
De tarieven staan al apart in een tabel :
(1, 'NL to Gabon', 'Peak', 200),
(2, 'NL to Gabon', 'Offpeak', 100)
bijv
Elke charge_band heeft zijn eigen prijs per tariefsklasse peak, offpeak etc.
Het 'afhankelijk van de tijden' is mijn probleem juist. Ik kan lastig bepalen over welke tariefklasses een gesprek loopt. Alles wat je zelf aandroeg heb ik al up and running, alleen dit laatste deel krijg ik niet voor elkaar. Ookal zet ik de tarieven apart in een koppeltabel, neemt het nog niet weg dat de tijden waarover een tarief gaat gewoon vast daarin staan. Of deze nou in dezelfde tabel staan of in een koppeltabel heeft 0 invloed op mijn berekening van tijd die ik moet maken. Zon select uit een koppeltabel lukt me ook nog wel, das het probleem niet.
@erik, dit heeft geen invloed op mn query waar ik de tijd+prijs wil berekenen tho. en dan nog, het is geen dubbele data, hoogstens kan er een koppeltabelletje tussen, dit heeft alleen even geen prio
al sla ik het een milioen keer op, heeft nog geen invloed op mn probleem, and you know it
Gewijzigd op 01/01/1970 01:00:00 door - wes -
Edit: of het nou een verwijzing is of een vaste waarde, je gaat geen strings 1000x opslaan
Gewijzigd op 01/01/1970 01:00:00 door PHP erik
Maar dat mag je uiteraard helemaal zelf weten.
Succes!
Quote:
Nee het lijkt maar zo :). Optische illusie.het is geen dubbele data
(behalve het van, naar gebeuren, dat is een charge_band, geen echt van.. naar veld)
ik maak niet zomaar een topic, en je weet best dat ik dit alleen post als ik het niet anders kan doen. stop miereneuking alsof ik hier net zit.
Gewijzigd op 01/01/1970 01:00:00 door - wes -
Edit: wes, je kunt jezelf wel een beetje flexibel opstellen. Nu gedraag je je zo van: "Ik wil geen commentaar, geef me nou maar de oplossing!!!". Daar heb ik geen zin in. Zeker niet van iemand met 4000 offtopic posts.
Gewijzigd op 01/01/1970 01:00:00 door PHP erik
Voel je je er beter bij als ik normaliseer en dan dezelfde vraag nog een keer post?
het kan zijn dat iemand vier uur aan de telefoon hangt, waarvan twee uur peaktarief en twee uur offpeaktarief en dat maakt het moeilijk de kosten uit te rekenen?
Maar goed, ook ik wens je veel succes!
@erik, dat vraag ik toch? moet ik voor je even normaliseren, zodat je dan wel met exact dezelfde vraag als daarvoor wel kan helpen?
PHPerik schreef op 15.10.2007 16:23:
normaliseren, iets dat alles onoverzichtelijker maakt en je database verneukt.
Geen commentaar. ;-)))
wes schreef op 15.10.2007 16:25:
@jan, juist
Ja en dan wil je ook totalen kunnen maken natuurlijk. Interessant probleem! Ik zal even een klein testopstellinkje maken om dit te bestuderen.
@rest, sorry voor mn attitude, tis alleen een probleem wat me tonnen kan kosten als ik dit niet optijd voor mekaar krijg. vandaar dat ik er nogal haast mee heb
Quote:
Het verwijst naar het woord "puntje", en dat suggereert een foutief puntje. Beetje slechte signaalwoorden, ik weet het, maar ik bedoel niet dat normaliseren iets verneukt.Geen commentaar. ;-)))
In dit geval maakt normaliseren het hele verhaal eenvoudiger, maar dat wil TS nog niet inzien :)
wes schreef op 15.10.2007 16:31:
Wat schuift het? Dan help ik je wel even en los dat probleem met normaliseren ook wel even voor je op.tis alleen een probleem wat me tonnen kan kosten als ik dit niet optijd voor mekaar krijg. vandaar dat ik er nogal haast mee heb
Als we het over zoveel geld hebben, daar valt mijn tarief bij in het niets...
of post even ontopic
Edit:
Zou niet zo snel 1 query kunnen verzinnen die dat voor je oplost. De bruteforce manier zou zijn:
1) alle tarieven ophalen die bij het gesprek horen en fetchen
2) in de while loop waarin je fetcht een tweede query maken die de overlaps checkt en berekent
1) alle tarieven ophalen die bij het gesprek horen en fetchen
2) in de while loop waarin je fetcht een tweede query maken die de overlaps checkt en berekent
Gewijzigd op 01/01/1970 01:00:00 door Jan Koehoorn
wes schreef op 15.10.2007 16:39:
Wes, ik vind je reacties vaak bijzonder grappig en daar is het verzoek 'of post even ontopic' geen uitzondering op!normaal het hele bedrag speciaal voor jou frank, helaas zit er een sarcasme uitzondering op.
of post even ontopic
of post even ontopic
Nooit gedacht dat nog eens van jou te mogen horen! De offtopic-man himself die vraagt om een ontopic reactie!
Maar nu raken we erg offtopic... :p
jan, bedankt voor de effort zover. Dat het lastig is kwam ik ook al tegen, lijkt dat ik er niet aan ontkom dit in 2 querys te zetten. Maar dan nog krijg ik het niet voor elkaar om alle tariefklasses waar het gesprek doorloopt eruit te krijgen. Das mn prioriteit 1
Gewijzigd op 01/01/1970 01:00:00 door - wes -