# Increments

Hello
I have a data set where it contains customers and no of days columns
eg
Customer          Days
A                         5
B                         55
C                         765
D                         123 ETC ETC
....
I want to be able to group the days in 30 day increments (0 to 30 then 61 to 90 .. all the way to 1000) - this could be a very long Case statement.  Is there another succinct way?

Customer       Days          Grouping
A                       5               0 to 30              ETC ETC
Regards
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

freelancerCommented:
try a recursive common table expression to build the ranges, then join it to your table, like this:
``````
WITH ranges (startat, stopat) AS (
SELECT 0 AS startat, 30 AS stopat FROM dual
UNION ALL
SELECT stopat + 1 AS startat, stopat + 30 AS stopat
FROM ranges
WHERE stopat + 1 < 1000
)
SELECT
r.startat
, r.startat || ' - ' || r.stopat as range
, count(days) as within_range
from your_table d
inner join ranges r on d.days between r.startat and r.stopat
group by
r.startat
, r.startat || ' - ' || r.stopat
order by
startat
;
``````
0
Commented:
no need for additional data to join to, just use simple division to identify the buckets

SELECT customer, days, 30 * FLOOR((days-1) / 30) + 1 || ' to ' || 30 * (FLOOR((days-1) / 30) + 1) GROUPING
FROM yourdata
0
Commented:
oops, I just noticed the first range started at 0, not at 1

So that grouping is a little odd since it's not a group of 30 days, but 31

Also, what should the last grouping be?  991 to 1000?
or 991 to 1020?

Is your data constrained such that values over 1000 are impossible?
If not, what should the results be if you encounter big values 1001 or higher?

Assuming 0-30, then groups of 30 except 991-1000, then try this for the grouping portion of the query

CASE
WHEN days <= 30 THEN '0 to 30'
WHEN days >= 991 THEN '991 to 1000'
ELSE 30 * (CEIL(days / 30) - 1) + 1 || ' to ' || 30 * (CEIL(days / 30))
END

if you want groups of 30 for all sets except the first then simply remove the second WHEN

CASE
WHEN days <= 30 THEN '0 to 30'
ELSE 30 * (CEIL(days / 30) - 1) + 1 || ' to ' || 30 * (CEIL(days / 30))
END
0
freelancerCommented:
quite right: the recursive approach stops at 1020, not 1000

:) on the plus side, if I want 25 or 100 as the increment it's getting easier to adjust using the cte
(alter 30 to new number in lines 3 and 5)
0
Commented:
same with division.  Simply change the 30 to 100 or 25.

math is cool like that.  :)
0
freelancerCommented:
if it was the one calculation I'd certainly agree, annoying as it is to admit that
but as you keep adding case expressions it's starting to look less inviting :)

one may also need to add a method for sorting
``````SELECT
startat
, range
, count(range)
FROM (
SELECT
CASE
WHEN days <= 30 THEN '0 to 30'
ELSE 30 * (CEIL(days / 30) - 1) + 1 || ' to ' || 30 * (CEIL(days / 30))
END AS range
, 30 * (CEIL(days / 30) - 1) + 1 as startat
FROM your_table
)
GROUP BY
startat
, range
ORDER BY
startat
``````
and (last cheap shot) if one wanted to report on all ranges (left join to data)?
0
Commented:
Sorry, I have no idea what you're trying to say.

but that's ok, the question was answered including other variations on top condition
0
Author Commented:
I've just done a calc and the maximum number it need to go to is 1830 in terms of 30 day grouping and anything above 1830 can be categorized as the last group.

Regards
0
Commented:
Another recursive method -
with ranges as
(select
case when level = 1 then 0 else (level - 1) * 30 + 1 end startno,
case when level * 30 > 1000 then 1000 else level * 30 end stopno
from dual
connect by level <= ceil(1000/30))
select d.customer, d.days, to_char(r.startno)||' to '||to_char(r.stopno) as grouping
from dataset d, ranges r
where d.days between r.startno and r.stopno
order by d.customer;
0
freelancerCommented:
the recursive cte approach for those additions would be:
``````WITH ranges (startat, stopat) AS (
SELECT 0 AS startat, 30 AS stopat FROM dual
UNION ALL
SELECT stopat + 1 AS startat, stopat + 30 AS stopat
FROM ranges
WHERE stopat + 1 < 1830
)
SELECT
NVL(r.startat,1831) as startat
, CASE
WHEN r.startat is not null then r.startat || ' - ' || r.stopat
ELSE '1831 +'
END AS range
, count(days) AS within_range
FROM your_table d
LEFT JOIN ranges r ON d.days BETWEEN r.startat AND r.stopat
GROUP BY
r.startat
, r.startat || ' - ' || r.stopat
ORDER BY
startat
;
``````
This assumes you only want to list ranges that has data from your table that matches. e.g. if there were no days falling in the range 1021 - 1050 it would not be listed.

{+ edit - sorry, spelling correction}
0
Commented:
simply use the second case above and it will handle up to 1830 (or beyond if you encounter some)

SELECT customer, days,
CASE
WHEN days <= 30 THEN '0 to 30'
ELSE 30 * (CEIL(days / 30) - 1) + 1 || ' to ' || 30 * (CEIL(days / 30))
END GROUPING
FROM yourdata

if you want to force a hard upper limit then that simply becomes an additional case condition

CASE
WHEN days <= 30 THEN '0 to 30'
WHEN days >= 1801 THEN '1801 to 1830'
ELSE 30 * (CEIL(days / 30) - 1) + 1 || ' to ' || 30 * (CEIL(days / 30))
END

or possibly something like this to indicate any larger value

CASE
WHEN days <= 30 THEN '0 to 30'
WHEN days > 1830 THEN '1831 or more'
ELSE 30 * (CEIL(days / 30) - 1) + 1 || ' to ' || 30 * (CEIL(days / 30))
END
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thanks Guys
Great responses - I've used ststuber case statement and works a treat.
0
freelancerCommented:
:) as I thought, drat

last cheap shot was meant to imply something like this:
``````WITH
range (startat, stopat) AS (
SELECT 0 AS startat, 30 AS stopat
FROM dual
UNION ALL
SELECT stopat + 1 AS startat, stopat + 30 AS stopat
FROM range
WHERE stopat + 1 < 1830
),
ranges (startat, stopat, range) AS (
SELECT startat, stopat, startat || ' - ' || stopat AS range FROM range
UNION ALL
SELECT 1831, NULL, '1831 +' FROM dual
)
SELECT
r.startat
, r.range
, count(days) AS within_range
FROM ranges r
LEFT JOIN your_table d ON (d.days BETWEEN r.startat AND r.stopat)
OR (d.days >= 1831 AND r.startat = 1831)
GROUP BY
r.startat
, r.range
ORDER BY
startat
;
``````
IF you did ever need to report all the ranges, then a table of ranges is useful.
Cheers, Paul
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.