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' fieldi.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.413rd,4th and 5th are linked together so the sum be : 46.976th is alone so the sum be 12.83like these i need to update all the values in 'totalprice' field.Any help will be highly appreciated.ThanksPol_Num Linked_Pol_Num Price TotalPrice LGU11209401 JUB11209402 75.00JUB11209402 LGU11209401 21.41SOL11209421 JUB11209422 32.09JUB11209422 SOL11209421,SOL11209442 5.74SOL11209442 JUB11209422 9.14SOL11209120 JUB11209119 12.83Expected ResultPol_Num Linked_Pol_Num Price TotalPrice LGU11209401 JUB11209402 75.00 96.41JUB11209402 LGU11209401 21.41 96.41SOL11209421 JUB11209422 32.09 46.97JUB11209422 SOL11209421,SOL11209442 5.74 46.97SOL11209442 JUB11209422 9.14 46.97SOL11209120 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 ? |
|
|
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 ?NoThanks |
|
|
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 DATADECLARE @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] |
|
|
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 CTEUNIONSELECT first_link, Pol_Num, Price from CTE where first_link in (Select pol_num from @Temp)UNIONSELECT 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 UNIONSELECT 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 UNIONSELECT 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] |
|
|
|
|
|