Here's one way to do it. To set it up, first you want to:
- Generate a list of dates in October:
select date'2016-10-01'+rownum-1 dt from dual connect by level <= 31
- Convert the list of transactions into start/end date periods. You can do this using lead:
select loc, dt, lead(dt) over (partition by loc order by dt) nd, qty
from t
Then join the October dates that fall in the calculated periods. Exclude those where the quantity is zero:
with oct_dates as (
select date'2016-10-01'+rownum-1 dt from dual connect by level <= 31
), ranges as (
select loc, dt, lead(dt) over (partition by loc order by dt) nd, qty
from t
)
select loc,
d.dt
from ranges r
join oct_dates d
on d.dt >= r.dt
and d.dt < nvl(r.nd, date'2016-11-01')
where qty > 0;
LOC DT
CA02D003A 01-Oct-16
CA02D004A 01-Oct-16
CA02D003A 02-Oct-16
CA02D004A 02-Oct-16
CA02D003A 03-Oct-16
CA02D004A 03-Oct-16
...
This gives you the dates each location was in use. If you just want the number of days, group by loc and count:
create table t (
loc varchar2(20),
dt date,
qty int
);
alter session set nls_date_format = 'dd-Mon-yy';
insert into t values ('CA02D003A', '31-Jul-16', 1152);
insert into t values ('CA02D003A', '30-Sep-16', 1092);
insert into t values ('CA02D003A', '11-Oct-16', 500);
insert into t values ('CA02D003A', '13-Oct-16', 0);
insert into t values ('CA02D003A', '20-Oct-16', 200);
insert into t values ('CA02D003A', '24-Oct-16', 0);
insert into t values ('CA02D003A', '26-Oct-16', 1752);
insert into t values ('CA02D004A', '31-Aug-16', 0);
insert into t values ('CA02D004A', '30-Sep-16', 96);
insert into t values ('CA02D004A', '12-Oct-16', 40);
insert into t values ('CA02D004A', '18-Oct-16', 70);
insert into t values ('CA02D004A', '22-Oct-16', 0);
insert into t values ('CA02D004A', '26-Oct-16', 20);
insert into t values ('CA02D004A', '30-Oct-16', 0);
with oct_dates as (
select date'2016-10-01'+rownum-1 dt from dual connect by level <= 31
), ranges as (
select loc, dt, lead(dt) over (partition by loc order by dt) nd, qty
from t
)
select loc,
count(d.dt)
from ranges r
join oct_dates d
on d.dt >= r.dt
and d.dt < nvl(r.nd, date'2016-11-01')
where qty > 0
group by loc;
LOC COUNT(D.DT)
CA02D003A 22
CA02D004A 25
If you want to know which periods it was in use, you can use the Tabibitosan method to group consecutive dates together and get the min start and max end date for each:
with oct_dates as (
select date'2016-10-01'+rownum-1 dt from dual connect by level <= 31
), ranges as (
select loc, dt, lead(dt) over (partition by loc order by dt) nd, qty
from t
), grps as (
select loc,
d.dt - row_number() over (partition by loc order by d.dt) grp,
d.dt
from ranges r
join oct_dates d
on d.dt >= r.dt
and d.dt < nvl(r.nd, date'2016-11-01')
where qty > 0
)
select loc, min(dt), max(dt) from grps
group by loc, grp
order by 1, 2;
LOC MIN(DT) MAX(DT)
CA02D003A 01-Oct-16 12-Oct-16
CA02D003A 20-Oct-16 23-Oct-16
CA02D003A 26-Oct-16 31-Oct-16
CA02D004A 01-Oct-16 21-Oct-16
CA02D004A 26-Oct-16 29-Oct-16
You can find out more about the Tabibitosan method by watching this:
https://www.youtube.com/watch?v=yvimYixXo2Q Or reading this:
http://rwijk.blogspot.co.uk/2014/01/tabibitosan.html