SQL query, please help urgent
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SQL query, please help urgent

  1. #1
    Join Date
    Jan 2006
    Posts
    5

    SQL query, please help urgent

    Hi,
    I want some help in writing a SQL Query .Its besically a hierarchical query. Let me lay down the table structure first to explain my requirements better.

    PORP_TABLE(NODE_LEVEL int, WBS_ID int, WBS_NUMBER varchar(60), LFT int,RGT int)

    SELECT NODE_LEVEL, WBS_ID, LFT,RGT FROM PROPOSAL_WBS PW WHERE PROPOSAL_REV_ID = 7000

    (SAMPLE DATA)

    NODE WBS
    LEVEL WBS_ID NUMBER LFT RGT
    ------------------------------------------------------------------------
    0 7055 ROOT 1 24
    1 7056 1 2 5
    1 7088 2 6 9
    2 7057 1.1 3 4
    2 7089 2.1 7 8
    2 7091 3.1 11 14
    2 7103 3.2 15 16
    2 7105 4.1 19 20
    1 7090 3 10 17
    3 7092 3.1.1 12 13
    1 7104 4 18 23
    2 7106 4.2 21 22


    ALLOCATION_DETAIL( WBS_ID int, COST_ID int, PERIOD Date, AMOUNT Float)

    sample data
    WBS_ID , COST_ID , PERIOD , AMOUNT
    ------------------------------------------------------------
    7057 100 01-jan-2005 5000
    7057 100 01-feb-2005 2000
    7057 100 01-mar-2005 1000
    7057 100 01-apr-2005 6000
    7057 100 01-may-2005 3000
    7057 100 01-jun-2005 45000
    7106 100 01-mar-2005 8000
    7106 100 01-apr-2005 7000
    7106 100 01-may-2005 9000


    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.

    SO THE RESULT SET SHOULD BRING LIKE THIS

    WBS_NUMBER, PERIOD_NUMER, COST_CATEGORY , AMOUNT
    --------------------------------------------------------------------------------------------------
    ROOT
    ......
    ......
    1
    ......
    ......
    1.1
    ......
    ......
    2
    ......
    ......
    2.1
    ......
    ......
    3
    ......
    ......
    3.1
    ......
    ......
    3.1.1
    ......
    ......
    3.2
    ......
    ......
    4
    ......
    ......
    4.1
    ......
    ......
    4.2
    ......
    ......

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Now there is No connect by Prior or any analytic functions available for this . THIS NEEDS TO BE DONE ONLY THROUGH TRADITIONAL SQL STATEMENT .
    Are you using Informix, the dead product?

    Why don't you post the create and insert statements for all tables?


    Tamil

  3. #3
    Join Date
    Jan 2006
    Posts
    5
    No I am using Sybase ASE.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    err this is an oracle forum

  5. #5
    Join Date
    Jan 2006
    Posts
    5
    I know this is an Oracle Forum . But this is a SQL question. So any help will be appreciated.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Why don't you post the create and insert statements for all tables?

    Tamil

  7. #7
    Join Date
    Jan 2006
    Posts
    5
    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).

    PERIOD_YEAR ----------------------------->
    WBS NUMBER , COST_CATEGORY
    || || amount---------->
    || || |
    || || |
    \/ \/ \/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width