Multiple data that has no real links and insert thge into a new table

Transact-SQL
1

sample
sample1221×648 112 KB

hi

just wondering if this is possible and how i would do it.

what i need to do from the screen shot i attached is for account 447217 i need to multiple the %split value by each of the CollateraByCurrency value for each settle currency where trade type is collateral and then insert the data as i have under the new table as in the screen shot.

hopefully i have explained it ok

2

Please provide the sample data in the form of a proper sql script and not an image. Help us help you

3

Sorry should have put same data up

Blockquote

GO

CREATE TABLE [dbo].[Table A](
[ReportDate] [datetime] NULL,
[Broker] nvarchar NULL,
[Counterpartyborrower] nvarchar NULL,
[Trade_type] nvarchar NULL,
[Account] nvarchar NULL,
[SettleCurrency] nvarchar NULL,
[MarketValueBaseCurrency] [decimal](28, 9) NULL,
[% split] [decimal](28, 9) NULL,
[CollateraByCurrency] [decimal](28, 9) NULL

) ON [PRIMARY]
GO

INSERT INTO [dbo].[Table A]
([ReportDate]
,[Broker]
,[Counterpartyborrower]
,[Trade_type]
,[Account]
,[SettleCurrency]
,[MarketValueBaseCurrency]
,[% split]

)
VALUES
(
'30/06/2020',
'Barclays Bank PLC',
'Govt Debt & Supras (Euroclear TriParty)',
'Loan',
'447217',
'EUR',
'1051611.45',
'0.033038',
'0.0'
),
(
'30/06/2020',
'Barclays Bank PLC',
'Govt Debt & Supras (Euroclear TriParty)',
'Loan',
'447220',
'EUR',
'24.61',
'0.002091',
'0.0'
),
(
'30/06/2020',
'Barclays Bank PLC',
'Govt Debt & Supras (Euroclear TriParty)',
'Collateral',
'',
'EUR',
'118049.12',
'0.0',
'28042211.43'
),
(
'30/06/2020',
'Barclays Bank PLC',
'Govt Debt & Supras (Euroclear TriParty)',
'Collateral',
'',
'AUD',
'2411.78',
'0.0',
'428254.1'
),

 (
 '30/06/2020',
  'Barclays Bank PLC',
    'Govt Debt & Supras (Euroclear TriParty)',
    'Collateral',
	'',
  'GBP',
  '117883.94',
   '0.0',
	'1576465.15'
), 
(
 '30/06/2020',
  'Barclays Bank PLC',
    'Govt Debt & Supras (Euroclear TriParty)',
    'Collateral',
	'',
  'AUD',
  '2329.22',
   '0.0',
	'2410116.69'
);

Blockquote

4

Did you test this script locally to see if it works first? How does one know those values are for that specific account and not the other account 447220. Or can it also apply to all other accounts.

Also not sure how you got the AUD as 895.4793231

select 428254.100000000 * 0.033038000
5

this script works

i just copied the sum from the first one down. it must not have done the right but i still need the %split * Counterpartyborrower

6 
declare @benjamins table(
[ReportDate] [datetime] NULL,
[Broker] nvarchar(50) NULL,
[Counterpartyborrower] nvarchar(50) NULL,
[Trade_type] nvarchar(50) NULL,
[Account] nvarchar(50) NULL,
[SettleCurrency] nvarchar(50) NULL,
[MarketValueBaseCurrency] [decimal](28, 9) NULL,
[% split] [decimal](28, 9) NULL,
[CollateraByCurrency] [decimal](28, 9) NULL

) 

INSERT INTO @benjamins
([ReportDate]
,[Broker]
,[Counterpartyborrower]
,[Trade_type]
,[Account]
,[SettleCurrency]
,[MarketValueBaseCurrency]
,[% split]
,CollateraByCurrency
)
VALUES
(
'2020-06-30',
'Barclays Bank PLC',
'Govt Debt & Supras (Euroclear TriParty)',
'Loan',
'447217',
'EUR',
'1051611.45',
'0.033038',
'0.0'
),
(
'2020-06-30',
'Barclays Bank PLC',
'Govt Debt & Supras (Euroclear TriParty)',
'Loan',
'447220',
'EUR',
'24.61',
'0.002091',
'0.0'
),
(
'2020-06-30',
'Barclays Bank PLC',
'Govt Debt & Supras (Euroclear TriParty)',
'Collateral',
'',
'EUR',
'118049.12',
'0.0',
'28042211.43'
),
(
'2020-06-30',
'Barclays Bank PLC',
'Govt Debt & Supras (Euroclear TriParty)',
'Collateral',
'',
'AUD',
'2411.78',
'0.0',
'428254.1'
),

 (
 '2020-06-30',
  'Barclays Bank PLC',
    'Govt Debt & Supras (Euroclear TriParty)',
    'Collateral',
	'',
  'GBP',
  '117883.94',
   '0.0',
	'1576465.15'
), 
(
 '2020-06-30',
  'Barclays Bank PLC',
    'Govt Debt & Supras (Euroclear TriParty)',
    'Collateral',
	'',
  'AUD',
  '2329.22',
   '0.0',
	'2410116.69'
);

select * from @benjamins
--select 428254.100000000 * 0.033038000

select CONCAT(a.account,b.SettleCurrency) ColumnA, 
       a.[% split] *  b.CollateraByCurrency, a.[% split], b.CollateraByCurrency
from @benjamins a
 join (select * 
         from @benjamins 
		where account = '' 
		  or account is null 
		 and Trade_type = 'Collateral'
	  ) b
 on a.ReportDate = b.ReportDate
where a.Account = '447217'

the AUD calculations on your side dont add up unless I am missing something

7

with no real links to join by

select distinct CONCAT(a.account,b.SettleCurrency) ColumnA, 
       a.[% split] *  b.CollateraByCurrency ColumnB, a.[% split], b.CollateraByCurrency
from @benjamins a
 cross apply (select * 
         from @benjamins 
		where account = '' 
		  or account is null 
		 and Trade_type = 'Collateral'
	  ) b
 --on a.ReportDate = b.ReportDate
where a.Account = '447217'
8

is there anyway to do the above with naming the account number. I probably going forward wont know exactly what account number is in file and there could be 20 different account numbers

9

populate the sample script with more account numbers and see what happens. did you try it? and look at the where clause.

10

i no i can populate the script with more account numbers. but i wont know the account number before hand in general. so i need a way of not having to manually add them like this where a.Account = '447217' in a script. im wondering if that is possible

11

Its just a sample script you can add anything you want.

12

ok please show me how to do it without adding the account numbers manually as i have no clue how to.

13

The sample script you posted just add some more rows with different accounts. Then remove

where a.Account = '447217' in the final query

Then apply same logic to dbo].[Table

select distinct CONCAT(a.account,b.SettleCurrency) ColumnA, 
       a.[% split] *  b.CollateraByCurrency ColumnB, a.[% split], b.CollateraByCurrency
from dbo.tableA a
 cross apply (select * 
         from dbo.tableA
		where account = '' 
		  or account is null 
		 and Trade_type = 'Collateral'
	  ) b
14

thanks. you were right was easy to fix just couldn't see it. and if i wanted to insert that data into a new table for certain columns would i be better to update the data so i can do the set column a(from the select) = fundcode (in the new table)

15

no idea what you mean. the final script I posted should take care of what you need I think

16

i need to put that data from column a and column b in the your select

into columns in a new table of new column in the same table either one is good. need them stored in columns in a table.

17

anyone any ideas on how i would go about getting the information from the select into a new table