3

I have a table with two columns; EVENT_DATE date and RANG number the first column holds a date for an event while the second column is for the period of that event. here is a sample of data

| EVENT_DATE | RANG |
|------------|------|
| 03/01/2015 |    1 |
| 09/04/2015 |    3 |
| 15/10/2015 |    2 | 

is there any way to expand the EVENT_DATE by increment it based on the RANG value, so the output will be like,

| EVENT_DATE |
|------------|
| 03/01/2015 |
| 04/01/2015 |

| 09/04/2015 |
| 10/04/2015 |
| 11/04/2015 |
| 12/04/2015 |

| 15/10/2015 |
| 16/10/2015 |
| 17/10/2015 |
  • Is there any max allowed value for the RANG column? Or what is the current max for it? It is doable in mysql (sans ordering/clustering - table is inherently not-ordered) but would be much simpler in some scripting language. – jkavalik Sep 15 '15 at 5:47
  • @jkavalik 1) is a solution similar to the oracle solution dba.stackexchange.com/a/115043/2047 posible in mysql? 2) I don't understand your remark about ordering? Why isn' odering possible? – miracle173 Sep 15 '15 at 8:18
  • Sorry, somehow mixed mysql instead of oracle. CTE is not possible in mysql, but you can "generate" a virtual table of as many dates as you want on the fly like in stackoverflow.com/a/2157776/1786423 . For the ordering in sql - a table is just a set of rows, there is no explicit ordering for them in the standard - actual implementation then can have its own order depending on lot of things (clustering key can define one etc). But it is good to expect the rows not to be ordered at all "by default" - thats what ORDER BY clause is for. Sorry for not being clear. – jkavalik Sep 15 '15 at 8:39
2

You could use a CTE to get a table with a sequence of numbers up to the highest rank in your table, and then join with that:

with nums as (
  select
    level-1 lvl
  from dual
  connect by
    level-1 < = (select max(rang) from foo)
)
select 
  foo.event_date + nums.lvl
, rang
from foo
join nums
  on nums.lvl <= foo.rang
order by
  foo.event_date + nums.lvl;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.