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
PHIL SawyerAsked:
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.

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

Open in new window

0
sdstuberCommented:
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
sdstuberCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PortletPaulfreelancerCommented:
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
sdstuberCommented:
same with division.  Simply change the 30 to 100 or 25.

math is cool like that.  :)
0
PortletPaulfreelancerCommented:
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

Open in new window

and (last cheap shot) if one wanted to report on all ranges (left join to data)?
0
sdstuberCommented:
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
PHIL SawyerAuthor 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
awking00Commented:
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
PortletPaulfreelancerCommented:
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
;

Open in new window

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
sdstuberCommented:
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

Your issues matter to us.

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

Start your 7-day free trial
PHIL SawyerAuthor Commented:
Thanks Guys
Great responses - I've used ststuber case statement and works a treat.
0
PortletPaulfreelancerCommented:
:) 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
;

Open in new window

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.