>>>>>>>>>>>>>>SELECT Qty, COUNT(*) AS QtySold ; >>>>>>>>>>>>>> FROM YourTable ; >>>>>>>>>>>>>> GROUP BY Qty ; >>>>>>>>>>>>>> INTO CURSOR csrQtys >>>>>>>>>>>>>> >>>>>>>>>>>>>>SELECT Qty, MAX(QtySold) AS SoldMost ; >>>>>>>>>>>>>> FROM csrQtys ; >>>>>>>>>>>>>> INTO CURSOR csrSoldMost >>>>>>>>>>>>>> >>>>>>>>>>>>>>SELECT Qty, MPrices, COUNT(*) AS PriceCount ; >>>>>>>>>>>>>> FROM YourTable ; >>>>>>>>>>>>>> GROUP BY Qty, MPrices ; >>>>>>>>>>>>>> INTO CURSOR csrPriceCounts >>>>>>>>>>>>>> >>>>>>>>>>>>>>SELECT Qty, MPrices, MAX(PriceCount) AS PriceMost ; >>>>>>>>>>>>>> FROM csrPriceCounts ; >>>>>>>>>>>>>> GROUP BY Qty, MPrices ; >>>>>>>>>>>>>> INTO CURSOR csrPriceMost >>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>SELECT Qty, COUNT(*) AS QtySold ; >>>>>>>>>>>>> FROM myPrices ; >>>>>>>>>>>>> GROUP BY Qty ; >>>>>>>>>>>>> INTO CURSOR csrQtys >>>>>>>>>>>>> >>>>>>>>>>>>>brow >>>>>>>>>>>>> >>>>>>>>>>>>>SELECT Qty, MAX(QtySold) AS SoldMost ; >>>>>>>>>>>>> FROM csrQtys ; >>>>>>>>>>>>> GROUP BY Qty, QtySold ; >>>>>>>>>>>>> INTO CURSOR csrSoldMost >>>>>>>>>>>>> >>>>>>>>>>>>>brow >>>>>>>>>>>>> >>>>>>>>>>>>>SELECT Qty, myPrices, COUNT(*) AS PriceCount ; >>>>>>>>>>>>> FROM myPrices ; >>>>>>>>>>>>> GROUP BY Qty, myPrices ; >>>>>>>>>>>>> INTO CURSOR csrPriceCounts >>>>>>>>>>>>> >>>>>>>>>>>>>BROWSE >>>>>>>>>>>>> >>>>>>>>>>>>>SELECT Qty, myPrices, MAX(PriceCount) AS PriceMost ; >>>>>>>>>>>>> FROM csrPriceCounts ; >>>>>>>>>>>>> GROUP BY Qty, myPrices ; >>>>>>>>>>>>> INTO CURSOR csrPriceMost >>>>>>>>>>>>> >>>>>>>>>>>>>browse >>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>CREATE CURSOR QtyRange (iID I, nLow N(6,2), nHigh N(6,2) >>>>>>>>>>>>INSERT INTO QtyRange VALUES (1, .25, 1) >>>>>>>>>>>>INSERT INTO QtyRange VALUES (2, 1.25,2) >>>>>>>>>>>>INSERT INTO QtyRange VALUES (3, .25, .5) >>>>>>>>>>>>INSERT INTO QtyRange VALUES (4, .51, 1) >>>>>>>>>>>>* etc., to try different sets >>>>>>>>>>>> >>>>>>>>>>>>SELECT QtyRange.iID, COUNT(*) AS RangeCount; >>>>>>>>>>>> FROM YourTable ; >>>>>>>>>>>> JOIN QtyRange ; >>>>>>>>>>>> ON YourTable.Qty BETWEEN QtyRange.nLow and QtyRange.nHigh ; >>>>>>>>>>>> GROUP BY QtyRange.iID ; >>>>>>>>>>>> INTO CURSOR csrRangeCount >>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>CREATE CURSOR QtyRange (iID I, nLow N(6,2), nHigh N(6,2)) >>>>>>>>>>>mID = 1 >>>>>>>>>>>mMin = .1 >>>>>>>>>>>mMax = 1.0 >>>>>>>>>>>mDiff = mMax - mMin >>>>>>>>>>>mBins = 10 >>>>>>>>>>>mIncre = mDiff/mBins >>>>>>>>>>>mStep = mIncre >>>>>>>>>>>mLow = mMin >>>>>>>>>>>FOR r = .1 TO mBins mStep >>>>>>>>>>> INSERT INTO QtyRange VALUES (mId, mLow, mLow + mStep) >>>>>>>>>>> mID = mID + 1 >>>>>>>>>>> mLow = mLow + mStep >>>>>>>>>>>endfor >>>>>>>>>>> >>>>>>>>>>>SELECT qtyRange.iID, qtyRange.nLow, qtyRange.nHigh, COUNT(*) AS RangeCount; >>>>>>>>>>> FROM qtyRange ; >>>>>>>>>>> JOIN myPrices ; >>>>>>>>>>> ON myPrices.Qty BETWEEN QtyRange.nLow and QtyRange.nHigh ; >>>>>>>>>>> GROUP BY QtyRange.iID, qtyRange.nLow, qtyRange.nHigh ; >>>>>>>>>>> INTO CURSOR csrRangeCount >>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>SELECT Qty, MIN(MyPrice) AS MinPrice, MAX(MyPrice) AS MaxPrice; >>>>>>>>>> FROM MyPrices ; >>>>>>>>>> GROUP BY Qty >>>>>>>>>>>>>>>>>>>>
>>>>>>>>CREATE CURSOR PriceRange (iID I, nLowPrice N(6,2), nHighPrice N(6,2)) >>>>>>>>INSERT INTO PriceRange VALUES (...) >>>>>>>>... >>>>>>>> >>>>>>>> >>>>>>>>SELECT qtyRange.iID, qtyRange.nLow, qtyRange.nHigh, PriceRange.nLowPrice, PriceRange.nHighPrice, COUNT(*) AS PriceCount ; >>>>>>>> FROM myPrices ; >>>>>>>> JOIN qtyRange ; >>>>>>>> ON myPrices.Qty BETWEEN QtyRange.nLow and QtyRange.nHigh ; >>>>>>>> JOIN PriceRange ; >>>>>>>> ON myPrices.MyPrice BETWEEN PriceRange.nLowPrice AND PriceRange.nHighPrice ; >>>>>>>> GROUP BY 1, 2, 3, 4, 5 ; >>>>>>>> INTO CURSOR csrResults >>>>>>>>>>>>>>>>
>>>>> >>>>>CLOSE all >>>>>SET SAFETY off >>>>> >>>>>*** 1 *** >>>>>CREATE TABLE myPrices(qty n(10,6), myPrices n(12,2) ) >>>>>FOR r = 1 TO 5000 >>>>> INSERT INTO myPrices VALUES (RAND(), RAND()*1000000 ) >>>>>endfor >>>>>INDEX on 100000 - myPrices TO priceIndex >>>>>GO top >>>>>priceTop = myPrices >>>>>GO bott >>>>>priceBott = myPrices >>>>>GO top >>>>>BROWSE >>>>>wait >>>>> >>>>>*** 2 *** >>>>>CREATE table qtyRange (qtyID I, qtyLow N(6,2), qtyHigh N(6,2)) >>>>>mID = 1 >>>>>mMin = .1 >>>>>mMax = 2.0 >>>>>mDiff = mMax - mMin >>>>>mBins = 10 >>>>>mIncre = mDiff/mBins >>>>>mStep = mIncre >>>>>mLow = mMin >>>>>FOR r = mMin TO mBins mStep >>>>> mHigh = mLow + mStep >>>>> INSERT INTO qtyRange VALUES (mId, mLow, mHigh ) >>>>> mID = mID + 1 >>>>> mLow = mLow + mStep >>>>>endfor >>>>> >>>>>*** 3 *** *********** prices stuff >>>>>CREATE table prRange (prID I, prLow N(6,2), prHigh N(6,2)) >>>>>mID2 = 1 >>>>>mMin2 = priceBott >>>>>mMax2 = priceTop >>>>>mDiff2 = INT(mMax2 - mMin2) >>>>>mBins2 = 3 >>>>>mIncre2 = mDiff2/mBins2 >>>>>mIncre2 = INT(mincre2) >>>>>mStep2 = mIncre2 >>>>>mLow2 = mMin2 >>>>>mHigh2 = mLow2 + mStep2 >>>>>FOR r = 1 TO mBins2 mStep2 >>>>> INSERT INTO prRange VALUES (mId2, mLow2, mHigh2) >>>>> mID2 = mID2 + 1 >>>>> mLow2 = mLow2 + mStep2 >>>>> mHigh2 = mLow2 + mStep2 >>>>>endfor >>>>>*BROWSE >>>>>*wait >>>>> >>>>>*** 4 *** counting QTY's for each range >>>>>SELECT qtyRange.qtyID, qtyRange.qtyLow, qtyRange.qtyHigh, COUNT(*) AS qtyCount; >>>>> FROM qtyRange, myPrices; >>>>> where myPrices.Qty BETWEEN QtyRange.qtyLow and QtyRange.qtyHigh ; >>>>> GROUP BY qtyRange.qtyID, qtyRange.qtyLow, qtyRange.qtyHigh; >>>>> into table qtyRange2 >>>>>SELECT qtyRange2 >>>>>brow >>>>>wait >>>>> >>>>>*** 5 -- counts of prices within each range >>>>>CLOSE all >>>>>SELECT prRange.prID, prRange.prLow, prRange.prHigh, COUNT(*) AS prCount; >>>>> FROM prRange, myPrices; >>>>> where myPrices.myPrices BETWEEN prRange.prLow and prRange.prHigh ; >>>>> GROUP BY prRange.prID, prRange.prLow, prRange.prHigh; >>>>> into table prRange2 >>>>>SELECT prRange2 >>>>>BROWSE >>>>>wait >>>>> >>>>>*** 6 *** includes count of prices ranges for each QTY range >>>>>SELECT qtyRange.qtyID as qtyID, qtyRange.qtyLow, qtyRange.qtyHigh,; >>>>> prRange.prID as prID, prRange.prLow, prRange.prHigh, COUNT(prRange.prID) AS prCount ; >>>>> FROM myPrices ; >>>>> JOIN qtyRange ; >>>>> ON myPrices.Qty BETWEEN QtyRange.qtyLow and QtyRange.qtyHigh ; >>>>> JOIN prRange ; >>>>> ON myPrices.MyPrices BETWEEN prRange.prLow AND prRange.prHigh ; >>>>> GROUP BY 1, 2, 3, 4, 5, 6; >>>>> INTO table mAll >>>>>SELECT mAll >>>>>brow >>>>>wait >>>>> >>>>>*** 7 *** Add in qty counts to the above >>>>>SELECT ; >>>>>mAll.qtyID, mAll.qtyLow, mAll.qtyHigh, qtyRange2.qtyCount ,; >>>>>mAll.prID, mAll.prLow, mAll.prHigh, mAll.prCount ; >>>>>FROM mAll, qtyRange2 ; >>>>>WHERE mAll.qtyLow = qtyRange2.qtyLow AND mAll.qtyHigh = qtyRange2.qtyHigh; >>>>>INTO TABLE tall >>>>>brow >>>>>>>>>
>>>>*** 6a *** prices ranges for each yr and QTY range >>>>SELECT yrRange.prID as yrID, yrRange.yrLow, yrRange.yrHigh ,; >>>> qtyRange.qtyID as qtyID, qtyRange.qtyLow, qtyRange.qtyHigh ,; >>>> prRange.prID as prID, prRange.prLow, prRange.prHigh ,; >>>> COUNT(*) AS prCount ; >>>> FROM myPrices ; >>>> JOIN yrRange ; >>>> ON myPrices.yr BETWEEN yrRange.yrLow AND yrRange. yrHigh ; >>>> JOIN qtyRange ; >>>> ON myPrices.Qty BETWEEN QtyRange.qtyLow AND QtyRange.qtyHigh ; >>>> JOIN prRange ; >>>> ON myPrices.MyPrices BETWEEN prRangeprLow AND prRange.prHigh ; >>>> GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9; >>>> INTO table mAll1234 >>>>SELECT mAll1234 >>>>brow >>>>wait >>>>>>>>
>>>>*** 6aa1 *** prices ranges for each yr and QTY range >>>>SELECT prRange.prID as yrID, yrRange.yrLow, yrRange.yrHigh ,; >>>> qtyRange.qtyID as qtyID, qtyRange.qtyLow, qtyRange.qtyHigh ,; >>>> COUNT(*) AS qtyCount ; >>>> FROM myPrices ; >>>> JOIN yrRange ; >>>> ON myPrices.yr BETWEEN yrRange.yrLow and yrRange.yrHigh ; >>>> JOIN qtyRange ; >>>> ON myPrices.Qty BETWEEN QtyRange.qtyLow and QtyRange.qtyHigh ; >>>> GROUP BY 1, 2, 3, 4, 5, 6; >>>> INTO table mALL >>>>SELECT mALL >>>>*brow >>>>*wait >>>> >>>>*** 6aa2 *** prices ranges for each yr and QTY range >>>>SELECT mAll.yrID, mAll.yrLow, mAll.yrHigh,; >>>> mAll.qtyID, mAll.qtyLow, mAll.qtyHigh, mALL.qtyCount ,; >>>> prRange.prID, prRange.prLow, prRange.prHigh ,; >>>> COUNT(*) AS prCount ; >>>> FROM mALL,myPrices ; >>>> JOIN prRange; >>>> ON myPrices.myPrices BETWEEN prRange.prLow and prRange.prHigh ; >>>> GROUP BY 1, 2,3,4,5,6,7,8,9,10; >>>> INTO table mALL_2 >>>>SELECT mALL_2 >>>>brow >>>>wait >>>>>>>
>SELECT citRange.citID, citRange.city, ; > yrRange.yrID, yrRange.yrLow, yrRange.yrHigh ,; > qtyRange.qtyID, qtyRange.qtyLow, qtyRange.qtyHigh ,; > prRange.prID, prRange.prLow, prRange.prHigh ,; > COUNT(*) AS prCount ; > FROM myPrices ; > JOIN citRange ; > ON LOWER(ALLTRIM(citRange.city))$LOWER(ALLTRIM(myPrices.city)) ; > JOIN yrRange ; > ON myPrices.yr BETWEEN yrRange.yrLow and yrRange.yrHigh ; > JOIN qtyRange ; > ON myPrices.Qty BETWEEN QtyRange.qtyLow and QtyRange.qtyHigh ; > JOIN prRange; > ON myPrices.myPrices BETWEEN prRange.prLow and prRange.prHigh ; > GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11; > INTO table mAll >select mAll > >** add in missing counts >SELECT mAll.citID, mAll.city, citRange2.citCount ,; > mAll.yrID, mAll.yrLow, mAll. yrHigh, yrRange2.yrCount ,; > mAll.qtyID, mAll.qtyLow, mAll.qtyHigh, qtyRange2.qtyCount ,; > mAll.prID, mAll.prLow, mAll.prHigh, mAll.prCount ; >FROM mAll, citRange2, yrRange2, qtyRange2 ; >INTO TABLE tALL >BROWSE WIDTH 7 >wait > >