2023-07-08

SQL Server How to count consecutive fields across a row with a start point?

I have a table that for headers has account number, 2022 gift, 2021 gift, 2020 gift, 2019 gift, etc. through to 2004 gift (example below only shows a few columns for visual).

I am looking for code that will count how many consecutive years the donor gave a gift starting with the 2022 gift column and going back from there. So for instance if they gave a gift every year between 2022 and 2016 it should set the count equal to 7. If they didn't give a gift in 2022 (regardless of giving in other years), it would return 0. If they gave a gift in 2022, 2021, 2020 and then skipped 2019 but had given in 2018, it would return 3. Sometimes a field without a gift will be null but other times it will be '0' (both of which would be considered a gap; in case that information is helpful).

Any help would be greatly appreciated. Thanks in advance!

Table Example

Acct # 2022 Gift 2021 Gift 2020 Gift 2019 Gift 2018 Gift 2017 Gift 2016 Gift Count
546885 200 12 74 956 23 45 8559 7
253145 40 5 26 56 20 3
524865 854 523 75 52 0 0

I thought I could do a bulky case when for every possibility (ex. case when 2022 gift <1 then '0' when (all years) > 0 then '20' when 2022 gift >0 and 2021 gift >0 and 2020 gift >0, etc. but I know there has to be a better way to do it than that. My SQL skills are pretty low so my hope is that there is a better way to do it than the huge case when with every possibility.



No comments:

Post a Comment