Having problem with insert

1

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

diagram.PNG860×459 9.35 KB

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

2

If you could post DDL for your tables and insert statements to populate the tales you're selecting from perhaps you will get some useful replies.

3

Hi Jotorre , I kinda solve it by using temporary table where each generated item i put the new currency and the listid inside then reloop till the temporary table is empty but i am not sure if my boss would accept the answer. awaiting his arrival to ask him.

Never the less thank you for taking your time to look at it :grinning:

4

If you have a possible solution, perhaps you could post it for the benefit of others.

5

sure.i edited a summary of the solution up in the post