Date compare question in PLSQL 1

[cassie99]

Hello,

I have a database table that I can query for store items. Each item in the table has an associated purchase date and an associated store. How would you suggest writing a PLSQL script that first sorts the data in the table by store, item and purchase date and then only prints out only those stores and items with purchase dates within 3 days of each other. Thanks in advance.

-Cassie

Store: A
--Item: 123
----Purchase Date: 01-Jan-2011
--Item: 456
----Purchase Date: 02-Jan-2011

Note that Item: 789 would not be displayed because the purchase date is 07-Jan-2011

 

[SantaMufasa]

Cassie,

We are very happy and eager to help, but rather than our composing a script for you, please post your best effort so far, then we can offer suggestions to help you get to "the finish line".

Also, it would be very helpful to us if you can post a CREATE TABLE <table_name>... statement and a few INSERT INTO <table_name>... statements that will save us having to "re-invent the wheel" to compose working code that approximates your environment.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[cassie99]

Thanks SantaMufasa.

Was able to solve this with PARTITION BY and RANGE BETWEEN INTERVAL.

Will take your advice (above) when I post my next question.

-Cassie

 

[SantaMufasa]

Cassie, Does your focus table contain massive amounts of data? If not, using PARTITION BY and RANGE BETWEEN INTERVAL is like using a sledge hammer to kill a termite.

You can get the results you want from a standard table with a standard SQL query.

...But...if (your sledgehammer) ain't broke, then no need to fix it, right?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[cassie99]

Hi Mufasa,

Yes. Depending on the schema we are querying, this target table may contain five hundred thousand records or more.

Thanks.

-Cassie

 

[Beilstwh]

something else is causing your problems, lack of proper indexes or current statistics. I have tables with 250,000,000 rows in them with no partitioning and have instantaneous access to our data.

Bill
Lead Application Developer
New York State, USA

 

[Dagon]

I think there might be some confusion here. I don't think (although I may be wrong) that casie99 actually partitioned the table. I presume it's a reference to the PARTITION BY and RANGE BETWEEN INTERVAL that can be used in OLAP-type select statements e.g.

select 
store,
purchase_date,
COUNT(*) OVER ( PARTITION BY  store, item
                             ORDER BY    purchase_date
                       RANGE BETWEEN INTERVAL '3' DAY PRECEDING
                                AND INTERVAL '3' DAY FOLLOWING)
from table

For Oracle-related work, contact me through Linked-In.

 

[cassie99]

Dagon,

You are right on target! Thanks for clarifying.

-Cassie