I have a table with columns of product and sold date, and want to query the statistics of sold interval of each product group(max interval, min interval ...) , is there any good advice to make it, appreciate~
Prod SaleDate
-------------------
A 2013-02-05
D 2013-02-24
B 2013-03-01
A 2013-03-12
D 2013-03-22
A 2013-04-03
D 2013-04-08
. . .
Sold interval means days interval between two adjacent date.
Sold interval of A: DATEDIFF(d, '2013-02-05', '2013-03-12') DATEDIFF(d, '2013-03-12', '2013-04-03') ...
Sold interval of D: DATEDIFF(d, '2013-02-24', '2013-03-22') DATEDIFF(d, '2013-03-22', '2013-04-08')
and I want get the average, max and min value of sold interval.
Prod IntervalAvg IntervalMax IntervalMin
-----------------------------------------------------
A xxx xxx xxx
B xxx xxx xxx
C
. . .
Thanks Kahn's answer give me a hint. I re-implement my code for sql server 2000 by "left outer join".
DECLARE @DATA TABLE (Prod CHAR(1), SaleDate SMALLDATETIME)
INSERT INTO @DATA VALUES ('A','2013-02-05')
INSERT INTO @DATA VALUES ('D','2013-02-24')
INSERT INTO @DATA VALUES ('B','2013-03-01')
INSERT INTO @DATA VALUES ('A','2013-03-12')
INSERT INTO @DATA VALUES ('D','2013-03-22')
INSERT INTO @DATA VALUES ('A','2013-04-03')
INSERT INTO @DATA VALUES ('D','2013-04-08')
SELECT
t.Prod
, MAX(t.Interval) IntervalMax
, MIN(t.Interval) IntervalMin
, AVG(t.Interval) IntervalAvg
FROM
(
SELECT t1.*, DATEDIFF(dd, MAX(t2.SaleDate), t1.SaleDate) Interval
FROM @DATA t1
LEFT OUTER JOIN @DATA t2 ON t1.Prod = t2.Prod AND t1.SaleDate > t2.SaleDate
GROUP BY t1.Prod, t1.SaleDate
)t
GROUP BY t.Prod
ORDER BY t.Prod