Rank based Query- Help

• Ram:)

  SSCrazy

  Points: 2246

  More actions

  April 12, 2011 at 10:46 am

  #249284

  Hi,

  Please help on this. Here is my table

  CREATE TABLE #TABLE (SNO INT,AMT NUMERIC(16,2),RANK INT)

  INSERT INTO #TABLE(SNO,AMT)

  SELECT 1,10000

  UNION

  SELECT 2,2000

  UNION

  SELECT 3,4500

  UNION

  SELECT 4,1000

  UNION

  SELECT 5,40000

  UNION

  SELECT 6,500

  UNION

  SELECT 7,9500

  UNION

  SELECT 8,1200

  Here is the Result needed.

  110000.00 1

  22000.002

  34500.002

  41000.002

  540000.00 3

  6500.004

  79500.004

  81200.005

  The rank should be increased for every sum of 10000 .

  Regards

  Ram..

  🙂

• LutzM

  SSC Guru

  Points: 107049

  More actions

  April 12, 2011 at 3:49 pm

  #1310736

  You might want to have a look at Jeffs Running Total article[/url].

  Based on the concept described in this article you could use a conditional increment instead of the running total value to store it in your target column.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]