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

wko9yo5t 于 2023-08-02 发布在 SQL Server

关注(0)|答案(2)|浏览(113)

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.

sql-server

来源：https://stackoverflow.com/questions/76630117/sql-server-how-to-count-consecutive-fields-across-a-row-with-a-start-point

2条答案

按热度按时间

j9per5c41#

Just for completeness, here is how to do it via XQuery.

It is completely generic and concise, doesn't matter how many columns table has.

I made two modifications to the initial setup:

Modified column names.
Replaced NULLs with zeros.

Notable points:

First CROSS APPLY converts each row into XML format. Plus it is adding extra <Gift>0</Gift> as a last node.
Second CROSS APPLY is calculating pos that holds a position of the first column with zero value.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE ([AcctNo] INT,[Gift_2022] INT,[Gift_2021] INT,[Gift_2020] INT
,[Gift_2019] INT,[Gift_2018] INT ,[Gift_2017] INT ,[Gift_2016] INT);
INSERT INTO @tbl VALUES
(546885,200 ,  12 , 74  , 956 , 23 , 45 ,8559),
(253145, 40 ,   5 , 26  ,0 , 56 , 20 ,0),
(524865,0, 854 , 523 , 75  , 52 , 0  ,0);
-- DDL and sample data population, end

SELECT t.*
    , result = pos - 1
FROM @tbl AS t
CROSS APPLY (SELECT t.*, Gift=0 FOR XML PATH(''), TYPE, ROOT('root')) AS t1(xmldata)
CROSS APPLY (SELECT t1.xmldata.query('
    for $x in /root/*[contains(local-name(),"Gift")][text()="0"][1]
    let $pos := count(root/*[contains(local-name(),"Gift")][. << $x]) + 1
    return $pos
    ').value('.', 'INT')) AS t2(pos);

Result

AcctNo	Gift_2022	Gift_2021	Gift_2020	Gift_2019	Gift_2018	Gift_2017	Gift_2016	result
546885	200	12	74	956	23	45	8559	7
253145	40	5	26	0	56	20	0	3
524865	0	854	523	75	52	0	0	0

赞(0）回复(0）举报 2023-08-02

8mmmxcuj2#

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

example

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

Example here

赞(0）回复(0）举报 2023-08-02

我来回答

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

2条答案

相关问题

热门标签

最新问答