mysql query datum/tijd issues

Door - wes - op 15-10-2007 15:21

4.015 views

Ik heb 3 tabellen:

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

P PHP erik

15-10-2007 16:52

klik
klik
klik
klik
klik

What goes around, goes around, goes around
Comes all the way back around
What goes around, goes around, goes around
Comes all the way back around
What goes around, goes around, goes around
Comes all the way back around
What goes around, goes around, goes around
Comes all the way back around

- - wes -

15-10-2007 17:01

goh, bedankt voor de paginas die ik allemaal al open had
behalve die van je homosensuele vriendje dan

P PHP erik

15-10-2007 17:08

Mijn advies is om met die soort problemen vanuit een goede basis te starten: goede normalisatie en goed databasesysteem. PostgreSQL zou een oplossing kunnen zijn. Zeer krachtige functies. Zie o.a. http://www.phphulp.nl/php/tutorials/3/493/ en post maar als je iets specifieks hebt waar je niet uitkomt!

- - wes -

15-10-2007 17:10

zit mn haar wel goed? of moet ik dat ook voor je aanpassen?

dacht dat je zei dat je geen tijd had...

P PHP erik

15-10-2007 17:11

Ik heb altijd tijd voor actieve PHPhulp gebruikers. Ik heb me bedacht.

zit mn haar wel goed? Muah

Jan Koehoorn

15-10-2007 17:13

Ik heb een zeer primitief beginnetje gemaakt. Als je in de tweede query WHERE ... AND doet, krijg je in ieder geval alle gesprekken die binnen 1 tariefperiode vielen. Verander je de voorwaarde in OR dan krijg je ze allemaal, maar hoe je dan moet totaliseren kan ik zo snel niet verzinnen.

<?php
	require 'db_config.php';
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php
	$sql = "
		SELECT *
		FROM tarieven
		ORDER BY begintijd ASC, eindtijd ASC
		";
	if ($res = mysql_query ($sql)) {
		while ($row = mysql_fetch_assoc ($res)) {
			$sql2 = "
				SELECT *
				FROM gesprekken
				WHERE van BETWEEN '" . $row['begintijd'] . "' AND '" . $row['eindtijd'] . "'
				AND tot BETWEEN '" . $row['begintijd'] . "' AND '" . $row['eindtijd'] . "'
				";
			if ($res2 = mysql_query ($sql2)) {
				while ($row2 = mysql_fetch_assoc ($res2)) {
					echo '<p>Van: ' . $row2['van'] . ' tot:' . $row2['tot'] . '</p>';
				}
			}
		}
	}

?>
</body>
</html>

P PHP erik

15-10-2007 17:16

Heel veel respect voor Jan. Dat meen ik.

R Red Crew

15-10-2007 19:30

@ Erik: ik weet dat wes soms vervelend uit de hoek kan komen, en dat hij veel off topic spamt.

Maar nu komt hij met een serieuze vraag. jij en Frank zeggen om te normaliseren. Dat begrijp ik. Dan als wes vraagt in welk opzicht dat zijn probleem oplost geef jij geen concreet antwoord op (tot hier zit je nog goed) maar dan moet jij zijn topic niet gaan verzieken.

Sorry dat ik zo cru ben maar een moderator zou het goede voorbeeld moeten geven (geen kant en klare oplossingen) zeker wat betreft het gedrag.
Zelfs het o zo kinderachtig gedrag wat sommige mensen ook hebben. Als jij iets tegen die mensen hebt geen probleem. NEGEER ZE DAN. pgfrank reageert er tenminste nog fatsoenlijk op.

- - wes -

16-10-2007 11:08

Poging 2, nieuwe databaseindeling. zelfde vraag.

Hoe kan ik bepalen in MySQL (dus niet PHP, zoals Jan voorstelde) door welke rates (rate.id) een gesprek loopt van cdr.beg_datetime tot cdr.beg_datetime + cdr.duration in secondes en dan hier de prijs uit halen (rates bepalen is mn prio)


INSERT INTO `rate` (`id`, `rate_band`, `begin_rate`, `end_rate`) VALUES 
(1, 'Peak', '08:00:00', '18:59:59'),
(2, 'Offpeak', '19:00:00', '23:59:59'),
(3, 'Weekend_And_Night', '00:00:00', '07:59:59');


INSERT INTO `dest` (`id`, `int_name`, `nat_name`) VALUES 
(1, 'NL to Gabon', 'Gabon'),
(2, 'NL to Gabon Mobile', 'Gabon Mobiel');


INSERT INTO `tari` (`id`, `dest_id`, `call_type`, `call_charge`, `call_price`) VALUES 
(1, 1, 1, '0.0100', '0.0590'),
(2, 1, 2, '0.0100', '0.0550'),
(3, 1, 3, '0.0100', '0.0540'),
(4, 2, 1, '0.0100', '0.0640'),
(5, 2, 2, '0.0100', '0.0600'),
(6, 2, 3, '0.0100', '0.0590');


INSERT INTO `cdr` (`id`, `rec_num`, `beg_datetime`, `duration`, `cli`, `cli_si`, `telnum`, `call_ti`, `call_type`) VALUES 
(1, 24034244225, '2007-07-24 11:03:57', 62312, '31243523847', '2', '00241796464', 4, 'NL to Gabon'),
(2, 24034259231, '2007-07-26 08:02:11', 10, '31206856312', '1', '00319009395', 13, '70 EUROcent per call'),
(3, 24034265714, '2007-07-27 23:08:21', 305, '31356094537', '2', '0092915502839', 4, 'NL to Pakistan');

P PHP erik

16-10-2007 11:10

Effe serieus hè, waarom queries als je ook stored procedures kan gebruiken? Ik denk dat je het dan zo hebt opgelost.

Reageren

Inloggen om te reageren