If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Now the PORP_TABLE has got the parents and childs. Only the leaf nodes in the hierarchy has the values stored in the ALLOCATION_DETAIL table. Now here is the scenario

In the example 7055 is the root WBS . The Leaf WBS are the one with max extension in the wbs number ( in this case it is 1.1, 2.1, 3.1.1, 3.2, 4.1 and 4.2)

Now the Starting period for each leaf node in the ALLOCATION_TABLE could be differrent . What that means is WBS 1.1 could start in Jan -2003 and WBS 3.1 Could be Jul-2005 . So the ending perios are also differrent for differrent WBS . Some can span 2 years some can 5 years.

So how to write a query so it retrieves the value for all the Wbs starting from the MIN ( PERIOD ) upto the MAX(PERIOD), and it should roll up also. Now there is No connect by Prior or any analytic functions available for this . THIS NEEDS TO BE DONE ONLY THROUGH TRADITIONAL SQL STATEMENT . And NO DB FUNCTIONS CAN BE USED .
Now if the WBS is a parent node then it should have the sum of all its child nodes for the COST category.

hi Tamil,
The table has got more fields, but I have selected few fields which are required. And the data is saved into these table through portals. I am trying to create a crosstab ( matrix) report using Crystal Report 10 . So it lists, the leaf WBS with their data for each cost category for all the periods ( so if for a perticular wbs , and period there is no amount then it lists 0 (zero) . And then the roll up for the parents . So basically the report should look like this ( with the hirarchy of wbs like root, then parent then child and leaf).