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
tech29Asked:
Who is Participating?
 
sdstuberCommented:
if my assumption is correct then try this...


  SELECT orderno,
         CASE
             WHEN COLUMN_VALUE = 1 THEN orderdate
             ELSE ADD_MONTHS(TRUNC(orderdate, 'mm'), COLUMN_VALUE - 1)
         END
             orderdate,
         TO_NUMBER(TO_CHAR(orderdate, 'mm')) + COLUMN_VALUE - 1 period
    FROM (SELECT orderno,
                 orderdate,
                 TRUNC(orderdate, 'mm'),
                 MONTHS_BETWEEN(
                     LEAD(TRUNC(orderdate, 'mm'), 1) OVER(PARTITION BY orderno ORDER BY orderdate),
                     TRUNC(orderdate, 'mm')
                 )
                     gap
            FROM yourtable),
         TABLE(
                 SELECT COLLECT(LEVEL)
                   FROM DUAL
             CONNECT BY LEVEL <= gap
         )
ORDER BY orderno, orderdate
0
 
PortletPaulfreelancerCommented:
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
0
 
awking00Commented:
Your question is a little ambiguous. Can you provide a more detailed description of what you're trying to accomplish?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
tech29Author Commented:
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
0
 
sdstuberCommented:
>>> 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  ?
0
 
PortletPaulfreelancerCommented:
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
0
 
tech29Author Commented:
Hi PortletPaul,

Sorry I missed March..thanks for your help
0
 
PortletPaulfreelancerCommented:
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.
0
 
sdstuberCommented:
http:#a39698779 provided the accepted answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.