SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sum using two linked fields
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

satheesh
Posting Yak Master

United Kingdom
148 Posts

Posted - 08/22/2013 :  10:52:36  Show Profile  Reply with Quote
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

547 Posts

Posted - 08/22/2013 :  11:27:18  Show Profile  Reply with Quote
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

United Kingdom
148 Posts

Posted - 08/22/2013 :  12:06:30  Show Profile  Reply with Quote
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

Edited by - satheesh on 08/22/2013 12:09:00
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

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



-- 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;


Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 08/22/2013 :  17:42:29  Show Profile  Reply with Quote
Here is a different solution (not efficient):




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;


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000