Post reply

Count consecutive number of years of orders

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

     

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

  • February 4, 2021 at 9:22 pm

    #3841159

    Thanks

    This will fit nicely into my work

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply