Historical Data
-
Hi Everyone!
This would really mean a lot to me if anyone can solve my problem...
I have a data containing the following info:
Date Item Price
01-01-2007 Book1 12.50
02-01-2007 Book1 12.50
03-01-2007 Book1 13.00
04-01-2007 Book1 13.00
05-01-2007 Book1 12.50
06-01-2007 Book1 12.50
07-01-2007 Book1 12.50
Is it possible to return the fluctuating price per item?
In such a way that the result would be like this?
Effective Date Item Price
01-01-2007 - 02-01-2007 Book1 12.50
03-01-2007 - 04-01-2007 Book1 13.00
05-01-2007 - 07-01-2007 Book1 12.50
Help!
-
If you have less than about 10000 rows a triangular join, like the following, should be reasonably efficient.
If you have more than 10000 rows you will have to look at using temp tables and/or cursors.
-- *** Test Data ***
DECLARE @t TABLE
(
[Date] datetime NOT NULL
,Item varchar(20) NOT NULL
,Price money NOT NULL
,PRIMARY KEY (Item, [Date])
)
INSERT INTO @t
SELECT '20070101', 'Book1', 12.50 UNION ALL
SELECT '20070101', 'Book2', 2.50 UNION ALL
SELECT '20070102', 'Book1', 12.50 UNION ALL
SELECT '20070102', 'Book2', 3.50 UNION ALL
SELECT '20070103', 'Book1', 13.00 UNION ALL
SELECT '20070103', 'Book2', 3.00 UNION ALL
SELECT '20070104', 'Book1', 13.00 UNION ALL
SELECT '20070104', 'Book2', 3.00 UNION ALL
SELECT '20070105', 'Book1', 12.50 UNION ALL
SELECT '20070106', 'Book1', 12.50 UNION ALL
SELECT '20070107', 'Book1', 12.50
-- *** End Test Data ***
-- The query
SELECT D.Item
,D.Price
,D.DateFrom
,MAX(D.[Date]) AS DateTo
FROM (
SELECT T1.Item
,T1.Price
,MIN(T1.[Date]) AS DateFrom
,T2.[Date]
FROM @t T1
JOIN @t T2
ON T1.Item = T2.Item
AND T1.[Date] <= T2.[Date]
WHERE T1.Price = ALL (
SELECT T3.Price
FROM @t T3
WHERE T3.Item = T1.Item
AND T3.[Date] BETWEEN T1.[Date] AND T2.[Date]
)
GROUP BY T1.Item, T1.Price, T2.[Date]
) D
GROUP BY D.Item, D.Price, D.DateFrom
ORDER BY Item, DateFrom
-
Thanks a lot! And I thought the only possible way to do this is
through the use of cursor/temp tables. 🙂
Again, thanks for the help! It was highly appreciated.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply