"For the examples above the next numbers generated should be CNCB17000002,CNPO17000002,CNCP17000004"

???..."next numbers generated?"....from  where/what/which process?

please confirm what version of SQL you are using.....in some of your other posts IIRC you said you were using 2008??
This is a 2016 forum

just a thought......

CREATE TABLE #ProductPrice(
 ProdId  VARCHAR(6) NOT NULL
,BasePrice INTEGER NOT NULL
,Pricegroup VARCHAR(3) NOT NULL
);
INSERT...

from your example data it appears that you are applying the same discount to all sizes.
buy get 5% off, buy 3 get 10% off, buy 4 get 15% off.

what consideration have you given to when to wish alter a price?
are you intending to keep a history of price changes per item?
If you have a start/end datetime...

not exactly sure of what you are asking.....but to start the discussion why cant you use COUNT & GROUP BY

eg

SELECT   COUNT(*) , OptionID, Quantity