Arrange the date query result [oracle 11g]

Question

This is my query to get all possible dates between two dates based on days.

    select A.presentationID,
       A.PRESENTATIONDAY,
       TO_CHAR(A.PRESENTATIONDATESTART+delta,'DD-MM-YYYY','NLS_CALENDAR=GREGORIAN') LIST_DATE
from 
  PRESENTATION A, 
  (
     select level-1 as delta 
     from dual 
     connect by level-1 <= (
       select max(PRESENTATIONDATEEND- PRESENTATIONDATESTART) from PRESENTATION
     )
  )
where A.PRESENTATIONDATESTART+delta <= A.PRESENTATIONDATEEND
and
  a.presentationday = trim(to_char(A.PRESENTATIONDATESTART+delta, 'Day'))
order by 1,2,3;

The values are retrieved from presentation table which consist of presentationday, presentationdatestart and presentationdateend.

Result from this query is :

622 Monday  02-05-2016 12:00:00
622 Monday  09-05-2016 12:00:00
622 Monday  16-05-2016 12:00:00
622 Monday  23-05-2016 12:00:00
622 Monday  30-05-2016 12:00:00
623 Tuesday 03-05-2016 12:00:00
623 Tuesday 10-05-2016 12:00:00
623 Tuesday 17-05-2016 12:00:00
623 Tuesday 24-05-2016 12:00:00
623 Tuesday 31-05-2016 12:00:00
624 Wednesday   04-05-2016 12:00:00
624 Wednesday   11-05-2016 12:00:00
624 Wednesday   18-05-2016 12:00:00
624 Wednesday   25-05-2016 12:00:00
624 Wednesday   01-06-2016 12:00:00
625 Thursday    05-05-2016 12:00:00
625 Thursday    12-05-2016 12:00:00
625 Thursday    19-05-2016 12:00:00
625 Thursday    26-05-2016 12:00:00
625 Thursday    02-06-2016 12:00:00

How can I arrange these value into something like this:

    622 Monday      02-05-2016     
    623 Tuesday     03-05-2016     
    624 Wednesday   04-05-2016     
    625 Thursday    05-05-2016     
    622 Monday      09-05-2016     
    623 Tuesday     10-05-2016  
    624 Wednesday   11-05-2016    
    625 Thursday    12-05-2016 
    622 Monday      16-05-2016 
    ....
    625 Thursday    02-06-2016

First use `to_char` to change date format as you want. Second `order by 2, 1` instead of `1,2` as order by date then id to arrange your result rows. — Pham X. Bach, May 24 '16 at 15:19
Thing you wanted first is now different from what you want now. Let me update my answer — SSD, May 24 '16 at 15:22
I changed my query in my question. However, I tried to change my order by clause... The result turned to be weirder. Wednesday 01-06-2016 is the first row. It should be Monday 02-05-2016 — Mong2203, May 24 '16 at 15:26

SSD · Answer 1 · 2016-05-24T15:40:28.347

1

Use this:

TO_CHAR(A.PRESENTATIONDATESTART+delta,'DD-MM-YYYY','NLS_CALENDAR=GREGORIAN' ) LIST_DATE

instead of

A.PRESENTATIONDATESTART+delta LIST_DATE

this formats your date

updated answer for your new challenge:

select * from (
  select A.presentationID,
       A.PRESENTATIONDAY,


          TO_CHAR(A.PRESENTATIONDATESTART+delta,'DD-MM-YYYY','NLS_CALENDAR=GREGORIAN') LIST_DATE,
    row_number() over (partition by presentationID,PRESENTATIONDATESTART+delta
order by presentationID,PRESENTATIONDATESTART+delta) r
    from 
      PRESENTATION A, 
      (
         select level-1 as delta 
         from dual 
         connect by level-1 <= (
           select max(PRESENTATIONDATEEND- PRESENTATIONDATESTART) from PRESENTATION
         )
      )
    where A.PRESENTATIONDATESTART+delta <= A.PRESENTATIONDATEEND
    and
      a.presentationday = trim(to_char(A.PRESENTATIONDATESTART+delta, 'Day'))

    )
    order by r

edited May 24 '16 at 15:40

answered May 24 '16 at 15:06

SSD

359
1
3
15

