Ufdjrw

I have the sample data set below which list the water meters not working for specific reason for a certain range period (jan 2016 to december 2018).

I would like to have a query that retrieves the last maximum and minimum consecutive period where the meter was not working within that range of period.

any help will be greatly appreciated.

You have two options:

select code, to_char(min_period, 'yyyymm') min_period, to_char(max_period, 'yyyymm') max_period
 from (
 select code, min(period) min_period, max(period) max_period,
 max(min(period)) over (partition by code) max_min_period
 from (
 select code, period, sum(flag) over (partition by code order by period) grp
 from (
 select code, period, 
 case when add_months(period, -1) 
 = lag(period) over (partition by code order by period) 
 then 0 else 1 end flag
 from (select mrdg_acc_code code, to_date(mrdg_per_period, 'yyyymm') period from t)))
 group by code, grp)
 where min_period = max_min_period

Explanation:

• flag rows where period is not equal previous period plus one month,


• create column grp which sums flags consecutively,


• group data using code and grp additionaly finding maximal start of period,


• show only rows where min_period = max_min_period
  

Second option is recursive CTE available in Oracle 11g and above:

with 
 data(period, code) as (
 select to_date(mrdg_per_period, 'yyyymm'), mrdg_acc_code from t 
 where mrdg_per_period between 201601 and 201812),
 cte (period, code) as (
 select to_char(period, 'yyyymm'), code from data
 where (period, code) in (select max(period), code from data group by code)
 union all
 select to_char(data.period, 'yyyymm'), cte.code 
 from cte 
 join data on data.code = cte.code 
 and data.period = add_months(to_date(cte.period, 'yyyymm'), -1))
select code, min(period) min_period, max(period) max_period 
 from cte group by code

Explanation:

• subquery data filters only rows from 2016 - 2018 additionaly converting period to date format. We need this for function add_months to work.


• 
  cte is recursive. Anchor finds starting rows, these with maximum period for each code. After union all is recursive member, which looks for the row one month older than current. If it finds it then net row, if not then stop.


• final select groups data. Notice that period which were not consecutive were rejected by cte.

Though recursive queries are slower than traditional ones, there can be scenarios where second solution is better.

Here is the dbfiddle demo for both queries. Good luck.

use aggregate function with group by

select max(mdrg_per_period) mdrg_per_period, mrdg_acc_code,max(mrdg_date_read),rea_Desc,min(mdrg_per_period) not_working_as_from
from tablename
group by mrdg_acc_code,rea_Desc

This is a bit tricky. This is a gap-and-islands problem. To get all continuous periods, it will help if you have an enumeration of months. So, convert the period to a number of months and then subtract a sequence generated using row_number(). The difference is constant for a group of adjacent months.

This looks like:

select acc_code, min(period), max(period)
from (select t.*,
 row_number() over (partition by acc_code order by period_num) as seqnum
 from (select t.*, floor(period / 100) * 12 + mod(period, 100) as period_num
 from t
 ) t
 where rea_desc = 'METER NOT WORKING'
 ) t
group by (period_num - seqnum);

Then, if you want the last one for each account, you can use a subquery:

select t.*
from (select acc_code, min(period), max(period),
 row_number() over (partition by acc_code order by max(period desc) as seqnum
 from (select t.*,
 row_number() over (partition by acc_code order by period_num) as seqnum
 from (select t.*, floor(period / 100) * 12 + mod(period, 100) as period_num
 from t
 ) t
 where rea_desc = 'METER NOT WORKING'
 ) t
 group by (period_num - seqnum)
 ) t
where seqnum = 1;