Help modifying query to honor min and max markups

Eddie Shipman
Eddie Shipman used Ask the Experts™
on
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.

SELECT
    -- when the markup is a 'P' (percentage) multiply     
	CASE WHEN (SELECT c0.mt 
               FROM p.markups c0 
               WHERE c0.id = 'M07' AND 
                     c0.pid = 'M34' AND 
                     c0.pt = '107') = 'P' THEN
		TO_CHAR(u.price + (u.price * (SELECT c1.mk 
		                                FROM p.markups c1 
		                                WHERE c1.id = 'M07' AND 
		                                      c1.pid = 'M34' AND 
		                                      c1.pt = '107')) , 'FM999999999.00')
	ELSE
	-- when the markup is a 'F" (flat), so add it
		TO_CHAR(u.price + (SELECT c2.mk 
		                    FROM p.markups c2 
		                    WHERE c2.id = 'M07' AND 
		                          c2.pid = 'M34' AND 
		                          c2.pt = '107') , 'FM999999999.00')
		                          
	END as mkprice	
FROM u.unet u
WHERE u.id = 'M34' AND 
      u.pt = '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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Engineer
Commented:
SELECT
   CASE WHEN (p.mt = 'P') THEN  u.price + LEAST(GREATEST((u.price * p.mk), p.minmk), p.maxmk)
        ELSE /* (p.mt = 'F') */ u.price + LEAST(GREATEST(p.mk, p.minmk), p.maxmk)
        END AS mkprice
FROM
   u.unet u, p.markups p
WHERE
   u.id = p.pid AND
   u.pt = p.pt
ORDER BY u.id, p.id

Open in new window

Eddie ShipmanAll-around developer

Author

Commented:
With some modification this worked just great, thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial