All Forums Database
lunate 2 posts Joined 07/07
31 Jul 2007
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 Amount200601 1 9999999 1000200602 1 9999999 2000200605 1 9999999 1320200607 1 9999999 1418200612 1 9999999 1501Output table has to be something like this.Month Customer_Cd Telephone Number Bill Amount200601 1 9999999 1000200602 1 9999999 2000200603 1 9999999 2000200604 1 9999999 2000200605 1 9999999 1320200606 1 9999999 1320200607 1 9999999 1418200608 1 9999999 1418200609 1 9999999 1418200610 1 9999999 1418200611 1 9999999 1418200612 1 9999999 1501

sachinp17 53 posts Joined 11/06
31 Jul 2007

Try it using cursor.Regds,sachin

Jim Chapman 449 posts Joined 09/04
31 Jul 2007

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.

dnoeth 4628 posts Joined 11/04
31 Jul 2007

Snodgrass's book is available as PDF:http://www.cs.arizona.edu/people/rts/publications.htmlI 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

Dieter

You must sign in to leave a comment.