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.

How is your table ordered? How do you define "*consecutive*"? Please not that in your [other question](http://stackoverflow.com/questions/23036457/sql-query-to-choose-row-before-two-consecutive-true-values?lq=1) you had rows ordered by `price`. Is it by `price desc, month` now? — PM 77-1, Apr 13 '14 at 01:51
First ordered by month (does not matter what order) and then ordered by price in descending order. — user1700890, Apr 13 '14 at 01:53
Add partitioning by month. See http://technet.microsoft.com/en-us/library/ms189461.aspx — PM 77-1, Apr 13 '14 at 02:00

score 3 · Accepted Answer · answered Apr 13 '14 at 02:09

3

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

Here's SQL Fiddle.

answered Apr 13 '14 at 02:09

PM 77-1

12,933
21
68
111

Thanks alot! SQL Fiddle is awesome! – user1700890 Apr 13 '14 at 12:58

CASE and GROUP BY in SQL Server

1 Answers1