De PHPhulp kroeg
[SQL] Between
Ik geraak er even niet meer uit (wellicht te lang bezig geweest :-) ). Ik heb de volgende query:
SELECT DISTINCT c.cus_id, c.firstname, c.lastname, c.credit, c.address, c.postal, c.city FROM customer_list AS c, transactions AS t WHERE t.transdate NOT BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 18 MONTH) AND DATE(NOW()) AND t.to_customer != '1' AND t.from_customer = c.cus_id ORDER BY c.lastname ASC
De bedoeling is om de klantgegevens uit de database te halen die geen transacties hebben gehad in de periode van de 18 afgelopen maanden. Echter krijg ik ook de resultaten terug van klanten die wel transacties hebben gehad in de afgelopen maanden en transacties hebben gehad voor de 18 maanden.
Aangezien mijn SQL kennis niet al te groot is, zou ik het zo in PHP kunnen omschrijven:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?php
$cus_query = mysql_query("SELECT cus_id, firstname, lastname, credit, address, postal, city FROM customer_list ORDER BY lastname ASC");
while ($cus_result = mysql_fetch_assoc($cus_query))
{
$trans_query = mysql_query("SELECT FROM transactions WHERE from_customer = '" .
$cus_result['cus_id'] .
"' AND transdate BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 18 MONTH) AND DATE(NOW()) AND to_customer != '1'");
if (mysql_num_rows($trans_query) == 0)
{
echo '<tr>
<td>' . $cus_result['cus_id'] . '</td>
<td>' . $cus_result['firstname'] . '</td>
<td>' . $cus_result['lastname'] . '</td>
<td>' . $cus_result['address'] . '</td>
<td>' . $cus_result['postal'] . '</td>
<td>' . $cus_result['city'] . '</td>
<td>' . $cus_result['credit'] . '</td>
</tr>';
}
}
?>
$cus_query = mysql_query("SELECT cus_id, firstname, lastname, credit, address, postal, city FROM customer_list ORDER BY lastname ASC");
while ($cus_result = mysql_fetch_assoc($cus_query))
{
$trans_query = mysql_query("SELECT FROM transactions WHERE from_customer = '" .
$cus_result['cus_id'] .
"' AND transdate BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 18 MONTH) AND DATE(NOW()) AND to_customer != '1'");
if (mysql_num_rows($trans_query) == 0)
{
echo '<tr>
<td>' . $cus_result['cus_id'] . '</td>
<td>' . $cus_result['firstname'] . '</td>
<td>' . $cus_result['lastname'] . '</td>
<td>' . $cus_result['address'] . '</td>
<td>' . $cus_result['postal'] . '</td>
<td>' . $cus_result['city'] . '</td>
<td>' . $cus_result['credit'] . '</td>
</tr>';
}
}
?>
Dit werkt ook, maar natuurlijk niet netjes en de performance op deze manier is ook niet al te best (> 100.000 transacties).
Kan iemand mij een schop in de goede richting geven om dit enkel met één SQL query af te kunnen?
B.V.D.
Niels.
Gewijzigd op 14/03/2013 14:38:32 door Niels van K
@ Ward: Goed idee, maar dan krijg je het probleem wat Ger beschrijft.
Ik heb vanmiddag nog even gepuzzeld, maar ik krijg het niet voor elkaar met alleen SQL queries. Met PHP wil het wel lukken, alleen de performance is 0.
Kan iemand mij een duwtje in de goede richting geven?
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT c.cus_id, c.lastname
FROM customers c
LEFT JOIN transactions t
ON t.cus_id = c.cus_id
AND t.transdate BETWEEN CURRENT_DATE AND CURRENT_DATE - INTERVAL 18 MONTH
WHERE t.cus_id IS NULL
FROM customers c
LEFT JOIN transactions t
ON t.cus_id = c.cus_id
AND t.transdate BETWEEN CURRENT_DATE AND CURRENT_DATE - INTERVAL 18 MONTH
WHERE t.cus_id IS NULL
Gewijzigd op 07/04/2013 15:52:50 door Ger van Steenderen
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT c.cus_id, c.lastname
FROM customer_list AS c
LEFT JOIN transactions AS t
ON t.from_customer = c.cus_id
AND t.transdate BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 18 MONTH) AND DATE(NOW())
WHERE t.from_customer IS NULL
FROM customer_list AS c
LEFT JOIN transactions AS t
ON t.from_customer = c.cus_id
AND t.transdate BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 18 MONTH) AND DATE(NOW())
WHERE t.from_customer IS NULL
Zal hem dan moeten worden. Echter loopt de DB hierop vast. Waarom gebruik je de IS NULL functie in de WHERE clause?
Heeft een klant altijd minimaal 1 transactie kun je de query simpeler maken door een INNER JOIN en een GROUP BY :
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT c.cus_id, c.last_name, MAX(t.transdate) lasttrans
FROM
customer c
INNER JOIN
transactions t
ON t.from_customer = c.cus_id
GROUP BY
c.cus_id, c.last_name
HAVING MAX(t.transdate) < CURRENT_DATE - INTERVAL 18 MONTH
FROM
customer c
INNER JOIN
transactions t
ON t.from_customer = c.cus_id
GROUP BY
c.cus_id, c.last_name
HAVING MAX(t.transdate) < CURRENT_DATE - INTERVAL 18 MONTH
Gewijzigd op 07/04/2013 16:15:04 door Ger van Steenderen
Echter, iedere klant betaalt lidmaatschap aan klant nummer 1 elke maand. Deze transacties moeten niet meegeteld worden.
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT c.cus_id, c.lastname, MAX(t.transdate) lasttrans
FROM
customer_list c
INNER JOIN
transactions t
ON t.from_customer = c.cus_id AND t.to_customer != 1
GROUP BY
c.cus_id, c.lastname
HAVING MAX(t.transdate) < CURRENT_DATE - INTERVAL 18 MONTH
FROM
customer_list c
INNER JOIN
transactions t
ON t.from_customer = c.cus_id AND t.to_customer != 1
GROUP BY
c.cus_id, c.lastname
HAVING MAX(t.transdate) < CURRENT_DATE - INTERVAL 18 MONTH
Het enige 'nadeel' wat ik van je begrijp, is dat klanten die _geen_ transacties hebben, worden ook niet meegenomen in het lijstje? Is dit ook nog te verhelpen?
Gewijzigd op 07/04/2013 16:44:25 door Niels van K
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT c.cus_id, c.lastname, MAX(t.transdate) lasttrans
FROM
customer_list c
INNER JOIN
transactions t
ON t.from_customer = c.cus_id AND t.to_customer != 1 AND active = 'Y'
GROUP BY
c.cus_id, c.lastname
HAVING MAX(t.transdate) < CURRENT_DATE - INTERVAL 18 MONTH OR MAX(t.transdate) IS NULL
FROM
customer_list c
INNER JOIN
transactions t
ON t.from_customer = c.cus_id AND t.to_customer != 1 AND active = 'Y'
GROUP BY
c.cus_id, c.lastname
HAVING MAX(t.transdate) < CURRENT_DATE - INTERVAL 18 MONTH OR MAX(t.transdate) IS NULL
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT c.cus_id, c.lastname, MAX(t.transdate) lasttrans
FROM
customer_list c
INNER JOIN
transactions t
ON t.from_customer = c.cus_id AND t.to_customer != 1 AND active = 'Y'
GROUP BY
c.cus_id, c.lastname
HAVING MAX(t.transdate) < CURRENT_DATE - INTERVAL 18 MONTH
FROM
customer_list c
INNER JOIN
transactions t
ON t.from_customer = c.cus_id AND t.to_customer != 1 AND active = 'Y'
GROUP BY
c.cus_id, c.lastname
HAVING MAX(t.transdate) < CURRENT_DATE - INTERVAL 18 MONTH