SQL help.

Database
Enthusiast

SQL help.

Hi All,

I have data like this in a table Market           

MARKET   PRODUCT    STARTDT   ENDDT    PROMOFLAG

1               1            5/2/2015    5/8/2015        Y  

1               1            5/9/2015    5/15/2015      Y  

1               1            5/16/2015  5/22/2015      N  

1               1            5/23/2015  5/29/2015      Y  

I need the output like below      

MARKET   PRODUCT    STARTDT   ENDDT      PROMOFLAG

1                  1          5/2/2015   5/15/2015        Y  

1                  1         5/16/2015  5/22/2015        N  

1                  1         5/23/2015  12/31/9999      Y

I want to calculate the min(STARTDT) and max(ENDDT) of the PROMOFLAG till the value changes in PROMOFLAG(Y/N).

Regards,

Mahesh

2 REPLIES
Teradata Employee

Re: SQL help.

Hi Mahesh,

Just use a cross join and a filter

selectfrom "public"."sqlForum" t1, "public"."sqlForum" t2

where t1."PROMOFLAG" != t2."PROMOFLAG"

and t1."MARKET" = t2."MARKET"

and t1." PRODUCT" = t2." PRODUCT"

and t1."STARTDT" = t2." ENDDT" +1

and build the Min and max in an outer query.

Regards,

Jens

Enthusiast

Re: SQL help.

Something like below will work

SELECT

t1.Market

,t1.Product

,t1.Start_Dt

,t1.End_Dt

, CASE WHEN COALESCE(t1.Promoflag,'X') = COALESCE(t2.Promoflag,'X') AND t1.Rno>1 THEN 0 ELSE 1 END AS data_chk

,COALESCE( MAXIMUM ( t1.End_dt ) OVER ( PARTITION BY t1.Market,t1.Product

            ORDER BY t1.Start_Dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ) ,  t1.End_dt ) AS NEW_VALID_TO_DTTM,

FROM

(SELECT Market,Product,Start_Dt,End_Dt ,Promoflag,ROW_NUMBER() OVER (PARTITION BY Market,Product ORDER BY Start_Dt ASC) AS rno FROM Market ) t1

LEFT JOIN

(SELECT Market,Product,Start_Dt,End_Dt ,Promoflag,ROW_NUMBER() OVER (PARTITION BY Market,Product ORDER BY Start_Dt ASC) AS rno FROM Market ) t2

ON t1.Market=t2.Market

AND t2.Product=t2.Product

AND t1.rno=t2.rno

WHERE data_chk=1

Sorry i didnt test with Data... Please check and let me know if it works..