sql server - Count Consecutive Days where value greater than 0 -

using sql server 2012, trying create query provides me with, say, top 10 longest wet (or dry) periods climate database.

my temp table provides following data output:

select monthid [id], date, rain_today  #raindays order monthid asc, date asc 

output:

id  date            rain_today ------------------------------- 1   24 dec 2014     2.4 1   25 dec 2014     0 1   26 dec 2014     8.7 1   27 dec 2014     1.8 1   28 dec 2014     0.3 1   29 dec 2014     0 1   30 dec 2014     0 1   31 dec 2014     0.3 2   01 jan 2015     0.3 2   02 jan 2015     0.3 2   03 jan 2015     18.3 2   04 jan 2015     0.3 

etc. etc.

i return ranked table count period rain_today > 0, (or rain_today = 0) i.e:

rank start_date   end_date    wet period ---------------------------------------- 1    31 dec 2014  04 jan 2015 5 2    26 dec 2014  28 dec 2014 3 

...

the closest have got reviewing other similar queries following (this dry days):

 select       #raindays.monthid id,      min(#raindays.date) [firstdryday],      max(#raindays.date) [latestdryday],      count(*) countdays        (select            monthid,            coalesce(max(case                            when rain_today > '0'                               #raindays.date end), '19000101') latestdry                #raindays      group           monthid) g join      #raindays on #raindays.monthid = g.monthid               , #raindays.date > g.latestdry group      #raindays.monthid order      countdays desc 

output:

id  firstdryday     latestdryday    countdays ----------------------------------------------- 23  21 oct 2016     31 oct 2016     11 21  23 aug 2016     31 aug 2016     9 **15    23 feb 2016     29 feb 2016     7** 10  25 sep 2015     30 sep 2015     6 8   28 jul 2015     31 jul 2015     4 24  28 nov 2016     30 nov 2016     3 29  29 apr 2017     30 apr 2017     2 30  30 may 2017     31 may 2017     2 31  29 jun 2017     30 jun 2017     2 20  30 jul 2016     31 jul 2016     2 7   29 jun 2015     30 jun 2015     2 5   30 apr 2015     30 apr 2015     1 11  31 oct 2015     31 oct 2015     1 17  30 apr 2016     30 apr 2016     1 22  30 sep 2016     30 sep 2016     1 

as can see, don't want group id want able span on different months , i'm missing other periods occur earlier in month. actual count working fine correctly seems, checking above highlighted period:

id  date    rain_today 15  22 feb 2016     3.9 15  23 feb 2016     0 15  24 feb 2016     0 15  25 feb 2016     0 15  26 feb 2016     0 15  27 feb 2016     0 15  28 feb 2016     0 15  29 feb 2016     0 16  01 mar 2016     3 

thanks in advance help!

is want???

if object_id('tempdb..#testdata', 'u') not null  drop table #testdata;  create table #testdata (     id int not null ,     [date] date not null,     rain_today decimal(9,2) not null      );  insert #testdata (id, date, rain_today) values      (1, '24 dec 2014', 2.4),     (1, '25 dec 2014', 0),     (1, '26 dec 2014', 8.7),     (1, '27 dec 2014', 1.8),     (1, '28 dec 2014', 0.3),     (1, '29 dec 2014', 0),     (1, '30 dec 2014', 0),     (1, '31 dec 2014', 0.3),     (2, '01 jan 2015', 0.3),     (2, '02 jan 2015', 0.3),     (2, '03 jan 2015', 18.3),     (2, '04 jan 2015', 0.3);  --======================================       cte_addrankgroup (         select              td.id,             td.date,             td.rain_today,             hr.hasrain,             rankgroup = dense_rank() on (partition td.id order td.date) -                 dense_rank() on (partition td.id, hr.hasrain order td.date)                      #testdata td             cross apply ( values (iif(td.rain_today = 0, 0, 1)) ) hr (hasrain)         ) select      arg.id,     begdate = min(arg.date),     enddate = max(arg.date),     wetperiod = iif(arg.hasrain = 1, 'wet', 'dry'),     consecutivedays = count(1)      cte_addrankgroup arg group      arg.id,     arg.hasrain,     arg.rankgroup order      arg.id,     min(arg.date); 

results...

id          begdate     enddate     wetperiod consecutivedays ----------- ----------  ----------  --------- --------------- 1           2014-12-24  2014-12-24  wet       1 1           2014-12-25  2014-12-25  dry       1 1           2014-12-26  2014-12-28  wet       3 1           2014-12-29  2014-12-30  dry       2 1           2014-12-31  2014-12-31  wet       1 2           2015-01-01  2015-01-04  wet       4 

edit: code version using case expression in place of iif...

--======================================       cte_addrankgroup (         select              td.id,             td.date,             td.rain_today,             hr.hasrain,             rankgroup = dense_rank() on (partition td.id order td.date) -                 dense_rank() on (partition td.id, hr.hasrain order td.date)                      #testdata td             cross apply ( values (case when td.rain_today = 0 0 else 1 end) ) hr (hasrain)         ) select top 10     arg.id,     begdate = min(arg.date),     enddate = max(arg.date),     wetperiod = case when arg.hasrain = 1 'wet' else 'dry' end,     consecutivedays = count(1)      cte_addrankgroup arg      arg.hasrain = '0' -- top 10 dry     --arg.hasrain = '1' -- top 10 wet group      arg.id,     arg.hasrain,     arg.rankgroup order      consecutivedays desc, min(arg.date); 

modified original script produce top 10 each period type ultimate aim (output full dataset):

id  begdate enddate wetperiod   consecutivedays 31  10 jun 2017     26 jun 2017     dry 17 4   02 mar 2015     14 mar 2015     dry 13 5   12 apr 2015     24 apr 2015     dry 13 20  15 jul 2016     26 jul 2016     dry 12 29  01 apr 2017     11 apr 2017     dry 11 26  17 jan 2017     27 jan 2017     dry 11 23  21 oct 2016     31 oct 2016     dry 11 25  01 dec 2016     09 dec 2016     dry 9 21  10 aug 2016     18 aug 2016     dry 9 21  23 aug 2016     31 aug 2016     dry 9