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!
| 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.
This like "gaps an islands", bat "gorizontaly" (not "verticaly"). See example
-- test data
create table test ([Acct #] int,[2022 Gift] int,[2021 Gift] int,[2020 Gift] int
,[2019 Gift] int,[2018 Gift] int ,[2017 Gift] int ,[2016 Gift] int,cnt int );
insert into test values
(546885,200 , 12 , 74 , 956 , 23 , 45 ,8559, 7)
,(253145, 40 , 5 , 26 ,null , 56 , 20 ,null, 3)
,(524865,null, 854 , 523 , 75 , 52 , 0 ,null, 0)
;
--query
select *
,case when coalesce([2022 Gift],0)>0 then
case when coalesce([2021 Gift],0)>0 then
case when coalesce([2020 Gift],0)>0 then
case when coalesce([2019 Gift],0)>0 then
case when coalesce([2018 Gift],0)>0 then
case when coalesce([2017 Gift],0)>0 then
case when coalesce([2016 Gift],0)>0 then
7 -- continue for [2015 Gift] and so on
else 6 end
else 5 end
else 4 end
else 3 end
else 2 end
else 1 end
else 0 end cntC
from test
Query result
| Acct # | 2022 Gift | 2021 Gift | 2020 Gift | 2019 Gift | 2018 Gift | 2017 Gift | 2016 Gift | cnt | cntC |
|---|---|---|---|---|---|---|---|---|---|
| 546885 | 200 | 12 | 74 | 956 | 23 | 45 | 8559 | 7 | 7 |
| 253145 | 40 | 5 | 26 | null | 56 | 20 | null | 3 | 3 |
| 524865 | null | 854 | 523 | 75 | 52 | 0 | null | 0 | 0 |
Upd1.
case expressions may only be nested to level 10 - I did not expect to encounter this limitation in my practice :)
Fast answer here
--test data
create table test ([Acct #] int,[2022 Gift] int,[2021 Gift] int,[2020 Gift] int
,[2019 Gift] int,[2018 Gift] int ,[2017 Gift] int ,[2016 Gift] int
,[2015 Gift] int,[2014 Gift] int ,[2013 Gift] int ,[2012 Gift] int
,[2011 Gift] int,[2010 Gift] int ,[2009 Gift] int ,[2008 Gift] int
,[2007 Gift] int,[2006 Gift] int ,[2005 Gift] int ,[2004 Gift] int
,[2003 Gift] int,[2002 Gift] int ,[2001 Gift] int ,[2000 Gift] int
,cnt int );
insert into test values
(546885,22,21,20 ,19,18,17,16,15,14,13,12,11,10,09,08,07,06,05,04,03,02,01,99,23)
,(253145,22,21,null,19,18,17,16,15,14,13,12,11,10,09,08,07,06,05,04,03,02,01,99, 2)
,(524865,22,21,20 ,00,18,00,16,15,14,13,12,11,10,09,08,07,06,05,04,03,02,01,99, 3)
,(524867,00,21,20 ,19,18,00,16,15,14,13,12,11,10,09,08,07,06,05,04,03,02,01,99, 0)
,(524868,null,21,20,19,18,00,16,15,14,13,12,11,10,09,08,07,06,05,04,03,02,01,99, 0)
;
-- query
select
charindex('0',
concat(
case when coalesce([2022 Gift],0)>0 then '1' else '0' end
,case when coalesce([2021 Gift],0)>0 then '1' else '0' end
,case when coalesce([2020 Gift],0)>0 then '1' else '0' end
,case when coalesce([2019 Gift],0)>0 then '1' else '0' end
,case when coalesce([2018 Gift],0)>0 then '1' else '0' end
,case when coalesce([2017 Gift],0)>0 then '1' else '0' end
,case when coalesce([2016 Gift],0)>0 then '1' else '0' end
,case when coalesce([2015 Gift],0)>0 then '1' else '0' end
,case when coalesce([2014 Gift],0)>0 then '1' else '0' end
,case when coalesce([2013 Gift],0)>0 then '1' else '0' end
,case when coalesce([2012 Gift],0)>0 then '1' else '0' end
,case when coalesce([2011 Gift],0)>0 then '1' else '0' end
,case when coalesce([2010 Gift],0)>0 then '1' else '0' end
,case when coalesce([2009 Gift],0)>0 then '1' else '0' end
,case when coalesce([2008 Gift],0)>0 then '1' else '0' end
,case when coalesce([2007 Gift],0)>0 then '1' else '0' end
,case when coalesce([2006 Gift],0)>0 then '1' else '0' end
,case when coalesce([2005 Gift],0)>0 then '1' else '0' end
,case when coalesce([2004 Gift],0)>0 then '1' else '0' end
,case when coalesce([2003 Gift],0)>0 then '1' else '0' end
,case when coalesce([2002 Gift],0)>0 then '1' else '0' end
,case when coalesce([2001 Gift],0)>0 then '1' else '0' end
,case when coalesce([2000 Gift],0)>0 then '1' else '0' end
)+'0' -- this '0' for simply check
)-1 cntC
,cnt cntT,*
from test