Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sum using two linked fields

Author  Topic 

satheesh
Posting Yak Master

152 Posts

Posted - 2013-08-22 : 10:52:36
Hi All,


I need to update the field 'TotalPrice' by adding values using 'linked_pol_num' field

i.e
'Pol_num' field and 'linked_pol_num' are linked with each other and it has a corresponding 'price'. I need to update the 'TotalPrice' by adding these two price together if link found!

for eg: in below table

First 2 values are linked together so the sum be : 96.41

3rd,4th and 5th are linked together so the sum be : 46.97

6th is alone so the sum be 12.83

like these i need to update all the values in 'totalprice' field.

Any help will be highly appreciated.

Thanks




Pol_Num Linked_Pol_Num Price TotalPrice
LGU11209401 JUB11209402 75.00
JUB11209402 LGU11209401 21.41
SOL11209421 JUB11209422 32.09
JUB11209422 SOL11209421,SOL11209442 5.74
SOL11209442 JUB11209422 9.14
SOL11209120 JUB11209119 12.83


Expected Result

Pol_Num Linked_Pol_Num Price TotalPrice
LGU11209401 JUB11209402 75.00 96.41
JUB11209402 LGU11209401 21.41 96.41
SOL11209421 JUB11209422 32.09 46.97
JUB11209422 SOL11209421,SOL11209442 5.74 46.97
SOL11209442 JUB11209422 9.14 46.97
SOL11209120 JUB11209119 12.83 12.83

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-22 : 11:27:18
What is the data type of 'linked_pol_num', how is the data formatted?
How many Pol_num can be stored in 'linked_pol_num' ?
Does Pol_Num has fixed width ?
Go to Top of Page

satheesh
Posting Yak Master

152 Posts

Posted - 2013-08-22 : 12:06:30
Hi Mumu,

Thanks for your reply.

What is the data type of 'linked_pol_num', how is the data formatted?
[linked_pol_num] [varchar](250) NULL,
If more than one linked_pol_num, then it be seperated by comma ','

How many Pol_num can be stored in 'linked_pol_num' ?
Max 3 to 4 'linked_pol_num' are found in existing data ,However in most case just one linked_pol_num.

Does Pol_Num has fixed width ?
No

Thanks
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-22 : 14:32:20
here is a way to get what you need (with two linked_pol_num)
you can extend it to more.

[CODE]

-- TEST DATA

DECLARE @Temp TABLE(Pol_Num VARCHAR(20), Linked_Pol_Num VARCHAR(100), Price NUMERIC(18,2), TotalPrice NUMERIC(18,2));

INSERT INTO @Temp VALUES
('LGU11209401', 'JUB11209402', 75.00, 0),
('JUB11209402', 'LGU11209401', 21.41, 0),
('SOL11209421', 'JUB11209422', 32.09, 0),
('JUB11209422', 'SOL11209421, SOL11209442', 5.74, 0),
('SOL11209442', 'JUB11209422', 9.14, 0),
('SOL11209120', 'JUB11209119', 12.83, 0);



-- With Variable length Pol_Num
;WITH CTE AS
(SELECT Pol_Num, Linked_Pol_Num, (CASE WHEN CHARINDEX(',', Linked_Pol_Num) > 0 THEN SUBSTRING(Linked_Pol_Num, 0, CHARINDEX(',', Linked_Pol_Num)) ELSE Linked_Pol_Num END) as first_link,
CHARINDEX(',', SUBSTRING(Linked_Pol_Num, CHARINDEX(',', Linked_Pol_Num)+2, LEN(Linked_Pol_Num) - CHARINDEX(',', Linked_Pol_Num))) as secondchar,
(CASE WHEN CHARINDEX(',', Linked_Pol_Num) > 0 THEN SUBSTRING(Linked_Pol_Num, CHARINDEX(',', Linked_Pol_Num)+1,
Len(linked_Pol_NUM) - CHARINDEX(',', Linked_Pol_Num))
ELSE NULL END) as SECOND_LINK,
Price
FROM @Temp),
CTE1 AS
(SELECT first_link, SUM(Price) as SubTotalPrice from CTE group by first_link),
CTE2 AS
(SELECT T1.Pol_Num, T1.Linked_Pol_Num, T1.first_link, SECOND_link, price, SubTotalPrice, (SELECT MAX(second_link) from CTE T3 where T1.Pol_Num = T3.first_link) as Forwardlink,
(SELECT MAX(first_link) from CTE T3 where T1.Pol_Num = T3.SECOND_link) as BackwardLink,
(price + COALESCE(SubTotalPrice, 0)) as TotalPrice from CTE T1 LEFT JOIN CTE1 T2 ON T1.Pol_Num = T2.first_link)
SELECT T1.Pol_Num, T1.Linked_Pol_Num, price, (TotalPrice + COALESCE((SELECT price from @Temp where Pol_Num = Forwardlink), 0) +
COALESCE((SELECT TotalPrice from CTE2 TT where TT.Pol_Num = T1.BackwardLink), 0)) as Total from CTE2 T1;


[/CODE]
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-22 : 17:42:29
Here is a different solution (not efficient):
[CODE]



DECLARE @Temp TABLE(Pol_Num VARCHAR(20), Linked_Pol_Num VARCHAR(100), Price NUMERIC(18,2), TotalPrice NUMERIC(18,2));

INSERT INTO @Temp VALUES
('LGU11209401', 'JUB11209402', 75.00, 0),
('JUB11209402', 'LGU11209401', 21.41, 0),
('SOL11209421', 'JUB11209422', 32.09, 0),
('JUB11209422', 'SOL11209421,SOL11209442', 5.74, 0),
('SOL11209442', 'JUB11209422', 9.14, 0),
('SOL11209120', 'JUB11209119', 12.83, 0);




;WITH CTE AS
(SELECT Pol_Num, Linked_Pol_Num, (CASE WHEN LEN(Linked_Pol_Num) > 11 THEN SUBSTRING(Linked_Pol_Num, 0, 12) ELSE Linked_Pol_Num END) as first_link,
(CASE WHEN LEN(Linked_Pol_Num) > 11 THEN SUBSTRING(Linked_Pol_Num, 13, 22) ELSE NULL END) as SECOND_link, Price
FROM @Temp),
CTE2 as
(SELECT Pol_Num, first_link, Price from CTE
UNION
SELECT first_link, Pol_Num, Price from CTE where first_link in (Select pol_num from @Temp)
UNION
SELECT SECOND_link, first_link, (Select price from @Temp Ta where Ta.Pol_num = T.first_link) from CTE T where SECOND_link IS NOT NULL
UNION
SELECT first_link, SECOND_link, (Select price from @Temp Ta where Ta.Pol_num = T.SECOND_link) from CTE T where SECOND_link IS NOT NULL
UNION
SELECT SECOND_link, first_link, (Select price from CTE Ta where Ta.first_link = T.first_link) from CTE T where SECOND_link IS NOT NULL)
SELECT Pol_Num, SUM(price) Total from CTE2 GROUP BY Pol_Num;


[/CODE]
Go to Top of Page
   

- Advertisement -