question

CoOr-3777 avatar image
0 Votes"
CoOr-3777 asked ZoeHui-MSFT commented

Group Every 3 Years?

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-generalsql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
1 Vote"
Viorel-1 answered ZoeHui-MSFT commented

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]


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That did the trick! Thank you.

0 Votes 0 ·

Glad to hear that your issue has been resolved, if you have any other question, don't hesitate to post in Microsoft Q&A. Have a nice day!

0 Votes 0 ·