February 4, 2021 at 12:16 pm
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
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;
February 4, 2021 at 9:22 pm
Thanks
This will fit nicely into my work
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy