Need help to perform Weighted avg calculation to update back to table

Question

Post reply

Need help to perform Weighted avg calculation to update back to table

JoNTSQLSrv

Default port

Points: 1420
More actions
June 1, 2017 at 11:24 pm

#337444

Hi all,
Actually i am working in banking domain. Our company has decided to change the data provider, so all the old data should be migrated to new one i.e., old provider code and unique business key should be replaced with new provider code and business key
so here the twist is : if a client has 3 different plan(means 3 different unique business key with old provider code ) , these 3 different plan will be converted to 1 unique business key and new provider code as below
TableA
unique business key provider code Amount
1 1 10000
12 1 5000
123 1 2000
to
TableA
unique business key provider code Amount
529764 2 17000
529764- is new unique business key and 2 - is new provider code and total amount -17000
so the above information is maintained in a 1 main transaction TableA and another child table is there which holds fund amount TableB
1 TableA
2. TableB
TableB
unique business key provider code fundID Allocation_Percentage Allocated_amount
1 1 1 50.00 5000
1 1 2 50.00 5000
12 1 1 50.00 2500
12 1 3 50.00 2500
123 1 1 50.00 1000
123 1 4 50.00 1000
After migration It is very easy to just delete the TableA and insert with new single row, but now the problem is with TableB
So after migration TableB should look like below
TableB
unique business key provider code fundID Allocation_Percentage Allocated_amount
529764 2 1 50.00 8500
529764 2 2 29.4 5000
529764 2 3 14.7 2500
529764 2 4 5.9 1000
Please help me to do the above thing. I have to do it in Mysql but i want to know how to do it in mssql so that i will convert it to My sql , can you all help me to do this through some nice sql program procedure logic .. it would be great. if i get the correct logic

Please find below create table script and insert script sample data

CREATE TABLE AMOUNT_INVESTED (
PROVIDER_CODE varchar(10) NOT NULL,
BSKEY bigint NOT NULL,
TOTAL_VALUE decimal(18,5) NOT NULL,
CURR_CODE char(3) DEFAULT NULL,
CREATED_TSTAMP datetime NOT NULL,
CREATED_BY varchar(10) NOT NULL,
MODIFIED_TSTAMP datetime ,
MODIFIED_BY varchar(10) NULL
)

