select sc.subcategoryID,
	sc.subcategoryName,
	min(ss.price) as minPrice,
	max(ss.price) as maxPrice,
	min(sz.width) as minWidth,
	min(sz.length) as minLength,
	max(sz.width) as maxWidth,
	max(sz.length) as maxLength,
	min(sz.width * sz.length) as minArea,
	max(sz.width * sz.length) as maxArea,
	(min(sz.width) * min(sz.length)) as calcMinArea,
	(max(sz.width) * max(sz.length)) as calcMaxArea,
	count(ss.sizeID) as numSizes

from XRef_SizeSubcategory ss
	left join [Size] sz on ss.sizeID = sz.sizeID
	left join subcategory sc on ss.subcategoryID = sc.subcategoryID

group by sc.subcategoryID, sc.subcategoryName

/*
having
	(min(sz.width) * min(sz.length)) = min(sz.width * sz.length)
	and (max(sz.width) * max(sz.length)) = max(sz.width * sz.length)
*/

CREATE VIEW dbo.RugSizes AS

SELECT
	sc.collectionID, 
	sc.subcategoryID, 
	sc.subcategoryName, 
	ss.price, 
	sz.width, 
	sz.length, 
	sz.width * sz.length AS area

FROM dbo.SizeSubcategory ss 
	LEFT OUTER JOIN dbo.Size sz ON ss.sizeID = sz.sizeID
	LEFT OUTER JOIN dbo.Subcategory sc ON ss.subcategoryID = sc.subcategoryID

WHERE (sc.categoryID = 3) 
ORDER BY sc.collectionID, sc.subcategoryID, area

select bMin.subcategoryID, 
	bMin.price as minPrice, bMin.width as minWidth,
	bMin.length as minLength, bMin.area as minArea,
	bMax.price as maxPrice, bMax.width as maxWidth,
	bMax.length as maxLength, bMax.area as maxArea

from
	v_rugSizes bMin,
	v_rugSizes bMax,
	(
		select a.subcategoryID,
			min(a.area) as theMinArea,
			max(a.area) as theMaxArea
		from v_rugSizes a
		group by a.subcategoryID
	) as a

where (a.subcategoryID = bMin.subcategoryID and a.theMinArea = bMin.area)
	and (a.subcategoryID = bMax.subcategoryID and a.theMaxArea = bMax.area)

order by bMin.subcategoryID