Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Location
    Vienna
    Posts
    24

    Unanswered: 2 tricky problems, self join & aggregate function in update statement

    Dear all,
    I've got a table containing financial data for several projects. The money is splitted into several accounts and per year.

    The table looks basically like this:
    Code:
    PROJECT_ID | ACCOUNT | YEAR  |  BUDGET  | EXPENDITURE
    -----------------------------------------------------
    PROJ_1     | 1100    | 2004  |  12,000  | 10,000     
    PROJ_1     | 1100    | 2003  |   5,000  |  3,000     
    PROJ_1     | 1100    | 2002  |   1,000  |    500     
    PROJ_1     | 1100    | 2001  |   4,000  |  2,000     
    PROJ_1     | 5100    | 2005  |  10,000  |  NULL
    I would like to extract this information into another table, and have information from the past and the future per PROJECT_ID/ACCOUNT/YEAR so it would look like this:

    Code:
    PROJECT_ID | ACCOUNT | YEAR  | ... | LAST_YEAR_BUDGET | PREVIOUS_YEARS_EXPENDITURES
    ----------------------------------------------------------------------------------
    PROJ_1     | 1100    | 2004  | ... |  ?               | ?
    PROJ_1     | 1100    | 2003  | ... |  ?               | ?
    PROJ_1     | 1100    | 2002  | ... |  ?               | ?
    PROJ_1     | 1100    | 2001  | ... |  ?               | ?
    PROJ_1     | 5100    | 2004  | ... |  ?               | ?
    Now my first problem is, in my new table, I would like to insert every account that exists for a project in combination with every year that exists for a project.

    I managed to do that with a SELECT DISTINCT on a self join with a join condition on PROJECT_ID, but it performs horribly, and I'm sure I'm missing something for this problem, because without the DISTINCT it gives me back 10 times the number of rows I expected.

    The other problem is that I need a SUM of all the expenditures from previous years,so for say, PROJECT_ID 'PROJ_1', ACCOUNT '1100', YEAR 2004 I need a sum of years 2001 - 2003 for this account and project.

    I have to do this in an UPDATE FROM statement, and I have no clue how to do this, because UPDATE FROM doesn't allow you to use GROUP BY it seems.

    I want to avoid subqueries and cursors, because the amount of data I'm dealing with is huge, and building the new table should be as fast as possible.

    I am grateful for every help on this issue,

    Thanks.
    kdb is the solution of all problems.
    Just try it, www.kx.com

  2. #2
    Join Date
    Aug 2004
    Posts
    42
    Example below uses 1 SELECT no UPDATE. Will need index on PROJECT_ID, ACCCOUNT, YEAR to make efficient.


    create table #t
    (
    PROJECT_ID char(10)
    ,ACCOUNT int
    ,YEAR int
    ,BUDGET int
    ,EXPENDITURE int NULL
    )

    insert #t select "PROJ_1" , 1100 , 2004 , 12000 ,10000
    insert #t select "PROJ_1" , 1100 , 2003 , 5000 , 3000
    insert #t select "PROJ_1" , 1100 , 2002 , 1000 , 500
    insert #t select "PROJ_1" , 1100 , 2001 , 4000 , 2000
    insert #t select "PROJ_1" , 5100 , 2005 , 10000 , NULL

    select PROJECT_ID
    ,ACCOUNT
    ,YEAR = max (YEAR)
    ,LAST_YEAR_BUDGET = sum (case when YEAR =
    (select max (YEAR)
    from #t b
    where a.PROJECT_ID = b.PROJECT_ID
    and a.ACCOUNT = b.ACCOUNT)
    then BUDGET
    else 0
    end
    )
    ,PREVIOUS_YEARS_EXPENDITURE =
    sum (case when YEAR <
    (select max (YEAR)
    from #t b
    where a.PROJECT_ID = b.PROJECT_ID
    and a.ACCOUNT = b.ACCOUNT)
    then EXPENDITURE
    else 0
    end
    )
    into #u
    from #t a
    group by PROJECT_ID
    ,ACCOUNT

Posting Permissions

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