CREATE TABLE AMOUNT_INVESTED_FUND (
PROVIDER_CODE varchar(10) NOT NULL,
BSKEY bigint NOT NULL,
FUND_ID int NOT NULL,
ALLOCATION_PCT decimal(5,2) NOT NULL,
FUND_VALUE decimal(18,5) NOT NULL,
CREATED_TSTAMP datetime NOT NULL ,
CREATED_BY varchar(10) NOT NULL
)
INSERT INTO AMOUNT_INVESTED
(PROVIDER_CODE, BSKEY, TOTAL_VALUE, CREATED_TSTAMP, CREATED_BY, MODIFIED_TSTAMP, MODIFIED_BY)
VALUES ('49', '1471088', '15990.49', getdate(), '9956', null, 'service');
INSERT INTO AMOUNT_INVESTED
(PROVIDER_CODE, BSKEY, TOTAL_VALUE, CREATED_TSTAMP, CREATED_BY, MODIFIED_TSTAMP, MODIFIED_BY)
VALUES ('49', '1483126', '46479.6', getdate(), '9956',null , 'service');
INSERT INTO AMOUNT_INVESTED
(PROVIDER_CODE, BSKEY, TOTAL_VALUE, CREATED_TSTAMP, CREATED_BY, MODIFIED_TSTAMP, MODIFIED_BY)
VALUES ('49', '1593090', '14514.4', getdate(), '9956', null, 'service');
INSERT INTO AMOUNT_INVESTED
(PROVIDER_CODE, BSKEY, TOTAL_VALUE, CREATED_TSTAMP, CREATED_BY, MODIFIED_TSTAMP, MODIFIED_BY)
VALUES ('49', '5406608', '12148.49', getdate(), '9956', null, 'service');
INSERT INTO AMOUNT_INVESTED
(PROVIDER_CODE, BSKEY, TOTAL_VALUE, CREATED_TSTAMP, CREATED_BY, MODIFIED_TSTAMP, MODIFIED_BY)
VALUES ('49', '5451935', '19506.89', getdate(), '9956', null, 'service');
INSERT INTO AMOUNT_INVESTED
(PROVIDER_CODE, BSKEY, TOTAL_VALUE, CREATED_TSTAMP, CREATED_BY, MODIFIED_TSTAMP, MODIFIED_BY)
VALUES ('49', '9132930', '7927.29', getdate(), '9956', null, 'service');
INSERT INTO AMOUNT_INVESTED_FUND
(PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1471088', '1', '10.00', '1599.05', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND
(PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1471088', '6', '5.00', '799.52', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND
(PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1471088', '9', '5.00', '799.52', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND
(PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1471088', '13', '10.00', '1599.05', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND
(PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1471088', '18', '10.00', '1599.05', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND
(PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1471088', '19', '10.00', '1599.05', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND
(PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1471088', '20', '10.00', '1599.05', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND
(PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1471088', '22', '10.00', '1599.05', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND
(PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1471088', '24', '10.00', '1599.05', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND
(PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1471088', '27', '10.00', '1599.05', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND
(PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1471088', '34', '10.00', '1599.05', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND
(PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1483126', '1', '10.00', '4647.96', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND
(PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1483126', '6', '5.00', '2323.98', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND
(PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1483126', '9', '5.00', '2323.98', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND
(PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1483126', '13', '10.00', '4647.96', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1483126', '18', '10.00', '4647.96', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1483126', '19', '10.00', '4647.96', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1483126', '20', '10.00', '4647.96', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1483126', '22', '10.00', '4647.96', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1483126', '24', '10.00', '4647.96', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1483126', '27', '10.00', '4647.96', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1483126', '34', '10.00', '4647.96', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1593090', '1', '10.00', '1451.44', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1593090', '6', '5.00', '725.72', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1593090', '9', '5.00', '725.72', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1593090', '13', '10.00', '1451.44', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1593090', '18', '10.00', '1451.44', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1593090', '19', '10.00', '1451.44', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1593090', '20', '10.00', '1451.44', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1593090', '22', '10.00', '1451.44', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1593090', '24', '10.00', '1451.44', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1593090', '27', '10.00', '1451.44', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '1593090', '34', '10.00', '1451.44', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5406608', '1', '10.00', '1214.85', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5406608', '6', '5.00', '607.42', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5406608', '9', '5.00', '607.42', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5406608', '13', '10.00', '1214.85', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5406608', '18', '10.00', '1214.85', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5406608', '19', '10.00', '1214.85', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5406608', '20', '10.00', '1214.85', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5406608', '22', '10.00', '1214.85', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5406608', '24', '10.00', '1214.85', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5406608', '27', '10.00', '1214.85', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5406608', '34', '10.00', '1214.85', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5451935', '1', '10.00', '1950.69', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5451935', '6', '5.00', '975.34', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5451935', '9', '5.00', '975.34', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5451935', '13', '10.00', '1950.69', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5451935', '18', '10.00', '1950.69', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5451935', '19', '10.00', '1950.69', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5451935', '20', '10.00', '1950.69', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5451935', '22', '10.00', '1950.69',getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5451935', '24', '10.00', '1950.69', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5451935', '27', '10.00', '1950.69', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '5451935', '34', '10.00', '1950.69', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '9132930', '1', '10.00', '792.73', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '9132930', '6', '5.00', '396.36', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '9132930', '9', '5.00', '396.36', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '9132930', '13', '10.00', '792.73', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '9132930', '18', '10.00', '792.73', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '9132930', '19', '10.00', '792.73', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '9132930', '20', '10.00', '792.73', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '9132930', '22', '10.00', '792.73', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '9132930', '24', '10.00', '792.73', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '9132930', '27', '10.00', '792.73', getdate(), '9956');
INSERT INTO AMOUNT_INVESTED_FUND (PROVIDER_CODE, BSKEY, FUND_ID, ALLOCATION_PCT, FUND_VALUE, CREATED_TSTAMP, CREATED_BY)
VALUES ('49', '9132930', '34', '10.00', '792.73', getdate(), '9956');

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 1

Hi,

Lots of sample data (great!) but no expected results so I've had to guess.

WITH Src AS ( SELECT BSKEY ,PROVIDER_CODE ,TOTAL_VALUE,TOTAL_VALUE / SUM(TOTAL_VALUE) OVER(PARTITION BY PROVIDER_CODE) AS RATIO FROM AMOUNT_INVESTED ) SELECT b.PROVIDER_CODE,b.FUND_ID,SUM(b.ALLOCATION_PCT * s.RATIO) AS ALLOCATION_PCT,SUM(FUND_VALUE) AS FUND_VALUE FROM AMOUNT_INVESTED_FUND b INNER JOIN Src s ON s.BSKEY = b.BSKEY AND s.PROVIDER_CODE = b.PROVIDER_CODE GROUP BY b.PROVIDER_CODE,b.FUND_ID ORDER BY b.PROVIDER_CODE,b.FUND_ID;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

JoNTSQLSrv Default port Points: 1420 More actions · Answer 2

Mark Cowne - Friday, June 2, 2017 2:23 AM
Hi,
Lots of sample data (great!) but no expected results so I've had to guess.
WITH Src AS ( SELECT BSKEY ,PROVIDER_CODE ,TOTAL_VALUE,TOTAL_VALUE / SUM(TOTAL_VALUE) OVER(PARTITION BY PROVIDER_CODE) AS RATIO FROM AMOUNT_INVESTED ) SELECT b.PROVIDER_CODE,b.FUND_ID,SUM(b.ALLOCATION_PCT * s.RATIO) AS ALLOCATION_PCT,SUM(FUND_VALUE) AS FUND_VALUE FROM AMOUNT_INVESTED_FUND b INNER JOIN Src s ON s.BSKEY = b.BSKEY AND s.PROVIDER_CODE = b.PROVIDER_CODE GROUP BY b.PROVIDER_CODE,b.FUND_ID ORDER BY b.PROVIDER_CODE,b.FUND_ID;

Thank you very much @SSCarpal Tunnel, result seems to be correct

but how to round the percentage column i mean this ALLOCATION_PCT column?

This ALLOCATION_PCT column should contain only 4 digit like (10.00 , 5.00 ext) and sum of this should be 100 for each Unique BSKEY .

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 3

Just use an explicit cast, so change

SUM(b.ALLOCATION_PCT * s.RATIO) AS ALLOCATION_PCT

to

CAST(SUM(b.ALLOCATION_PCT * s.RATIO) AS DECIMAL(5,2)) AS ALLOCATION_PCT

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

JoNTSQLSrv Default port Points: 1420 More actions · Answer 4

Mark Cowne - Monday, June 5, 2017 3:12 AM
Just use an explicit cast, so change
SUM(b.ALLOCATION_PCT * s.RATIO) AS ALLOCATION_PCT
to
CAST(SUM(b.ALLOCATION_PCT * s.RATIO) AS DECIMAL(5,2)) AS ALLOCATION_PCT

Thank you very much @mark-3 Cowne. That works perfectly. Great Job. An awesome SQL expert u are.

unique business key	provider code	Amount
1	1	10000
12	1	5000
123	1	2000

unique business key	provider code	fundID	Allocation_Percentage	Allocated_amount
1	1	1	50.00	5000
1	1	2	50.00	5000
12	1	1	50.00	2500
12	1	3	50.00	2500
123	1	1	50.00	1000
123	1	4	50.00	1000