CODEWITHSUNDEEP
oracle-databasedateoracle11g
Mong2203
Mong2203

Reputation: 71

Get list of all possible dates based on particular days [oracle 11g]

I have presentation table consists of:

ID     DAY         START       END      STARTDATE       ENDDATE
622   Monday     12:00:00    02:00:00   01-05-2016     04-06-2016 
623   Tuesday    12:00:00    02:00:00   01-05-2016     04-06-2016 
624   Wednesday  08:00:00    10:00:00   01-05-2016     04-06-2016 
625   Thursday   10:00:00    12:00:00   01-05-2016     04-06-2016

I would like to list down all possible dateS from STARTDATE to ENDDATE. I have tried a query which is working except that it will list ALL POSSIBLE dates for every IDs. I may just need from one ID because all rows are having the same dates basically.

Query:

select 
  A.PRESENTATIONID,
  A.PRESENTATIONDATESTART+delta dt
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
order by 1,2;

The result: 

622 01-05-2016 12:00:00 //startdate
.....
622 03-06-2016 12:00:00
622 04-06-2016 12:00:00 //enddate
.....
625 04-06-2016 12:00:00

The result is very tedious!

I would like to list down ONLY possible dates where the day is stored in my presentation table. For example, in presentation table the days are MONDAY,TUESDAY,WEDNESDAY and THURSDAY. Possible outcome will be:

MONDAY
02-05-2016 12:00:00
09-05-2016 12:00:00
16-05-2016 12:00:00
23-05-2016 12:00:00
30-05-2016 12:00:00

TUESDAY
03-05-2016 12:00:00
10-05-2016 12:00:00
17-05-2016 12:00:00
24-05-2016 12:00:00
31-05-2016 12:00:00
.... until THURSDAY

On the way to explain, in this problem I would like to EXCLUDE dates for FRIDAY, SATURDAY and SUNDAY. Is it possible?

UPDATE

New query:

select 
  A.PRESENTATIONID,
  A.PRESENTATIONDAY,
  A.PRESENTATIONDATESTART+delta dt
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;

Result:

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

You may check the calendar to see if the days and dates are match :D Thank you again @dcieslak!

Upvotes: 0

Views: 95

Answers (1)

dcieslak
dcieslak

Reputation: 2715

Add to your query:

 and
  a.day = trim(to_char(A.PRESENTATIONDATESTART+delta, 'Day'))

It will remove days that are not equal to your DAY column. The trim is important for 'Day' because we want to get rid of leading and trailing spaces.

Upvotes: 1

Related Questions