Home » SQL & PL/SQL » SQL & PL/SQL » Missing weeks should contain the value prior to the one that is present
Missing weeks should contain the value prior to the one that is present [message #353943] Wed, 15 October 2008 14:44 Go to next message
yashora
Messages: 39
Registered: August 2006
Member
Hi,

I am having the data as follows: Week will have 52 as the max.

funcunit jo week price
-------------------------------
f1 j1 w1 100
f2 j1 w3 150
f3 j1 w5 200
f4 j2 w9 500
f4 j2 w10 700

I should have the output in such a way that the missing weeks should also come into picture for the funcunit,jo and the price should contain the value prior to the one which are missing.
And the max. no of weeks i do have is 50.

Regards,
Yashoroa

[Updated on: Wed, 15 October 2008 23:41] by Moderator

Report message to a moderator

Re: Missing weeks should contain the value prior to the one that is present [message #353958 is a reply to message #353943] Wed, 15 October 2008 19:41 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
This is a typical problem faced in data warehouses. I am surprised that you do not have an answer from someone else already. You will need to use two specific data transformation processes to get your answer: DATAFILL, and ANALYTICS; and you have the option of using a technique called NUMBER GENERATION to facilitate it all.

Read this for a discussion and an example of all three topics. The third link has the example. The second link gives some background.

Kevin Meade's blog

A Simple Example of Oracle Analytics: Running Totals

The SQL WITH Clause, (Order Your Thoughts, Reuse Your Code)

In the end you will likely do this:

Quote:
1) use ANALYTICS (lead,lag) to pull forward your missing numbers
2) generate a list of weeks
3) join this list of weeks to your data using outer-join to do a DATAFILLL so each week has representation.


Good luck, Kevin
Re: Missing weeks should contain the value prior to the one that is present [message #353980 is a reply to message #353958] Thu, 16 October 2008 00:11 Go to previous message
yashora
Messages: 39
Registered: August 2006
Member
Hi Kevin,

Thank you very much for the links. I will walk through it to have a better understanding on such issues.

Good Luck to you.

Regards,
Yashora
Previous Topic: ORA-01799
Next Topic: Issue with Trigger
Goto Forum:
  


Current Time: Sun Dec 11 02:20:24 CST 2016

Total time taken to generate the page: 0.14127 seconds