Hi, thank you for your time. What does this query will do? I tried and I am getting a missing right parenthesis error. – Mong2203 May 24 '16 at 15:12
Sorry I forgot one ' at the end of my answer. Use updated answer. The TO_CHAR function formats a date to char type. The first parameter is date the second one is the format of display and the third one is the calendar type – SSD May 24 '16 at 15:15
Thank you for the format.. But I still cant organize the query result as how I desired :) – Mong2203 May 24 '16 at 15:19
Can you please put your result after using my answer? – SSD May 24 '16 at 15:20
use partition by for sorting based on the partitions – SSD May 24 '16 at 15:27
Hmm I am sorry. Still getting another error : `ORA-30485: missing ORDER BY expression in the window specification` – Mong2203 May 24 '16 at 15:33
I fixed it check now – SSD May 24 '16 at 15:37
sorry not having Oracle installed is a big problem. play with different value of partition by such as partition by 1 for example or different columns for order by in it. – SSD May 24 '16 at 15:51

Boneist · Accepted Answer · 2016-05-24T16:43:44.633

I think you're just after this:

select   a.presentationid,
         a.presentationday,
         to_char (a.presentationdatestart + delta, 'DD-MM-YYYY', 'NLS_CALENDAR=GREGORIAN') list_date
from     presentation a,
         (select     level - 1 as delta
          from       dual
          connect by level - 1 <= (select max (presentationdateend - presentationdatestart)
                                   from   presentation))
where    a.presentationdatestart + delta <= a.presentationdateend
and      a.presentationday = to_char(a.presentationdatestart + delta, 'fmDay')
order by a.presentationdatestart + delta,
         a.presentationid;

N.B. note how I've removed your trim() and replaced it with fm in the format mask.

P.S. You could rewrite your query to remove the join condition (and extra call to the presentation table) by doing it like so:

with presentation as (select 622 presentationid, 'Monday' presentationday, to_date('01/05/2016', 'dd/mm/yyyy') presentationdatestart, to_date('31/05/2016', 'dd/mm/yyyy') presentationdateend from dual union all
                      select 623 presentationid, 'Tuesday' presentationday, to_date('01/05/2016', 'dd/mm/yyyy') presentationdatestart, to_date('31/05/2016', 'dd/mm/yyyy') presentationdateend from dual union all
                      select 624 presentationid, 'Wednesday' presentationday, to_date('01/05/2016', 'dd/mm/yyyy') presentationdatestart, to_date('07/06/2016', 'dd/mm/yyyy') presentationdateend from dual union all
                      select 625 presentationid, 'Thursday' presentationday, to_date('01/05/2016', 'dd/mm/yyyy') presentationdatestart, to_date('07/06/2016', 'dd/mm/yyyy') presentationdateend from dual)
-- end of mimicking your presentation table with data in it. You wouldn't need this subquery as you have the table; see SQL below.
select presentationid,
       presentationday,
       to_char(next_day(presentationdatestart -1, presentationday) + 7*(level - 1), 'DD-MM-YYYY') list_date
from   presentation
connect by prior presentationid = presentationid
           and prior sys_guid() is not null
           and next_day(presentationdatestart -1, presentationday) + 7*(level - 1) <= presentationdateend
order by next_day(presentationdatestart -1, presentationday) + 7*(level - 1),
         presentationid;

PRESENTATIONID PRESENTATIONDAY LIST_DATE 
-------------- --------------- ----------
           622 Monday          02-05-2016
           623 Tuesday         03-05-2016
           624 Wednesday       04-05-2016
           625 Thursday        05-05-2016
           622 Monday          09-05-2016
           623 Tuesday         10-05-2016
           624 Wednesday       11-05-2016
           625 Thursday        12-05-2016
           622 Monday          16-05-2016
           623 Tuesday         17-05-2016
           624 Wednesday       18-05-2016
           625 Thursday        19-05-2016
           622 Monday          23-05-2016
           623 Tuesday         24-05-2016
           624 Wednesday       25-05-2016
           625 Thursday        26-05-2016
           622 Monday          30-05-2016
           623 Tuesday         31-05-2016
           624 Wednesday       01-06-2016
           625 Thursday        02-06-2016

Thank you so much! But Why do I have to use `fmDay` and remove the `trim()`? — Mong2203, May 24 '16 at 23:37
Because a) it's easier to read/maintain, but b) it's one less function call; it uses the built-in functionality of to_char(). That'll aid performance, even if it's just a tiny bit. It does the same job as the trim does, just y'know, at the same time as producing the string. You don't *have* to use it, just like you don't *have* to start your car by using its starter motor (i.e. a bump start), but it's convenient to. — Boneist, May 25 '16 at 05:46

Arrange the date query result [oracle 11g]

2 Answers2