Choose to open the opening balance for the last date of the month and the amount of another amount for the whole month

Question

I have a balance_detail table

sales_period    sales_date  opening_amt   sales_amt  payment_amt  closing_amt
-----------------------------------------------------------------------------
201501           01-01-2015                210        100          110
201501           02-01-2015   110          300        280          130
201501           03-01-2015   130                      50          80
201501           05-01-2015   80           600        670          10
201502           02-02-2015   10           160        100          70
201502           15-02-2015   70           100        170           0      

And get a result like this

sales_period    opening_amt   sales_amt  payment_amt  closing_amt
-----------------------------------------------------------------------------
 201501            80           1110      1110           10
 201502            70           260       270             0

Answer 1

One way is to use conditional aggregation together with row_number():

select sales_period,
       max(case when seqnum = 1 then opening_amt end) as opening_amt,
       sum(sales_amt) as sales_amt,
       sum(payment_amt) as payment_amt,
       max(case when seqnum = 1 then closing_amt end) as closing_amt
from (select bd.*,
             row_number() over (partition by sales_period order sales_date desc) as seqnum
      from balance_detail bd
     ) bd
group by sales_period;