Archives of the TeradataForum

Message Posted: Mon, 24 Jun 2002 @ 13:35:44 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Monday, June 24, 2002 08:01 -->

Hello All,

Given a set of data like this.....

STORE   UPC     UPC_EFF_DT      PRICE_EFF_DT PRICE_TERM_DT      PRICE_AMT
356     16      1993-12-01      2001-09-29      2001-11-02      1.19
356     16      1993-12-01      2001-11-03      2001-11-09      0.99
356     16      1993-12-01      2002-02-21      2002-02-27      1.19
356     16      1993-12-01      2002-02-28      2002-03-06      0.79
356     16      1993-12-01      2002-03-07      2002-03-13      1.19
356     16      1993-12-01      2002-03-14      2002-03-20      1.19
356     16      1993-12-01      2002-03-21      2002-03-30      0.99

I want to consolidate contiguous price_term_dt to price_eff_dt ranges if the price has not changed. At first glance this seemed like the right way to achieve the result.......

select  store
        ,upc
        ,upc_eff_dt
        ,min(price_eff_dt)
        ,max(price_term_dt)
        ,price_amt
from store_upc_price
group by 1,2,3,6

The result.....

STORE   UPC     UPC_EFF_DT      PRICE_EFF_DT PRICE_TERM_DT      PRICE_AMT
356     16      1993-12-01      2001-09-29      2002-03-20      1.19
356     16      1993-12-01      2001-11-03      2002-03-30      0.99
356     16      1993-12-01      2002-02-28      2002-03-06      0.79

This isn't correct, because the group by included the price that was the same at different periods of time. The data has to be evaluated in date order.

How do I do that in SQL?

Thanks for your help.

	<Prev	Next>		<<First	<Prev	Next>	Last>>