Expand table rows based on column value

oracle

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 |

Best Answer

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;

Related Solutions

Mysql – How to design a query that gives count of different parameters based on different condition from a single table

select sum(case when issue_date = return_date then 1 else 0 end) SAME_DAY,
       sum(case when return_date is null then 1 else 0 end) NOT_RET,
       sum(case when issue_date <> return_date then 1 else 0 end) SEP_DAY
  from books;

Summarizing Pay Checks Based on Employee’s Job Code

I was able to figure out my own problem. The HR system is based on Peoplesoft and it includes something called an effective sequence. For this specific employee they had more than one effective sequence on the 01/20/14 effective date which was causing the duplication. Here is the finished SQL:

SELECT
  CheckViewHrsErn.EMPLID,
  JobDta.JOBCODE,
  CheckViewHrsErn.CHECK_DT,
  SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
  CheckViewHrsErn.EMPLID = '01088844'
  AND CheckViewHrsErn.ENTRY_NBR <> 0
  AND CheckViewHrsErn.EARNINGS <> 0
  AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
  AND JobDta.EFFDT = 
              ( 
                SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
                WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
                AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
              )
  AND JobDta.EFFSEQ =
                    (
                      SELECT MAX ( JobMaxEffSeq.EFFSEQ ) FROM PS_JOB JobMaxEffSeq
                      WHERE JobMaxEffSeq.EMPLID = CheckViewHrsErn.EMPLID
                      AND JobMaxEffSeq.EFFDT =
                                            ( 
                                              SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
                                              WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
                                              AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
                                            )
                    )

GROUP BY
  CheckViewHrsErn.EMPLID,
  JobDta.JOBCODE,
  CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;

Best Answer

Related Solutions

Mysql – How to design a query that gives count of different parameters based on different condition from a single table

Summarizing Pay Checks Based on Employee’s Job Code

Related Question