merge data from two sets into one

code green

I have a table price_breaks with different prices identified by price_list ie:

Expand|Select|Wrap|Line Numbers

 
part    price_list      price

401        WSPL                1.6

401        LEVEL-70         1.3

404        WSPL                3.0

404        LEVEL-70         3.3

502        WSPL                2.0

600        LEVEL-70      4.0

I need to INSERT new records INTO this table,
which is a combination of the two price-lists as a new price_list WSPL70.
The criteria being
1. If a price for WSPL and LEVEL-70 exists, use the WSPL price
2. If only one price exists use that

I can JOIN to another table called products with the same parts.
The WSPL70 prices must be updated weekly from the same table
with any new products inserted
So I thought along the lines of

Expand|Select|Wrap|Line Numbers

 DELETE FROM price_breaks WHERE price_list = 'WSPL70';
 
INSERT INTO price_breaks

(part,price_list,price)

VALUES

(SELECT COALESCE (pl.part,alt.part) part

'WSPL70' price_list,

COALESCE (pl.price,alt.price) price,

FROM products

JOIN price_breaks AS pl 

ON (part.part = pl.part 

AND pl.price_list = 'WSPL')

LEFT JOIN price_breaks AS alt 

ON (part.part = alt.part 

AND alt.price_list = 'LEVEL-70'

AND pl.price_list IS NULL))

I have only tested the SELECT sub-query so far which doesn't return any 'LEVEL-70' prices
because the first JOIN filters them out.
If I change this to a LEFT JOIN
every part in products is returned with NULL values
whereas a RIGHT JOIN returns all the other price_lists (not shown in eg)

Can anybody help with this
Also, can I INSERT INTO a table with data from a sub-query from the same table as shown

deepuv04

Hi,
try using the following query

Expand|Select|Wrap|Line Numbers

 
INSERT INTO price_breaks

(part,price_list,price)

SELECT  distinct t.part,'WSPL70',

        case when wspl_Price is not null and level70_price is not null then wspl_price

             when wspl_Price is not null and level70_price is  null then wspl_price

             else level70_price end as price                                    

FROM price_breaks as t left outer join

        (select part,PRICE as wspl_Price from price_breaks as t1 where price_list = 'wspl'

        ) AS t1 on t1.part = t.part left outer join

        (select part,price as level70_price from price_breaks as t1 where price_list = 'Level-70' 

        ) as  t2 on t2.part = t.part

Thanks

code green

Different approach.
It was still producing null prices, probably from the other price_lists in the table.
(There are about 20 altogether) so I amended the query a little

Expand|Select|Wrap|Line Numbers

 SELECT DISTINCT t.part,'WSPL70',

wspl_price, level70_price,

CASE WHEN wspl_price IS NOT NULL 

    THEN wspl_price

WHEN wspl_Price IS NULL 

    AND level70_price IS NOT NULL 

    THEN level70_price END AS price                                    

FROM price_breaks AS t 

LEFT OUTER JOIN

 (SELECT part,price AS wspl_price 

  FROM price_breaks AS t1 

    WHERE price_list = 'PCD-WSPL') AS t1 

ON t1.part = t.part 

LEFT OUTER JOIN

  (SELECT part,price AS level70_price 

  FROM price_breaks AS t2 

      WHERE price_list = 'LEVEL-70') AS t2 

ON t2.part = t.part

WHERE price_list = 'LEVEL-70' OR price_list = 'PCD-WSPL'

ORDER BY t.part

Basically I moved a CASE condition to a WHERE condition at the end.
The result looks promising.
Using DISTINCT is a cop out in my book because it is not as efficient as filtering the records beforehand.
But I can see why it is needed in this case.

Thanks very much deepuv04.

merge data from two sets into one

re: merge data from two sets into one

re: merge data from two sets into one

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about