CODEWITHSUNDEEP
sqloracle11g
raatprohory
raatprohory

Reputation: 101

SQL Query for counting the number of days a particular balance of an account is continuing

Lets say I have an account balance table with the following information for a particular account 'X':

Date             Balance  
01/01/2016       100   
02/01/2016       100  
03/01/2016       100   
04/01/2016       200  
05/01/2016       200  
06/01/2016       100  
07/01/2016       100  
08/01/2016       500  
09/01/2016       100  
10/01/2016       600

I need output like below:

FromDate    UptoDate      Balance      NoOfDays  
01/01/2016  03/01/2016    100          3  
04/01/2016  05/01/2016    200          2  
06/01/2016  07/01/2016    100          2  
08/01/2016  08/01/2016    500          1  
09/01/2016  09/01/2016    100          1  
10/01/2016  10/01/2016    600          1

The above dates are given in 'DD/MM/YYYY' format and I am using Oracle11gR2.

Upvotes: 1

Views: 201

Answers (1)

dnoeth
dnoeth

Reputation: 60482

A common solution to this problem is based on two sequences:

select account, min(date), max(date), balance, count(*)
from
 (
   select 
      account, date, balance,
      -- assigns a meaningless number to each row 
      -- same number as long as the balance doesn't change
      row_number() over (partition by account order by date) -
      row_number() over (partition by account, balance order by date) as grp
   from account_balane
 ) dt
group by account, balance, grp

Upvotes: 2

Related Questions