Link to home
Start Free TrialLog in
Avatar of Eddie Shipman
Eddie ShipmanFlag for United States of America

asked on

Help modifying query to honor min and max markups

I need modify this query to honor the minmk and maxmk, if mt = 'P' and either minmk and maxmk exist.
Not sure how to do that...

Currently, the query returns 125.00 for the first record and 2893.75. It needs to return 140.00 and 2815.00

Note: There are only 'P' (percentage) and 'F' (flat) markup types.

    -- when the markup is a 'P' (percentage) multiply     
               FROM p.markups c0 
               WHERE = 'M07' AND 
            = 'M34' AND 
            = '107') = 'P' THEN
		TO_CHAR(u.price + (u.price * (SELECT 
		                                FROM p.markups c1 
		                                WHERE = 'M07' AND 
		                             = 'M34' AND 
		                             = '107')) , 'FM999999999.00')
	-- when the markup is a 'F" (flat), so add it
		TO_CHAR(u.price + (SELECT 
		                    FROM p.markups c2 
		                    WHERE = 'M07' AND 
		                 = 'M34' AND 
		                 = '107') , 'FM999999999.00')
	END as mkprice	
FROM u.unet u
WHERE = 'M34' AND = '107'

Open in new window

If I have this data in `p.markups`:
"id" , "pid", "pt" , "mt", "mk", "minmk", "maxmk", "exc"
"M07", "M34", "107", "P" , 0.25, 40     , 500    , 0

Open in new window

and this data in u.unet:
"id",  "pt" , "price"
"M34", "107",  100.00
"M34", "107", 2315.00

Open in new window

Avatar of Jan Louwerens
Jan Louwerens
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Eddie Shipman


With some modification this worked just great, thanks.