question

siera_gld avatar image
siera_gld asked

Continous Changes by date

I am trying to create a report that reflects a continuous stream of price changes by date and item. I have a table full of prices their changes by date. I need to compare the change in price over the next price in sequence. Also - i am selecting a date range so every change within the range of dates should appear and then according to the date, the new price will become the old price whenever a newer date with a prc change comes in effect. When I created a procedure, old price eseems to always revert to the date that is associated with the beg dt. I am really not sure the best way to approach this. CREATE TABLE #TEMP ( ITEM CHAR(7), PRC DECIMAL(13,2), PRC_EFF_DT DATETIME, PRC_END_DT DATETIME) INSERT INTO #temp VALUES( '2134880',1.14,'2011-01-12','2011-03-09') INSERT INTO #temp VALUES('2134880',1.14,'2011-03-10' ,'2011-03-11') INSERT INTO #temp VALUES('2134898',1.95,'2011-01-12' ,'2011-03-09') INSERT INTO #temp VALUES('2134898',1.95,'2011-03-10' ,'2011-03-11') INSERT INTO #temp VALUES('2134880',23.37,'2011-04-14' ,'2099-12-31') INSERT INTO #temp VALUES('2134880',25.39,'2011-03-12' ,'2011-04-13') INSERT INTO #temp VALUES('2134898',46.75,'2011-04-14' ,'2099-12-31') INSERT INTO #temp VALUES('2134898',50.79,'2011-03-12' ,'2011-04-13') DECLARE @BEG_DT DATETIME SET @BEG_DT = '2011-01-01' DECLARE @END_DT DATETIME SET @END_DT = '2011-04-26' SELECT ITEM, PRC, PRC_EFF_DT, PRC_END_DT, ROW_NUMBER() OVER(PARTITION BY PRC, ITEM ORDER BY PRC_EFF_DT) PRC_ID INTO #PRC_TEMP FROM #TEMP WHERE PRC_EFF_DT BETWEEN @BEG_DT AND @END_DT CREATE TABLE #TMP_FUT (ITEM CHAR(7) ,PRC DECIMAL(13,2) ,PRC_EFF_DT DATETIME ,PRC_END_DT DATETIME ,PRIMARY KEY(ITEM,PRC_EFF_DT) ) INSERT INTO #TMP_FUT SELECT ITEM ,(ISNULL(PRC,0)) AS PRC ,MIN(PRC_EFF_DT) ,MAX(PRC_END_DT) FROM #PRC_TEMP WHERE PRC_EFF_DT BETWEEN CONVERT(CHAR(10), @BEG_DT, 101) AND CONVERT(CHAR(10), @END_DT, 101) GROUP BY ITEM, PRC CREATE TABLE #TMP_CURR (ITEM CHAR(7) ,PRC DECIMAL(13,2) ,PRC_EFF_DT DATETIME ,PRC_END_DT DATETIME ,PRIMARY KEY(ITEM,PRC_EFF_DT) ) INSERT INTO #TMP_CURR SELECT h.ITEM ,(ISNULL(h.PRC,0)) AS PRC ,MIN(h.PRC_EFF_DT) ,MAX(h.PRC_END_DT) FROM #PRC_TEMP h WHERE CONVERT(CHAR(10), @BEG_DT,101) between PRC_EFF_DT AND PRC_END_DT GROUP BY ITEM, PRC SELECT DISTINCT f.ITEM ,f.PRC_EFF_DT ,f.PRC_END_DT ,isNull(h.PRC,0) AS OLD_PRC ,isNull(f.PRC,0) AS NEW_PRC ,CASE WHEN isNull(h.PRC, 0) = 0 THEN '1' ELSE (isNull(f.prc,0) - isNull(h.prc,0))/h.PRC END as PCT_CHG ,CASE WHEN isNull(h.PRC, 0) = 0 THEN 'Add' WHEN isNull(c.PRC, 0) = 0 THEN 'Removal' WHEN f.PRC>h.PRC THEN 'Price Increase' WHEN h.PRC>f.PRC THEN 'Price Decrease' END AS NOTES FROM #TMP_FUT AS f LEFT JOIN #TMP_CURR AS h ON h.ITEM = f.ITEM WHERE ISNULL(f.PRC,0) isNull(h.PRC, 0) and ((isNull(f.prc,0) - isNull(h.prc,0))/h.PRC) > .02 and isNull(h.PRC, 0) 0
t-sqlpartitioningdates
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
WilliamD avatar image
WilliamD answered
If I understood correctly, you want to pack rows that have a contiguous price validity period, then show the price change between each price grouping. With the sample data you provided you expect the following results: item prc_eff_dt prc_end_dt old_price new_price pct_chg notes ------- ----------- ----------- ---------- --------- -------- -------------- 2134880 2011-01-12 2011-03-11 0.00 1.14 1.00 Add 2134880 2011-03-12 2011-04-13 1.14 25.39 2127.19 Price Increase 2134880 2011-04-14 2099-12-31 25.39 23.37 -7.96 Price Decrease 2134898 2011-01-12 2011-03-11 0.00 1.95 1.00 Add 2134898 2011-03-12 2011-04-13 1.95 50.79 2504.62 Price Increase 2134898 2011-04-14 2099-12-31 50.79 46.75 -7.95 Price Decrease To do this you start with the row packing using ROW_NUMBER() to identify the groups, then find the max and min dates for these groups, then give them a sequence value to find the current and next row. The final select will then get the current row, join to the next row via item and sequence number and do the price comparison. This should be what you are looking for: DECLARE @BEG_DT datetime SET @BEG_DT = '2011-01-01' DECLARE @END_DT datetime SET @END_DT = '2011-04-26' ; WITH price AS (SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - ROW_NUMBER() OVER (PARTITION BY item ORDER BY prc_eff_dt) rn FROM @temp WHERE prc_eff_dt BETWEEN @BEG_DT AND @END_DT), priceagg AS (SELECT item, prc, MIN(prc_eff_dt) prc_eff_dt, MAX(prc_end_dt) prc_end_dt FROM price GROUP BY item, prc), prices AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY item ORDER BY prc_eff_dt) rn FROM priceagg) SELECT cur.item, cur.prc_eff_dt, cur.prc_end_dt, (ISNULL(lst.prc, 0)) AS old_price, cur.prc AS new_price, CAST(CASE WHEN lst.prc IS NULL THEN 1 ELSE ((cur.prc - (ISNULL(lst.prc, 0))) / lst.prc) * 100 END AS decimal(10, 2)) pct_chg, CASE WHEN lst.prc IS NULL THEN 'Add' WHEN cur.prc = 0 THEN 'Removal' WHEN (ISNULL(lst.prc, 0)) > cur.prc THEN 'Price Decrease' WHEN (ISNULL(lst.prc, 0)) < cur.prc THEN 'Price Increase' WHEN (ISNULL(lst.prc, 0)) = cur.prc THEN 'Unchanged' END notes FROM prices cur LEFT JOIN prices lst ON cur.rn = lst.rn + 1 AND cur.item = lst.item
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

On The Money! Thank you very much..
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.