Querying to find end-of-day counts - tough nut!

[doddy]

Greetings from Adelaide, Australia.

I have a table STATUS that maps the changing status of orders, that can move through RECEIVED, VALID, INVALID, COMPLETED. Each status has a timestamp when it became that status. eg.

order no. status statustimestamp
1234 REC 22-JUL-2005 14:56
1234 INV 22-JUL-2005 14:59
1234 COM 26-JUL-2005 09:05

I am trying to get a daily snapshot of the number of orders in the error queue (INV status) at the end of each day, and need to be able to do this for prior dates.

It's a bit like a warehouse end-of-day inventory I suppose, where the count of orders in INV status is equivalent to the stock-on-hand.

I can see that the report date needs to be between the INV status timestamp and the next one (if it has since moved to a different status) and I think I may need to use two copies of the STATUS table to do this.

Can anyone help with an approach which for a given date, will reveal a count of orders that were still invalid at the end of that date, and will also be able to retrieve the timestamp to allow me to calculate how many of that total had been in the INV status for less than 48 hours. For that calculation I'll probably use something like sum(decode((sign((report_date+1) - statustimestamp)-2),-1,1,0)).

I can't use PL/SQL in this case (insufficient grants), just normal querying DML for Oracle 8.

regards,
Doddy

 

[SantaMufasa]

Doddy,

Here are some sample data:

col status format a6
col b heading "STATUSTIMESTAMP" format a17
select order_no, status, to_char(statustimestamp,'dd-MON-yyyy hh24:mi') b
  from status
 order by order_no,statustimestamp;

  ORDER_NO STATUS STATUSTIMESTAMP
---------- ------ -----------------
      1234 REC    22-JUL-2005 14:56
      1234 INV    22-JUL-2005 14:59
      1234 COM    26-JUL-2005 09:05
      1235 REC    22-JUL-2005 14:57
      1235 INV    22-JUL-2005 15:01
      1235 VAL    23-JUL-2005 10:55
      1235 COM    26-JUL-2005 09:05
      1236 REC    22-JUL-2005 15:05
      1236 INV    22-JUL-2005 15:20
      1236 VAL    23-JUL-2005 10:56
      1236 COM    26-JUL-2005 09:05
      1237 REC    22-JUL-2005 15:26
      1237 COM    26-JUL-2005 09:05
      1238 REC    22-JUL-2005 15:30
      1238 INV    22-JUL-2005 15:32
      1238 VAL    22-JUL-2005 15:45
      1238 COM    22-JUL-2005 17:00

17 rows selected.

Here is code that I stored in a script named "TT_265b.sql":

set echo off
set verify off
accept report_date prompt "Enter the Report Date (dd-MON-yyyy): "
col a heading "Count|of|INVALID|status|as of|EOD on|&report_date" format 9,999
col b heading "Count|of|INVALIDs|in that|status|> 48 hrs." format 9,999
select count(*) a
      ,count(
         decode(
           sign(2-(b.statustimestamp-a.statustimestamp))
           ,-1,'x'
           ,null)
            ) b
from status a, status b
where a.status = 'INV'
  and to_date('&report_date 23:59:59','dd-mon-yyyy hh24:mi:ss')
      between a.statustimestamp and b.statustimestamp
  and a.order_no = b.order_no
  and b.statustimestamp =
      (select min(statustimestamp)
         from status
        where statustimestamp > a.statustimestamp
          and order_no = a.order_no);

Here is an invocation of TT_265b.sql and its results:

SQL> @tt_265b
Enter the Report Date (dd-MON-yyyy): 22-jul-2005

      Count
         of     Count
    INVALID        of
     status  INVALIDs
      as of   in that
     EOD on    status
22-jul-2005 > 48 hrs.
----------- ---------
          3         1

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[doddy]

Mufasa,

You've done a top job here. I had tried 7 different approaches (using various subquerying methods, correlated or otherwise) and yours is now an eighth version that I can see should deliver the goods. Many thanks indeed for your efforts.

However, I think it suffers from the same issue my working versions did, in that the query just doesn't return (I killed it after 2900 secs, that for one day's data only). The STATUS table discussed gets some 12 million new records per year, and I'm guessing it doesn't appreciate being joined to itself.

Can you recommend any hints that might be helpful for such a large table being self-joined? It is indexed only on ORDER_NO, which was our join. I'm a novice on hints, but I was wondering whether something like an INDEX(a) USE_NL(b)
might be of benefit?

regards,
Doddy

 

[SantaMufasa]

Doddy,

If you do not have an additional index on at least "statustimestamp", then yes, no matter what else you do, even with hints, the query will take too long for satisfaction. If you need to run this query frequently, then I recommend either partitioning your data by "statustimestamp" or creating a temporary table with an extraction of all status rows for order_nos that have "INV" as one of its statuses and the report_date is between the "INV" status date and the next status.

Either alternative should run much faster than your current execution time.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

I think that the best thing you may do is to store status timestamps with orders. As I may see a set of statuses is predefined thus you may add 4 columns and make your life much easier. I suspect that your application doesn't even control whether COM timestamp is greater than REC one.

I can't see your 7 methods but you may try to get all that information from this query:

SELECT a.order_no,
       a.statustimestamp inv_timestamp,
       b.statustimestamp com_timestamp
  FROM status a, status b
 WHERE a.status = 'INV'
   AND a.statustimestamp < trunc(:report_date) + 1
   AND a.order_no = b.order_no(+)
   AND b.status(+) = 'COM'
   AND (b.statustimestamp IS NULL OR
       b.statustimestamp >= trunc(:report_date) + 1)

Sure you should add an outer select with DECODE or CASE or anything else you prefer.




Regards, Dima

http://seminihin.narod.ru