Skip to Main Content
  • Questions

  • Query and show all weeks between two dates

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Renato Fonseca Oliveira.

Asked: March 03, 2016 - 3:21 pm UTC

Last updated: March 08, 2016 - 1:31 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hello, Tom

I have a sort of tables linked by common columns in a query. It's quite simple. The result looks like this:

PROJ_ID | ACTIVITY_ID |WORK_RESOURCE_ID |TO_CHAR(T3.PLANNED_FINISH,'YYIW')|TO_CHAR(T3.PLANNED_START,'YYIW') | PLANNED_BUDGET
-------- ------------------------------------- ------------------ --------------------------------- -------------------------------- --------------
XXX-001 | FT-PRD-ESTR-EARL-BL33RC-U331B-PROC | A - ESTRUTURA | 1349 | 1347 | 900

Ok. I need to make this line look like this:

PROJ_ID | ACTIVITY_ID | WORK_RESOURCE_ID | "WORKING_WEEK" | PLANNED_BUDGET
-------- ------------------------------------- ------------------ --------------------------------- --------------
XXX-001 | FT-PRD-ESTR-EARL-BL33RC-U331B-PROC | A - ESTRUTURA | 1347 | 300
XXX-001 | FT-PRD-ESTR-EARL-BL33RC-U331B-PROC | A - ESTRUTURA | 1348 | 300
XXX-001 | FT-PRD-ESTR-EARL-BL33RC-U331B-PROC | A - ESTRUTURA | 1349 | 300

Splitting the PLANNED_BUDGET value is ok. It's just a matter of counting weeks and making math.

What I can't figure out is how to show each week between the interval and make one line for each of them.

You can just assume the first result as a table with columns (PROJ_ID, ACTIV, WORK_R, PLAN_FINISH, PLAN_START, PLAN_BUDG).

Thank you

and Connor said...

SQL> create table t1 ( id int, w_from int, w_to int);

Table created.

SQL>
SQL> insert into t1 values (1,  1300, 1302);

1 row created.

SQL> insert into t1 values (2,  1400, 1408);

1 row created.

SQL>
SQL> with
  2    week_boundaries as
  3      ( select min(w_from) minw, max(w_to) maxw from t1 ),
  4    row_generator as
  5      ( select rownum-1+minw wk_num
  6        from   dual, week_boundaries
  7        connect by level <= maxw-minw+1
  8      )
  9  select t1.id, r.wk_num
 10  from t1,
 11       row_generator r
 12  where t1.w_from <= r.wk_num
 13  and   t1.w_to   >= r.wk_num
 14  order by 1,2;

  ID     WK_NUM
---- ----------
   1       1300
   1       1301
   1       1302
   2       1400
   2       1401
   2       1402
   2       1403
   2       1404
   2       1405
   2       1406
   2       1407
   2       1408

12 rows selected.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

It works! Thank you

Renato Fonseca Oliveira Renato, March 04, 2016 - 12:14 pm UTC

Thank you Connor

That's exactly what I was looking for. Quick answer too.

Very nice!

Richard Xu, March 05, 2016 - 12:30 am UTC

I can do this with:

with data as
(select 1 id, 1300 w_from, 1303 w_to from dual
union select 2 id, 1630 w_from, 1639 w_to from dual)
select distinct id, w_from, w_to, w_from+level-1 wk
from data
connect by level <= w_to-w_from+1
order by id, wk;

But if I remove the "DISTINCT", the result is not I could understand. Would you please explain in detail how the SQL engine process the query without the DISTINCT?

Thanks.
Richard Xu
Connor McDonald
March 05, 2016 - 1:26 am UTC

"connect by level <= 10" simply says recurse the hierarchy until I get 10 levels deep. So in the case above for example, you might be on a row with ID=1, but you traverse from that row into a row that might have ID=2 (because you have placed no limitation in the connect by to not do so).

So the first 10 rows of below might help explain

SQL> with data as
  2  ( select 1 id, 1300 w_from, 1303 w_to from dual
  3    union
  4    select 2 id, 1630 w_from, 1639 w_to from dual
  5   )
  6  select level, prior id, id, w_from, w_to, w_from+level-1 wk
  7  from   data
  8  connect by level <= w_to-w_from+1 ;

     LEVEL    PRIORID         ID     W_FROM       W_TO         WK
---------- ---------- ---------- ---------- ---------- ----------
         1                     1       1300       1303       1300
         2          1          1       1300       1303       1301
         3          1          1       1300       1303       1302
         4          1          1       1300       1303       1303
         5          1          2       1630       1639       1634
         6          2          2       1630       1639       1635
         7          2          2       1630       1639       1636
         8          2          2       1630       1639       1637
         9          2          2       1630       1639       1638
        10          2          2       1630       1639       1639


See how at level 5, ID=1 links to 2

Performance Issues

Renato Fonseca Oliveira Renato, March 07, 2016 - 3:27 pm UTC

Hello, guys
As I reviewed before, this solution resulted exactly as I wanted. But I'm experiencing performance issues.
I placed my select statement with all relevant information under WEEK_BOUNDARIES section. This select results in more than 20.000 rows and it tends to grow over time.
When I run the query with WHERE restrictions for testing purposes (resulting in just a few rows), it goes fine. But when I remove the testing filters the query keeps running, and running. It's taking so long that I didn't saw it completed yet.

I was debugging to see how it works on the background and I realized that if I take this part out (where t1.w_from <= r.wk_num and t1.w_to >= r.wk_num - lines 12/13) there is going to have a combination of the WK_NUM. For example:

In Conor's answer if the clauses I mentioned are removed, the result should look like this:

ID WK_NUM
---- ----------
1 1300
1 1301
1 1302
1 1400
1 1401
1 1402
1 1403
1 1404
1 1405
1 1406
1 1407
1 1408
2 1400
2 1401
2 1402
2 1403
2 1404
2 1405
2 1406
2 1407
2 1408
2 1300
2 1301
2 1302

So I think this is what is making this query take so long. With so many IDs and w_from/w_to, those combinations are endless. What do you think? Could this be it or am I doing something wrong?

Thank you

I was wrong

Renato Fonseca Oliveira Renato, March 07, 2016 - 6:09 pm UTC

Sorry, guys
There was a mistake in my code. I should not have done my whole select under the WEEK_BOUNDARIES section.
I revised everything and realized that the other columns should be at the SELECT part at the end of the statement.

Please disconsider the other post.

Thank you


Chris Saxon
March 08, 2016 - 1:31 am UTC

Thanks for getting back to us. Saves us work :-)


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.