I'm trying to display how many cages for each rate and the start and end date. This is going into an invoice where we bill for each cage per day and these cages can have different rates.
It isn't a simple GROUP BY and getting the MIN and MAX dates since the number of cages can go down or up and then back to the same number again so I need to only look at contiguous data.
I searched for a solution and found this answer. I modified it a little bit to suit my needs and came up with this:
WITH cte(rate_name, cages, use_date) AS (
SELECT 'I1', 8, DATE'2017-11-04' FROM DUAL UNION ALL
SELECT 'I1', 8, DATE'2017-11-05' FROM DUAL UNION ALL
SELECT 'I1', 7, DATE'2017-11-07' FROM DUAL UNION ALL
SELECT 'I1', 7, DATE'2017-11-10' FROM DUAL UNION ALL
SELECT 'I1', 7, DATE'2017-11-11' FROM DUAL UNION ALL
SELECT 'I1', 8, DATE'2017-11-12' FROM DUAL UNION ALL
SELECT 'I1', 8, DATE'2017-11-13' FROM DUAL UNION ALL
SELECT 'I1', 8, DATE'2017-11-14' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-01' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-02' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-03' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-04' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-05' FROM DUAL UNION ALL
SELECT 'I1 - BR', 1, DATE'2017-11-06' FROM DUAL UNION ALL
SELECT 'I1 - BR', 1, DATE'2017-11-07' FROM DUAL UNION ALL
SELECT 'I1 - BR', 1, DATE'2017-11-08' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-09' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-10' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-11' FROM DUAL
)
SELECT
a.rate_name,
a.cages,
MIN(a.use_date) AS startdate,
MAX(a.use_date) AS enddate
FROM (
SELECT
cte.use_date,
cte.rate_name,
cte.cages,
ROW_NUMBER() OVER (ORDER BY cte.rate_name, cte.use_date) - ROW_NUMBER() OVER (PARTITION BY cte.rate_name, cte.cages ORDER BY cte.use_date) AS grp
FROM cte
) a
GROUP BY a.rate_name, a.cages, a.grp
ORDER BY a.rate_name ASC, 3;
I ran this query within PL/SQL and it seems to work perfectly for what I'm trying to do. When I tried plugging in the solution in the software tool that we're using it turns out it doesn't support ROW_NUMBER(), OVER, and PARTITION BY.
Is there a way I could achieve the same results without having to use those built-in functionalities?
I've started looking at implementing the ROW_NUMBER() manually and found this approach. It seems to work when I tested it but I haven't really plugged it in. I'm now a bit stuck with implementing PARTITION BY and I just feel a little lost and don't know whether I'm heading in the right direction here.
I just noticed that the query returns an incorrect result.
For I1 with 7 cages there should be 2 rows returned. The first row starts and ends on 2017-11-07 while the second row start on 2017-11-10 and ends on 2017-11-11.
Well, this is a little awkward, but it's the first thing I thought of. I'm sure it can be cleaned up. I had to make a second-level CTE to get it to work without any functions or anything. Probably would've been easier with connect by or lag, but I'm guessing your software tool can't handle those either.
WITH cte(rate_name, cages, use_date) AS (
SELECT 'I1', 8, DATE'2017-11-04' FROM DUAL UNION ALL
SELECT 'I1', 8, DATE'2017-11-05' FROM DUAL UNION ALL
SELECT 'I1', 7, DATE'2017-11-07' FROM DUAL UNION ALL
SELECT 'I1', 7, DATE'2017-11-10' FROM DUAL UNION ALL
SELECT 'I1', 7, DATE'2017-11-11' FROM DUAL UNION ALL
SELECT 'I1', 8, DATE'2017-11-12' FROM DUAL UNION ALL
SELECT 'I1', 8, DATE'2017-11-13' FROM DUAL UNION ALL
SELECT 'I1', 8, DATE'2017-11-14' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-01' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-02' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-03' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-04' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-05' FROM DUAL UNION ALL
SELECT 'I1 - BR', 1, DATE'2017-11-06' FROM DUAL UNION ALL
SELECT 'I1 - BR', 1, DATE'2017-11-07' FROM DUAL UNION ALL
SELECT 'I1 - BR', 1, DATE'2017-11-08' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-09' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-10' FROM DUAL UNION ALL
SELECT 'I1 - BR', 5, DATE'2017-11-11' FROM DUAL
),
recur as (
SELECT
c1.use_date,
c1.rate_name,
c1.cages,
case when c3.rate_name is null then c1.use_date else null end as start_date,
case when c2.rate_name is null then c1.use_date else null end as end_date
FROM cte c1
-- next day
left join cte c2 on c2.rate_name = c1.rate_name and c2.use_date = c1.use_date +1 and c2.cages = c1.cages
-- prev day
left join cte c3 on c3.rate_name = c1.rate_name and c3.use_date = c1.use_date -1 and c3.cages = c1.cages
)
select rate_name, cages, start_date,
(select min(e.end_date) from recur e
where e.rate_name = s.rate_name
and e.end_date >= s.start_date) as end_date
from recur s
where start_date is not null
order by rate_name, start_date;