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
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);
cdr
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');
rate
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');
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-------------
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
