Eddie Shipman
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.
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'
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
and this data in u.unet:"id", "pt" , "price"
"M34", "107", 100.00
"M34", "107", 2315.00
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER