I have a temp table that has a SecurityCode
column. This SecurityCode
column gets updated based off value from a CrossReference
table.
The value selected from the CrossReference
table depends on how much data that row contains.
So the first update matches on CustomIdentifier
. The 2nd update checks if CrossReference
table CounterpartyID
col has a value and updates relevant to that row. The 3rd update checks if CrossReference
table CounterpartyID
and AccountCode
columns have values and updates relevant to that row.
INSERT into @ManagerData
SELECT
ManagerID,
SecurityManager,
SecurityAccount,
null as SecurityCode,
Broker,
AssetType,
InvestmentType,
Description,
Currency,
sum(Quantity) as Quantity
FROM
dbo.ManagerData
WHERE (ManagerID = @ManagerID)
AND (Account = @Account)
GROUP BY ManagerID, Account, Broker,AssetType,InvestmentType,Description, Currency
-- Update with Code Cross Reference By Instrument Identifier Only
Update @ManagerData
Set SecurityCode = CrossReference.GenevaCode
From @ManagerData MD
Inner Join CrossReference
On MD.ManagerID = CrossReference.CustomIdentifier
WHERE CrossReference.CounterPartyID = ''
AND CrossReference.AccountCode = ''
-- Update with Code Cross Reference By CounterParty & Instrument Identifier
Update @ManagerData
Set SecurityCode = CrossReference.GenevaCode
From @ManagerData MD
Inner Join CrossReference
On MD.ManagerID = CrossReference.CustomIdentifier
AND MD.SecurityManager = CrossReference.CounterPartyID
WHERE CrossReference.AccountCode = ''
-- Update with Code Cross Reference By CounterParty, CounterParty Account & Instrument Identifier
Update @ManagerData
Set SecurityCode = CrossReference.GenevaCode
From @ManagerData MD
Inner Join CrossReference
On MD.ManagerID = CrossReference.CustomIdentifier
AND MD.SecurityManager = CrossReference.CounterPartyID
AND MD.SecurityAccount = CrossReference.AccountCode
How do I improve this script into a single query if possible? I have tried something similar to below...
SELECT
ManagerID,
SecurityManager,
SecurityAccount,
null as SecurityCode,
Broker,
AssetType,
InvestmentType,
Description,
Currency,
sum(Quantity) as Quantity
FROM
dbo.ManagerData MD
LEFT Join CrossReference cr
On MD.ManagerID = cr.CustomIdentifier
AND (cr.CounterPartyID = MD.SecurityManager OR cr.CounterPartyID = '')
AND (cr.AccountCode = MD.SecurityAccount OR cr.AccountCode = '')
WHERE (ManagerID = @ManagerID)
GROUP BY ManagerID, Account, Broker,AssetType,InvestmentType,Description, Currency
Aucun commentaire:
Enregistrer un commentaire