Query Probelm !!

Database
Fan

Query Probelm !!

Dear ALL, I have a problem .kindly help me earlist,

Question:

We need to build a query which will fill up the gaps between different periods in a way that it utilizes the column values of the row which is above it.

Input table would look like this.

Month Customer_Cd Telephone Number Bill Amount
200601 1 9999999 1000
200602 1 9999999 2000
200605 1 9999999 1320
200607 1 9999999 1418
200612 1 9999999 1501

Output table has to be something like this.

Month Customer_Cd Telephone Number Bill Amount
200601 1 9999999 1000
200602 1 9999999 2000
200603 1 9999999 2000
200604 1 9999999 2000
200605 1 9999999 1320
200606 1 9999999 1320
200607 1 9999999 1418
200608 1 9999999 1418
200609 1 9999999 1418
200610 1 9999999 1418
200611 1 9999999 1418
200612 1 9999999 1501

3 REPLIES
Enthusiast

Re: Query Probelm !!

Try it using cursor.

Regds,
sachin
Enthusiast

Re: Query Probelm !!

In other words, you want to expand a query expression into a regular time series. There is a book by Richard Snodgrass, "Developing Time-Oriented Database Applications in SQL" that may be helpful if you can find a copy. I understand it is out of print.

You could create a table function to accomplish what you want. There is probably another solution using a join on the sys_calendar.calendar view, but I'm not clever enough with SQL (and haven't absorbed enough of the Snodgrass book) to tell you specifically how to do that.
Senior Apprentice

Re: Query Probelm !!

Snodgrass's book is available as PDF:
http://www.cs.arizona.edu/people/rts/publications.html

I did something similar for missing dates, the most efficient way was:

Calculate the next date using OLAP-functions and then CROSS JOIN to a table/query with all days WHERE date_col between start_date and next_date.
There are several special cases you must take care off, so it's not that efficient on larger amounts of data...

Dieter