Q:

get continuosly increasing sale records from table sql server

I have 2 tables 
Product
  ProdId, ProdName
    1     A
    2     B

and 
Sale
SaleId, ProdId, Sale, Year
1,      1,      100,  2012
2,      1,      130,  2013
3,      2,      100,  2012,
4,      1,      150,  2014,
5,      1,      180,  2015
6,      2,      120,  2013,
7,      2,       90,  2014,
8,      2,      130,  2015

I want the name of product whose sale is continuosly increasing.
Like Product "A" has sale record like in year 2012 - 100 Units,2013 - 130 Units,2014 - 150 Units,2015 - 180 Units, So this product A is having continuous increase in sale. Another case of non-continuous record is, product "B" having sale record 2012 - 100 Units,2013 - 120 Units,2014 - 90 Units, 2015 - 130 Units, So for product "B", it is not continuous.
I want records like product "A", who is having continuous increasing sale.
Help appreciated.

A:

Use cross apply to get the previous year's sale amount and check with conditional aggregation for the increasing amount condition.
select prodid 
from sale s1
cross apply (select sale as prev_sale 
             from sale s2 
             where s1.prodid=s2.prodid and s2.year=s1.year-1) s2
group by prodid
having sum(case when sale-prev_sale<0 then 1 else 0 end) = 0

To get the all the rows for such prodId's, use 
select * from sale 
where prodid in (select prodid 
                 from sale s1
                 cross apply (select sale as prev_sale 
                              from sale s2 
                              where s1.prodid=s2.prodid and s2.year=s1.year-1) s2
                 group by prodid
                 having sum(case when sale-prev_sale<0 then 1 else 0 end) = 0
                ) 

