I am a fresh grad and i have been assigned with the following task which i feel is really tough for my level
table a
item 01 02 03 04 05 06 rsp
ListId
Unit price
table b
listid 01 02 03 04 05 06 RSP
factor 0.2 0.5 2 1 3 0.2 0
baselistid RSP RSP 01 02 03 03 NULL
When i generate RSP item with unit price of $ 2 it will then generate 2 item which is 01($0.4) and 02($1),
then it is suppose to auto generate 03(01, $0.8) and 04(02, $1) which in the ends generate 05(03,$2.4 ) and 06(03, $0.16)
result for table a when create item with list id rsp
item a a a a a a
ListId 01 02 03 04 05 06
Unit price 0.4 1 0.8 1 2.4 0.16
-
@Item and @PriceListId are sent in as parameter, In this case @Item = 'a' @priceListId = 'rsp'
i am able to generate only the first level using
INSERT INTO [tablea] (item, listid, unitprice)
SELECT @Item, [P].[ListId], @CurrencyUnitPrice *( ISNULL([P].[Factor],0))
FROM [tableb] [P] LEFT OUTER JOIN [tableb] [PL] ON [PL].[ListId] = [P].[BaseListId]
where [P].[BaseListId] = @PriceListId AND [P].[BaseListId] IS NOT NULL AND [P].[Active] = 1
**assuming the faact that table b will not create loop data
I really hope if someone would be able to help me
Solution Found
CREATE TABLE #testing(testingid int, NewCurrencyUnitPrice decimal(19, 5))
INSERT INTO #testing(testingid, NewCurrencyUnitPrice)
VALUES(@ListId, @UnitPrice)
WHILE ( EXISTS (SELECT * FROM #testing)
BEGIN
UPDATE TABLEA .....
INSERT INTO #testing ([testingid],[NewCurrencyUnitPrice])
SELECT ListId, UnitPrice * Factor FROM TABLEA LEFTOUTER JOIN TABLEB
END