Retrieving the statuses of orders on certain dates using Oracle SQL

Question

I am having difficulty creating a Oracle query to report the historical status of orders on certain days (the first of each month over a series of months). I have searched and searched and have found no one asking a similar question. It seems like a straighforward problem so hopefully someone can help! Here is my example:

ORDERS table:

    ORDER_NUMBER   STATUS    DATE
    50001000       Created   01-15-2010
    50001000       Released  02-20-2010
    50001000       Completed 02-25-2010
    50001000       Closed    03-10-2010
    50001035       Created   01-20-2010
    50001035       Released  01-25-2010
    50001035       Completed 04-05-2010
    50001035       Closed    05-30-2010

So the output I am needing is the status of each order at the beginning of each month. Something like this:

    DATE        ORDER_NUMBER   STATUS
    12-01-2009  
    01-01-2010  
    02-01-2010  50001000       Created   
    02-01-2010  50001035       Released
    03-01-2010  50001000       Completed
    03-01-2010  50001035       Released
    04-01-2010  50001000       Closed
    04-01-2010  50001035       Released
    05-01-2010  50001000       Closed
    05-01-2010  50001035       Completed 
    06-01-2010  50001000       Closed
    06-01-2010  50001035       Closed
    07-01-2010  50001000       Closed
    07-01-2010  50001035       Closed
    ..etc

Are there some native keywords that can make this work without lots of joins and subqueries?

Thanks,

Garrett

Sounds like you can partition by the month/year and then choose the status with the max date for each order. Perhaps this thread may help: http://stackoverflow.com/questions/561836/oracle-partition-by-keyword Also, shouldn't status be `Released` on 02-01-2010 for Order `50001035` since it was released on 01-25-2010? — user1766760, Mar 14 '13 at 21:05
Yes you are correct I had an error in my example and have now changed it to Released on 02-01-2010. Thanks! — Garrett, Apr 02 '13 at 15:50

score 1 · Answer 1 · answered Mar 14 '13 at 23:29

It took a while, but I think this would be what you're looking for:

select to_char(mf.month_first, 'MON-YYYY'), 
       o.order_name,
       o.status
  from (select add_months(to_date('01-DEC-2009'), level-1) month_first
          from dual
       connect by level <= 12) mf
  left outer join orders o
    on trunc(o.status_date, 'MM') <= mf.month_first
 where not exists(
     select 1
       from orders
      where ((trunc(status_date, 'MM') = trunc(o.status_date, 'MM')
              and status_date < o.status_date)
          or (trunc(status_date, 'MM') != trunc(o.status_date, 'MM') 
              and status_date >= o.status_date))
        and trunc(status_date, 'MM') <= mf.month_first
        and order_name = o.order_name)
 order by mf.month_first, o.order_name

The above query ensures that if there are two status changes in the same month, the first one is displayed, which is why the comparison between status_date and o.status_date happens twice, once for when you're in the same month, once for different months...

The connect by level subselect allows you to vary the date range by specifying a first date of the month (01-DEC-2009 in this case) and a duration/length of the report (12 months).

I hope this is what you were after, though I have to say if you've got lots of orders, this will most likely create lots of rows (especially if an order is completed in March, it'll show up as Closed until the end of the report.

Here's a SQLFiddle to see it working.

Thank you for the reply I ran this query on our server and it seems like it times out. But I also looked into the LEAD ... OVER function (joined on first_day_of_month BETWEEN status_date AND next_status_date; and also looked into MAX ... OVER ... PARTITION BY joined on status_date < first_day_of_month and both are working. Thanks for your help! — Garrett, Apr 02 '13 at 16:10

score 0 · Answer 2 · answered Mar 14 '13 at 20:39

0

Not sure that I understood your sample data but here's the query that may help you - compare your date to the first day of each month:

SELECT * FROM your_table 
 WHERE your_date IN
(
 -- This query will give you the first day of each month --
 Select Add_Months(Trunc(Sysdate,'YEAR'),Level-1) first_day_of_month
   From dual
 Connect By Level <= 12 -- number of months in a year --
)

/

answered Mar 14 '13 at 20:39

Art

5,616
1
20
22

Yes that is good, I like the query being dynamic based on today instead of a hardcoded date. Thanks! – Garrett Apr 02 '13 at 15:59

Retrieving the statuses of orders on certain dates using Oracle SQL

2 Answers2