to find plan id and row number from a table

Database
Visitor

to find plan id and row number from a table

i have a table with following fields

policy_id     s1     E1

1001          150    200

1001           300   400

1001           400   500

1002           200   750

1002            750   350

 

here s1 should match with e1 if yes than plan id 

my output should be p1 . later s1  2 nd record is checked with e1's first  & 2 nd record if match thaen keep same plan id p1..else p2...so in this way each record needs to be checked with its corresponding record and previous record.and row no. must be changed if match is found to 1 else 0..help

policy_id     s1     E1     paln id    row no

1001          150    200    p1                1

1001           300   400    p2                1

1001           400   500    p2                2

1002           200   750    p3                1

1002            750   350   p3                2

2 REPLIES
Senior Supporter

Re: to find plan id and row number from a table

Hi Rohini,

 

Could you add some more info to this? Perhaps you could provide some data samples for each stage of your processing (name all the columns that you refer to in your description) so that we might be able ot better understand what you're trying to achieve.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Highlighted
Senior Apprentice

Re: to find plan id and row number from a table

I assume you got a column which can be used to sort your data correctly:

SELECT ...
   Sum(flag)
   Over (PARTITION BY policy_id
         ORDER BY whatever
         ROWS Unbounded Preceding) + 1
FROM
 (
   SELECT ...
      CASE WHEN Min(E1) -- check if it's the same value in current and previous row
                 Over (PARTITION BY policy_id
                       ORDER BY whatever
                       ROWS BETWEEN 1 Preceding AND 1 Preceding) = s1
           THEN 1 -- same -> increase 
           ELSE 0
      END AS flag
   FROM tab
 ) AS dt