Toad World® Forums

Need query to look at cost changes for past 3 years

Hello All,

I’m working in TOAD for Oracle. I have an Acquistions_Cost table. I have to find the change of cost for the last three years. It has four(4) colunms: Item_no, Update_date, Cost and Active_flag. This table contants about 1.5 million rows of SKU items. The active_flag has two options (A-Active and I-Inactive). Active means that is the current cost which can be tracked by the Update_date. Some items update every year and some maybe every two to 5, so I can’t just do a minus three years on the Update_date. I need a way to look at data and get the cost change for last three years. Remember, some may not have changed in 6. This data goes back to 1999. I have aggregated my sales data in another table by calendar year.

Item_no
Update_date
Cost
Active_flag
123
1/1/2013
1.05
A
123
1/1/2012
0.95
I
123
1/1/2011
1.01
I
222
1/1/2008
2.23
A
234
1/1/2012
0.65
A
234
1/1/2011
0.52
I
333
1/1/2013
1.65
A
333
1/1/2012
1.5
I
333
1/1/2011
1.76
I
333
1/1/2010
1.62
I

You can have AWR history defined as

select

extract( day from snap_interval) 2460+

extract( hour from snap_interval) *60+

extract( minute from snap_interval ) “Snapshot Interval”,

extract( day from retention) 2460+

extract( hour from retention) *60+

extract( minute from retention ) “Retention Interval”

from dba_hist_wr_control;

You can change settings by:

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(43200, 15);

But still cannot have all in database (too much data).

How to store AWR outside of database in html file: damir-vadas.blogspot.com/…/automated-awr-reports-in-oracle-10g11g.html

Hope this helps.

Damir

This is a SQL question, not a Toad question, but: LEAD and LAG are your friends; make their acquaintance. You can do something like this:

with
price_by_date_range as
(

select
item_no, update_date
as first_date,

(lead(update_date)
over
(partition
by item_no
order
by update_date))

1
as last_date,

cost

from input_table)

select
item_no,
cost
as cost_3_years_ago

from price_by_date_range

where
add_months(trunc(sysdate),
-36)
between first_date
and
NVL(last_date,
trunc(sysdate))

(Need the NVL because last_date will be null if the most recent update for a product is more than 3 years ago)

Nate Schroeder

US Row Crops IT Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. G3WB - Saint Louis, MO - 63167

314-694-2592

From: mark1906 [mailto:bounce-mark1906@toadworld.com]

Sent: Monday, March 17, 2014 1:06 PM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Need query to look at cost changes for past 3 years

Need query to look at cost changes for past 3 years

Thread created by mark1906

Hello All,

I’m working in TOAD for Oracle. I have an Acquistions_Cost table. I have to find the change of cost for the last three years. It has four(4) colunms: Item_no, Update_date, Cost and Active_flag.
This table contants about 1.5 million rows of SKU items. The active_flag has two options (A-Active and I-Inactive). Active means that is the current cost which can be tracked by the Update_date. Some items update every year and some maybe every two to 5, so
I can’t just do a minus three years on the Update_date. I need a way to look at data and get the cost change for last three years. Remember, some may not have changed in 6. This data goes back to 1999. I have aggregated my sales data in another table by calendar
year.

Item_no

Update_date

Cost

Active_flag

123

1/1/2013

1.05

A

123

1/1/2012

0.95

I

123

1/1/2011

1.01

I

222

1/1/2008

2.23

A

234

1/1/2012

0.65

A

234

1/1/2011

0.52

I

333

1/1/2013

1.65

A

333

1/1/2012

1.5

I

333

1/1/2011

1.76

I

333

1/1/2010

1.62

I

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled

to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and

all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its

subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of “Viruses” or other “Malware”.

Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying

this e-mail or any attachment.

The information contained in this email may be subject to the export control laws and regulations of the United States, potentially

including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of

Treasury, Office of Foreign Asset Controls (OFAC). As a recipient of this information you are obligated to comply with all

applicable U.S. export laws and regulations.

Thank you and my apologies for posting this in the wrong group.

Hello Nate Schroeder,

My first ordered row has to be where Active_flag is = A, remember, so items haven’t been updated in years.

I’m thinking that it has to be ordered by Item_no, then Active_flag = A, then the next three sku, update_date will be the last three price changes like item_no =333 from the above sample table I provided.

Try it each way, see what you get. Explore! Learn!

Nate Schroeder

US Row Crops IT Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. G3WB - Saint Louis, MO - 63167

314-694-2592

From: mark1906 [mailto:bounce-mark1906@toadworld.com]

Sent: Monday, March 17, 2014 2:07 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Need query to look at cost changes for past 3 years

RE: Need query to look at cost changes for past 3 years

Reply by mark1906

I’m thinking that it has to be ordered by Item_no, then Active_flag = A, then the next three sku, update_date will be the last three price changes like item_no =333 from the above sample table
I provided.

This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled

to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and

all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its

subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of “Viruses” or other “Malware”.

Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying

this e-mail or any attachment.

The information contained in this email may be subject to the export control laws and regulations of the United States, potentially

including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of

Treasury, Office of Foreign Asset Controls (OFAC). As a recipient of this information you are obligated to comply with all

applicable U.S. export laws and regulations.

Ok, thanks

(If I said my last comment was halfway channeling Miss Frizzle, would anyone understand the reference?)

cost changes for past 3 years
if this is tittle, all except how to achieve that is no relevant…o tittle has no sense?
And now I realize that “cost” is some dummy column and not cost plan … O tempera o mores.

I got it - Magic School Bus!

Cost is the same a price, sorry for the oversight. Need to calculate the change from last price change (whether increase for decrease). The cost column is actually the price. Active column (If =A, that means the actual current price). Need to calculate change. Change doesn happened yearly. There are over 700k items.

Kevin wins!