Generating Rank value for each SET of flags

Database
Enthusiast

Generating Rank value for each SET of flags

Hi All,

I need a data like below, I want to generate a rank value for each set of Flags.

seq market  product startdt  enddt  flag
1 10 100 5/10/2015 5/16/2015 N
2 10 100 5/17/2015 5/23/2015 N
3 10 100 5/24/2015 5/30/2015 Y
4 10 100 5/31/2015 6/6/2015 Y
5 10 100 6/7/2015 6/13/2015 Y
6 10 100 6/14/2015 6/20/2015 N
7 10 100 6/21/2015 6/27/2015 Y

I want the out put to be

seq market  product startdt  enddt  flag   flag_rank
1 10 100 5/10/2015 5/16/2015 N  1
2 10 100 5/17/2015 5/23/2015 N  1
3 10 100 5/24/2015 5/30/2015 Y  2
4 10 100 5/31/2015 6/6/2015 Y  2
5 10 100 6/7/2015 6/13/2015 Y  2
6 10 100 6/14/2015 6/20/2015 N  3
7 10 100 6/21/2015 6/27/2015 Y  4
8 10 100 6/21/2015 6/27/2015 Y        4

I want this to calculate min(startdt) and max(enddt) in each of Y and N.
4 REPLIES
Enthusiast

Re: Generating Rank value for each SET of flags

(SELECT SEQ,MARKET,PRODUCT,STRT_DT,ENDDT,FLAG,

SUM(CHANGE_FLG)OVER PARTITION BY MARKET,PRODUCT ORDER BY STARTDT,ENDDT) AS CHANGE_FLG

(SELECT SEQ,MARKET,PRODUCT,STRT_DT,ENDDT,FLAG,PREV_FLAG,

CASE WHEN FLAG=PREV_FLAG THEN 0 ELSE 1 END AS CHANGE_FLG

FROM

(SELECT SEQ,MARKET,PRODUCT,STRT_DT,ENDDT,FLAG,

COALESCE(MAX(FLAG)OVER (PARTITION BY MARKET,PRODUCT ORDER BY STARTDT,ENDDT ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) ,FLAG)

AS PREV_FLAG) A))B)C

this will find you the sets that you require and u can use that in the partition by clause to calculate min and max date.

Enthusiast

Re: Generating Rank value for each SET of flags

Use recursive...

Enthusiast

Re: Generating Rank value for each SET of flags

Jus a small edit to my code , its SUM(CHANGE_FLG)OVER PARTITION BY MARKET,PRODUCT ORDER BY STARTDT,ENDDT rows unbounded preceding)

Cheers,

mani

Enthusiast

Re: Generating Rank value for each SET of flags

Thank you Mani. Its working perfectly fine for my scenario.