We help IT Professionals succeed at work.

Help modifying query to honor min and max markups

117 Views
Last Modified: 2018-10-09
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

Engineering Manager
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Eddie ShipmanAll-around developer
CERTIFIED EXPERT

Author

Commented:
With some modification this worked just great, thanks.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.