Re: SQL Statement help
Barry Zubel wrote:
> I'm trying to construct an SQL statement that will give me the time period between two dates.
> I basically want to find the average period between occurrences for a particular customer
I usually tackle this kind of problem in small steps, verifying the results on the way:
USE test;
# A table ...
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
cust INT NOT NULL,
adate DATE
);
# ... and some test data
INSERT INTO foo (id, cust, adate) VALUES
(NULL, 1, '2005-01-01'),
(NULL, 1, '2005-01-03'),
(NULL, 1, '2005-02-01'),
(NULL, 1, '2005-01-07'),
(NULL, 1, '2005-01-11'),
(NULL, 2, '2005-01-01'),
(NULL, 2, '2005-02-03'),
(NULL, 2, '2005-03-07'),
(NULL, 2, '2005-04-11');
# Show all the records
SELECT * from foo;
# Show the FROM_DATE, TO_DATE pairs for all CUSTOMERs
SELECT
f1.cust AS CUSTOMER,
f1.adate FROM_DATE,
(SELECT
f2.adate
FROM foo AS f2
WHERE
f2.cust = f1.cust AND
f2.adate > f1.adate
ORDER BY f2.adate
LIMIT 1
) AS TO_DATE
FROM foo AS f1;
# Show the date difference in days between a FROM_DATE, TO_DATE pair
SELECT
f1.cust AS CUSTOMER,
-DATEDIFF(
f1.adate,
(SELECT
f2.adate
FROM foo AS f2
WHERE
f2.cust = f1.cust AND
f2.adate > f1.adate
ORDER BY f2.adate
LIMIT 1)
) AS DIFF
FROM foo AS f1;
# Finaly, take the average per CUSTOMER
SELECT
f1.cust,
AVG(-DATEDIFF(
f1.adate,
(SELECT
f2.adate
FROM foo AS f2
WHERE
f2.cust = f1.cust AND
f2.adate > f1.adate
ORDER BY f2.adate
LIMIT 1)
)) AS AVERAGE
FROM foo AS f1
GROUP BY cust;
(I apologize for the bad formatting - forum maintainers, are you listening? ;-)
--
felix