CODEWITHSUNDEEP
sqloracle-database
Kittoes0124
Kittoes0124

Reputation: 5080

How to generate rows for date range by key

I'm having trouble with the following query:

SELECT
    B.EMPLOYEE_NAME,
    A.START_DATE+(LEVEL-1) AS INDIVIDUAL_DAY,
    TO_CHAR(A.START_DATE,'MM/DD/YYYY') START_DATE,
    TO_CHAR(A.END_DATE,'MM/DD/YYYY') END_DATE
FROM table1 A
INNER JOIN table2 B ON A.EMPLOYEE_NAME = B.EMPLOYEE_NAME
CONNECT BY LEVEL-1 <= (A.END_DATE-A.START_DATE);

If there is only one record in table A then I get exactly what I'm looking for. However if there is more than one record then I get an excess amount of results.

What I'm looking for:

I have a table with rows that have EMPLOYEE_NAME, START_DATE, and END_DATE fields. I would like to turn this single row of information into multiple rows. Example:

Current - 

NAME          START       END
DAVID SMITH   1-1-2001    1-6-2011
JOHN SMITH    2-7-2012    2-9-2012

Desired - 

NAME          DATE
DAVID SMITH   1-1-2001
DAVID SMITH   1-2-2001
DAVID SMITH   1-3-2001
DAVID SMITH   1-4-2001
DAVID SMITH   1-5-2001
DAVID SMITH   1-6-2001
JOHN SMITH    2-7-2012
JOHN SMITH    2-8-2012
JOHN SMITH    2-9-2012

Any ideas on how I can accomplish this? Note: I'm using Oracle 10 and 11.

Upvotes: 2

Views: 2163

Answers (2)

Art
Art

Reputation: 5792

Not looking for scores. Found your post and doing this of boredom...

My test tabe:

EMPNO    ENAME    START_DATE    END_DATE
------------------------------------------
7369     SMITH    6/1/2011      6/7/2011
7499     ALLEN    7/1/2011      7/3/2011

SELECT ename, ind_start_date
  FROM
  (
   SELECT distinct ename
        , start_date
        , to_char(start_date + (LEVEL-1), 'MM/DD/YYYY') ind_start_date
        , to_char(end_date, 'MM/DD/YYYY') end_date
    FROM emp_test
   WHERE ename IN ('SMITH', 'ALLEN')
  CONNECT BY LEVEL <= (end_date-start_date)+1
  ORDER BY ename
 )
 /

ENAME    IND_START_DATE
--------------------------
ALLEN    07/01/2011
ALLEN    07/02/2011
ALLEN    07/03/2011
SMITH    06/01/2011
SMITH    06/02/2011
SMITH    06/03/2011
SMITH    06/04/2011
SMITH    06/05/2011
SMITH    06/06/2011
SMITH    06/07/2011

Keep it simple...

Upvotes: 3

DazzaL
DazzaL

Reputation: 21993

in 10g/11g you can use the model clause for this. 

SQL> with emps as (select rownum id, name, start_date,
  2                       end_date, trunc(end_date)-trunc(start_date) date_range
  3                  from table1)
  4  select name, the_date
  5    from emps
  6  model partition by(id as key)
  7        dimension by(0 as f)
  8        measures(name, start_date, cast(null as date) the_date, date_range)
  9        rules (the_date [for f from 0 to date_range[0] increment 1]  = start_date[0] + cv(f),
 10               name[any] = name[0]);

NAME        THE_DATE
----------- ----------
DAVID SMITH 01-01-2001
DAVID SMITH 01-02-2001
DAVID SMITH 01-03-2001
DAVID SMITH 01-04-2001
DAVID SMITH 01-05-2001
DAVID SMITH 01-06-2001
JOHN SMITH  02-07-2012
JOHN SMITH  02-08-2012
JOHN SMITH  02-09-2012

9 rows selected.

ie your base query:

select rownum id, name, start_date,
       end_date, trunc(end_date)-trunc(start_date) date_range
  from table1

just defines the dates + the range (I used rownum id, but if you have a PK you can use that instead.

the partition splits our calculations per ID(unique row):

6  model partition by(id as key)

the measures:

8        measures(name, start_date, cast(null as date) the_date, date_range)

defines the attributes we will be outputting/calculating. in this case, we're working with name, and the start_date plus the range of rows to generate. additionally i've defined a column the_date that will hold the calculated date (i.e we want to caluclate start_date + n where n is from 0 to the range.

the rules define HOW we are going to populate our columns:

9        rules (the_date [for f from 0 to date_range[0] increment 1]  = start_date[0] + cv(f),
10               name[any] = name[0]);

so with 

the_date [for f from 0 to date_range[0] increment 1]

we are saying that we will generate the number of rows that date_range holds+1 (ie 6 dates in total). the value of f can be referenced through the cv(current value) function.

so on row 1 for david, we'd have the_date [0] = start_date+0 and subsequently on row 2, we'd have the_date [1] = start_date+1. all teh way up to start_date+5 (i.e the end_date)

p.s. for connect by you'd need to do something like this:

select 
    A.EMPLOYEE_NAME,
    A.START_DATE+(b.r-1) AS INDIVIDUAL_DAY,
    TO_CHAR(A.START_DATE,'MM/DD/YYYY') START_DATE,
    TO_CHAR(A.END_DATE,'MM/DD/YYYY') END_DATE
FROM table1 A
     cross join (select rownum r
                   from (select max(end_date-start_date) d from table1)
                  connect by level-1 <= d) b
 where A.START_DATE+(b.r-1) <= A.END_DATE
 order by 1, 2;

i.e. isolate the connect by to a subquery, then filter out the rows where individual_day > end_date.

but i WOULD NOT recommend this approach. its performance will be worse compared to the model approach (especially if the ranges get big).

Upvotes: 3

Related Questions