All Forums Database
maheshkanni 9 posts Joined 02/12
29 Jun 2015
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.

 

manib0907 61 posts Joined 04/15
29 Jun 2015

(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.

Cheers,
Mani

yuvaevergreen 93 posts Joined 07/09
30 Jun 2015

Use recursive...

manib0907 61 posts Joined 04/15
30 Jun 2015

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

Cheers,
Mani

maheshkanni 9 posts Joined 02/12
01 Jul 2015

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

You must sign in to leave a comment.