Count consecutive number of years of orders

• cg 23106

  SSC Enthusiast

  Points: 173

  More actions

  February 4, 2021 at 12:16 pm

  #3840911

  Hi

  I like to get the number of consecutive years a customer have placed ordres starting from current year or last year

  Input

  2015

  2016

  2019

  2020

  2021

  Result = 3

  I hope someone can help with this.

   

• DesNorton

  SSC-Insane

  Points: 24516

  More actions

  February 4, 2021 at 12:38 pm

  #3840917

  Are you truly still using SQL 2008?  I can't remember if ROW_NUMBER() was introduced yet.

  If it was available in 2008, then this should help

  DECLARE @CustomerOrder table (
      CustomerID int NOT NULL
    , OrderYear  int NOT NULL
  );
  
  INSERT INTO @CustomerOrder ( CustomerID, OrderYear )
  VALUES  ( 1, 2015 )
        , ( 1, 2016 )
        , ( 1, 2019 )
        , ( 1, 2020 )
        , ( 1, 2021 )
        , ( 2, 2017 )
        , ( 2, 2020 );
  
  WITH cteGroups AS (
        SELECT  co.CustomerID
              , co.OrderYear
              , Grp = co.OrderYear - ROW_NUMBER() OVER (PARTITION BY co.CustomerID ORDER BY co.OrderYear)
        FROM    @CustomerOrder AS co
  )
  , ctePerriods AS (
        SELECT g.CustomerID
             , StartYear = MIN(g.OrderYear)
             , EndYear   = MAX(g.OrderYear)
             , NumYears  = MAX(g.OrderYear) - MIN(g.OrderYear) +1
             , rn = ROW_NUMBER() OVER (PARTITION BY g.CustomerID ORDER BY MAX(g.OrderYear) - MIN(g.OrderYear) DESC, MAX(g.OrderYear) DESC)
        FROM cteGroups AS g
        GROUP BY g.CustomerID, g.Grp
  )
  SELECT p.CustomerID, p.StartYear, p.EndYear, p.NumYears
  FROM  ctePerriods AS p
  WHERE p.rn = 1;
  

  ---

  How to post data/code on a forum to get the best help.

• cg 23106

  SSC Enthusiast

  Points: 173

  More actions

  February 4, 2021 at 9:22 pm

  #3841159

  Thanks

  This will fit nicely into my work