CODEWITHSUNDEEP
sqloracle-database
Garrett
Garrett

Reputation: 21

Retrieving the statuses of orders on certain dates using Oracle SQL

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

Upvotes: 2

Views: 160

Answers (2)

beny23
beny23

Reputation: 35068

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.

Upvotes: 1

Art
Art

Reputation: 5792

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 --
)

/

Upvotes: 0

Related Questions