Group Every 3 Years?

Chris Bridge 21 Reputation points
2021-03-15T21:21:23.35+00:00

Hi Everyone. I'm having trouble trying to get my data to display properly. For every three years, I need to add the year to a new group. Here's an example:

2018 - 1
2019 - 1
2020 - 1
2021 - 2
2022 - 2
2023 - 2
2024 - 3
2025 - 3
2026 - 3
2027 - 4
2028 - 4
2029 - 4

Any help would be greatly appreciated. Thank you.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,348 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,875 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 114.7K Reputation points
    2021-03-15T21:31:05.88+00:00

    Guessing that you have a table like this:

    declare @years as table ( [year] int )
    
    insert @years values
    ( 2018 ),
    ( 2019 ),
    ( 2020 ),
    ( 2021 ),
    ( 2022 ),
    ( 2023 ),
    ( 2024 ),
    ( 2025 ),
    ( 2026 ),
    ( 2027 ),
    ( 2028 ),
    ( 2029 )
    

    check this query:

    select *, (dense_rank() over (order by [year]) - 1) / 3 + 1 as [group]
    from @years
    order by [year]
    
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful