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