CASE and GROUP BY in SQL Server

Question

Here is my table.

Price   Volume  Month
--------------------------
600    60   April
500    50   April
450    0    April
400    0    April
300    40   April
95     0    April
200    40   March
100    40   March
90     0    March
80     0    March
70     10   March
60     0    March

For each month I need to select all row prior to first two consecutive zero in Volume column, which is ordered by price in descending order. e.g. Here is what I would like to get:

Price   Volume  Month   rn
--------------------------------
600      60     April   0
500      50     April   0
200      40     March   0
100      40     March   0

I know how to accomplish it without grouping by month (thanks to stackoverflow.com link).

Here is code:

with 
flagged as (
  select price, 
     volume,
     case 
       when volume + lead(volume) over (order by price desc) = 0 then 1
       else 0
     end as rn, month
  from [AnalysisDatabase].[dbo].[tblLagExample]),
 counted as (
 select price, 
     volume, 
     sum(rn) over (order by price desc) as cn ,-- this is running sum
     rn, month
  from flagged)

select price, volume, cn, month, rn
from counted
 where cn = 0 and rn = 0
 order by price desc

Any suggestions how to do it with grouping by month.

Answer 1

You're almost there. All you need is to add PARTITION BY month to your OVER clause.

Here's SQL Fiddle.