I am looking for some sort of 'rank function' taking care of the gap between days.

• avanrijn

  Grasshopper

  Points: 22

  More actions

  May 24, 2015 at 8:50 am

  #300955

  hello: working on sqlserver 2008 R2

  CREATE TABLE OrderRanking

  (

  OrderID INT IDENTITY(1,1) NOT NULL,

  CustomerID INT,

  OrderDate date

  )

  INSERT OrderRanking (CustomerID, OrderDate)

  SELECT 1, '01-01-2015'

  UNION all

  SELECT 1, '01-01-2015'

  UNION all

  SELECT 2, '02-01-2015'

  UNION all

  SELECT 2, '02-01-2015'

  UNION all

  SELECT 2, '05-01-2015'

  UNION all

  SELECT 2, '05-01-2015'

  SELECT *,

  ROW_NUMBER() OVER (ORDER BY OrderDate ) AS RN,

  ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate ) AS RNP,

  RANK() OVER (ORDER BY OrderDate ) AS R,

  RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate ) AS RP,

  DENSE_RANK() OVER (ORDER BY OrderDate ) AS DR,

  DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate ) AS DRP

  FROM OrderRanking

  ORDER BY OrderID

  Looks fine but what I need is DRP with this:

  CustomerID OrderDate 'DRP taking care of the gap in the days'

  1 '01-01-2015' 1

  1 '01-01-2015' 1

  2 '02-01-2015' 1

  2 '02-01-2015' 1

  2 '05-01-2015' 4

  2 '05-01-2015' 4

  Hope anyone can show some light on this.

  Regards,

  Arthur

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  May 24, 2015 at 9:15 am

  #1800257

  Not totally sure what you're after but this may work

  SELECT *,

  DATEDIFF(Day,MIN(OrderDate) OVER(PARTITION BY CustomerID),OrderDate) + 1

  FROM OrderRanking

  ORDER BY OrderID

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• avanrijn

  Grasshopper

  Points: 22

  More actions

  May 24, 2015 at 9:27 am

  #1800259

  Thank you Marc. This is what I was looking for. Regards from Amsterdam.