Using SQL Server 2012, I am trying create a query that provides me with, say, the top 10 longest wet (or dry) periods from a climate database. 

My temp table provides the following data output:

```
select monthid as [id], date, rain_today 
from #raindays
order by 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 would like to return a ranked table that would count the period where rain\_today is > 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 I have got from reviewing other similar queries is the following (this is for dry days):

```
 select 
     #raindays.monthid as id,
     min(#raindays.date) as [FirstDryDay],
     max(#raindays.date) as [LatestDryDay],
     count(*) as countdays
 from 
     (select 
          monthid, 
          coalesce(max(case 
                          when rain_today > '0' 
                             then #raindays.date end), '19000101') as latestdry
     from 
         #raindays
     group by 
         monthid) g
join 
    #raindays on #raindays.monthid = g.monthid
              and #raindays.date > g.latestdry
group by 
    #raindays.monthid
order by 
    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 you can see, I don't really want to group by id as I want to be able to span over different months and I'm missing other periods that occur earlier in the month. The actual count is working fine correctly it 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 for any help!