Increments

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
;

Select allOpen in new window

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

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

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)

same with division.  Simply change the 30 to 100 or 25.

math is cool like that.  :)

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

Select allOpen in new window

and (last cheap shot) if one wanted to report on all ranges (left join to data)?

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

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

Thanks Guys
Great responses - I've used ststuber case statement and works a treat.

:) 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
;

Select allOpen in new window

IF you did ever need to report all the ranges, then a table of ranges is useful.
Cheers, Paul