Multiple table data retrieval into separate table

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • mksmith
    New Member
    • Dec 2007
    • 1

    #1

    Multiple table data retrieval into separate table

    Greetings,

    I have an empty table in which i want to populate using data from another table. The following query will only populate the "CASH" column in the destination table, however, i need to populate 2 others columns in the same query by changing the WHERE clause criteria. The following query works for part of my problem:

    INSERT [HU_TEST].DBO.CLIENT_DET AIL
    (CLIENT_ID,
    CASH)
    SELECT [HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM .ACCOUNTNUMBER,
    SUM(DBO.E2E_POS SUM.TOTALVALUE) AS CASH
    FROM [HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM
    WHERE ((([HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM .ASSET) IN ('CA')))
    GROUP BY DBO.E2E_POSSUM. ACCOUNTNUMBER

    However, I have two more columns i need to populate at the same time, the only that changes in order to obtain the correct value is that the line that states:
    WHERE ((([HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM .ASSET) IN ('CA')))
    Will change to :
    WHERE ((([HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM .ASSET) IN ('CA','EQ')))


    Any help would be greatly appreciated.

    Thanks in advance.

    Michael Smith
  • ck9663
    Recognized Expert Specialist
    • Jun 2007
    • 2878

    #2
    Originally posted by mksmith
    Greetings,

    I have an empty table in which i want to populate using data from another table. The following query will only populate the "CASH" column in the destination table, however, i need to populate 2 others columns in the same query by changing the WHERE clause criteria. The following query works for part of my problem:

    INSERT [HU_TEST].DBO.CLIENT_DET AIL
    (CLIENT_ID,
    CASH)
    SELECT [HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM .ACCOUNTNUMBER,
    SUM(DBO.E2E_POS SUM.TOTALVALUE) AS CASH
    FROM [HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM
    WHERE ((([HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM .ASSET) IN ('CA')))
    GROUP BY DBO.E2E_POSSUM. ACCOUNTNUMBER

    However, I have two more columns i need to populate at the same time, the only that changes in order to obtain the correct value is that the line that states:
    WHERE ((([HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM .ASSET) IN ('CA')))
    Will change to :
    WHERE ((([HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM .ASSET) IN ('CA','EQ')))


    Any help would be greatly appreciated.

    Thanks in advance.

    Michael Smith
    this query will store the sum(TOTALVALUE) for all records where ASSET = 'CA' or 'EQ'. CASH_CA will contain sum(TOTALVALUE) for ASSET = 'CA' only. CASH_EQ will contain sum(TOTALVALUE) for ASSET = 'EQ' only. you can rename these fields accordingly.

    Code:
    INSERT [HU_TEST].DBO.CLIENT_DETAIL
    (CLIENT_ID, CASH, CASH_CA, CASH_EQ)
    
    SELECT [HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM.ACCOUNTNUMBER,
    SUM(DBO.E2E_POSSUM.TOTALVALUE) AS CASH,
    
    SUM(
       case when [HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM.ASSET = 'CA' then
          DBO.E2E_POSSUM.TOTALVALUE
       else
          0
       end
       ) AS CASH_CA,
    
    SUM(
       case when [HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM.ASSET = 'EQ' then
          DBO.E2E_POSSUM.TOTALVALUE
       else
          0
       end
       ) AS CASH_EQ
    FROM [HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM
    WHERE ((([HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM.ASSET) IN ('CA', 'EQ')))
    GROUP BY DBO.E2E_POSSUM.ACCOUNTNUMBER
    -- CK

    Comment

    Working...