asked on

Oracle Fill date Gap

I need help with the Oracle SQL query, I'm getting the data in this format. Fiscal period are 1-6 ,starting from any month and ending at 6 without gap, Gap between starting and end month.( in this case the missing month needs a new record )

Example data :

Order No      Date
100      1-Jan-13
100      2-Jan-13
100      4-Feb-13
100      6-Apr-13
100      8-May-13
100      10-Jun-13
200      6-Apr-13
200      7-May-13
200      8-Jun-13
300      1-Jan-13
300      7-May-13
300      8-Jun-13

Expected Result

Order No      Date      Period
100      1-Jan-13      1
100      2-Jan-13      1
100      4-Feb-13      2
100      6-Apr-13      4
100      8-May-13      5
100      10-Jun-13      6
200      6-Apr-13      4
200      7-May-13      5
200      8-Jun-13      6
300      1-Jan-13      1
300      1-Feb-13      2
300      1-Mar-13      3
300      1-Apr-13      4
300      7-May-13      5
300      8-Jun-13      6

Thanks

PortletPaul

Sorry but I don't understand the question. I think you are asking for:

You have a table with dates, and you wish to calculate "fiscal period"

BUT, it looks like "fiscal period" is the same as the month number
Jan = 1, feb = 2, ... Jun = 6

what happens if the dates are
Jul, Aug, Sep ... Dec ?

Plus:
These are the 3 extra rows
300 1-Feb-13 2
300 1-Mar-13 3
300 1-Apr-13 4

Why is this not required as well?
100 1-Mar-13

awking00

Your question is a little ambiguous. Can you provide a more detailed description of what you're trying to accomplish?

tech29

ASKER

Hi PortletPaul/awking00,

There are three orders i.e. 100,200,300

Order Number 100 : Starting Date 01-Jan-2013 and End Date 10-June-2013. Orders are placed every month so there is no issue.

Order Number 200 : Starting Date = 6-Apr-13 and End Date =8-Jun-13. Orders are placed every month from 6-Apr-13 till End date 8-Jun-13, no issue with data.

Order Number 300 : Starting Date = 1-Jan-13 and End Date 8-Jun-13 . Orders are missed in few months in this range i.e. Feb, March,April. In such case 3 new records to be added with first day of the missing records..i.e.

300 01-Feb-2013
300 01-Mar-2013
300 01-Apr-2013

Thanks

Sean Stuber

>>> Order Number 100 : Starting Date 01-Jan-2013 and End Date 10-June-2013. Orders are placed every month so there is no issue.

there is no March order for 100 in your sample data, I assume that means one should be filled in for March 1, 2013 ?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

You still haven't answered my questions (one shared with sdstuber now).

Why is March not required for order 100?

and:
Is fiscal period just the month number?

What is supposed to happen if the sample data was 1, 3 or 6 months later?

====
Using the query by sdstuber on your sample data, it produces the following result:

| ORDERNO |                       ORDERDATE | PERIOD |
|---------|---------------------------------|--------|
|     100 |  January, 01 2013 00:00:00+0000 |      1 |
|     100 |  January, 02 2013 00:00:00+0000 |      1 |
|     100 | February, 04 2013 00:00:00+0000 |      2 |
|     100 |    March, 01 2013 00:00:00+0000 |      3 | << This is missing in your sample
|     100 |    April, 06 2013 00:00:00+0000 |      4 |
|     100 |      May, 08 2013 00:00:00+0000 |      5 |
|     100 |     June, 10 2013 00:00:00+0000 |      6 |
|     200 |    April, 06 2013 00:00:00+0000 |      4 |
|     200 |      May, 07 2013 00:00:00+0000 |      5 |
|     200 |     June, 08 2013 00:00:00+0000 |      6 |
|     300 |  January, 01 2013 00:00:00+0000 |      1 |
|     300 | February, 01 2013 00:00:00+0000 |      2 |
|     300 |    March, 01 2013 00:00:00+0000 |      3 |
|     300 |    April, 01 2013 00:00:00+0000 |      4 |
|     300 |      May, 07 2013 00:00:00+0000 |      5 |
|     300 |     June, 08 2013 00:00:00+0000 |      6 |

Open in new window

If 1 month is added to the sample orderdate, using the same query the result would be:

| ORDERNO |                       ORDERDATE | PERIOD |
|---------|---------------------------------|--------|
|     100 | February, 01 2013 00:00:00+0000 |      2 |
|     100 | February, 02 2013 00:00:00+0000 |      2 |
|     100 |    March, 04 2013 00:00:00+0000 |      3 |
|     100 |    April, 01 2013 00:00:00+0000 |      4 |
|     100 |      May, 06 2013 00:00:00+0000 |      5 |
|     100 |     June, 08 2013 00:00:00+0000 |      6 |
|     100 |     July, 10 2013 00:00:00+0000 |      7 |
|     200 |      May, 06 2013 00:00:00+0000 |      5 |
|     200 |     June, 07 2013 00:00:00+0000 |      6 |
|     200 |     July, 08 2013 00:00:00+0000 |      7 |
|     300 | February, 01 2013 00:00:00+0000 |      2 |
|     300 |    March, 01 2013 00:00:00+0000 |      3 |
|     300 |    April, 01 2013 00:00:00+0000 |      4 |
|     300 |      May, 01 2013 00:00:00+0000 |      5 |
|     300 |     June, 07 2013 00:00:00+0000 |      6 |
|     300 |     July, 08 2013 00:00:00+0000 |      7 |

Open in new window

or if 3 months was added to the sample:

| ORDERNO |                        ORDERDATE | PERIOD |
|---------|----------------------------------|--------|
|     100 |     April, 01 2013 00:00:00+0000 |      4 |
|     100 |     April, 02 2013 00:00:00+0000 |      4 |
|     100 |       May, 04 2013 00:00:00+0000 |      5 |
|     100 |      June, 01 2013 00:00:00+0000 |      6 |
|     100 |      July, 06 2013 00:00:00+0000 |      7 |
|     100 |    August, 08 2013 00:00:00+0000 |      8 |
|     100 | September, 10 2013 00:00:00+0000 |      9 |
|     200 |      July, 06 2013 00:00:00+0000 |      7 |
|     200 |    August, 07 2013 00:00:00+0000 |      8 |
|     200 | September, 08 2013 00:00:00+0000 |      9 |
|     300 |     April, 01 2013 00:00:00+0000 |      4 |
|     300 |       May, 01 2013 00:00:00+0000 |      5 |
|     300 |      June, 01 2013 00:00:00+0000 |      6 |
|     300 |      July, 01 2013 00:00:00+0000 |      7 |
|     300 |    August, 07 2013 00:00:00+0000 |      8 |
|     300 | September, 08 2013 00:00:00+0000 |      9 |

Open in new window

Would these results be correct for those adjustments to your sample data?

see: http://sqlfiddle.com/#!4/03d2e/12

tech29

ASKER

Hi PortletPaul,

Sorry I missed March..thanks for your help

PortletPaul

Oh dear, that's not right. I did not solve the question - all I have done is ask questions.

The only offered solution was by sdstuber.

Sean Stuber

http:#a39698779 provided the accepted answer