Multiple table data retrieval into separate table

[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_DETAIL
(CLIENT_ID,
CASH)
SELECT [HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM.ACCOUNTNUMBER,
SUM(DBO.E2E_POSSUM.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]

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_DETAIL
(CLIENT_ID,
CASH)
SELECT [HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM.ACCOUNTNUMBER,
SUM(DBO.E2E_POSSUM.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.

 
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
 
   end
   ) AS CASH_CA,
 
SUM(
   case when [HU190-PORTFOLIOCENTER].DBO.E2E_POSSUM.ASSET = 'EQ' then
      DBO.E2E_POSSUM.TOTALVALUE
   else
 
   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