CREATE PROCEDURE usp_Backend_getPossibleParentSKUsForSKU

@subordinateSKUID integer,
@when datetime

AS

if (isDate(@when) = 0) set @when = getDate()

SELECT  Subcategory.SubcategoryName,
	SKU.SKUID as parentSKUID, SKU.SKU as parentSKU, SKU.Color

FROM SubCategory left join SKU on Subcategory.subcategoryID = SKU.SubcategoryID

where subcategory.Subordinate = 0
	and subcategory.time_validFrom <= @when and subcategory.time_validTo >= @when
	and SKU.time_validFrom <= @when and SKU.time_validTo >= @when
	and subcategory.manufacturerID in (
		select manufacturerID
		from subcategory left join SKU on subcategory.subcategoryID = SKU.subcategoryID
		where SKU.SKUID = @subordinateSKUID
			and subcategory.time_validFrom <= @when and subcategory.time_validTo >= @when
			and SKU.time_validFrom <= @when and SKU.time_validTo >= @when
	)
	and SKU.SKUID not in (select distinct parentSKUID from subordinate where subordinateSKUID = @subordinateSKUID and time_validFrom <= @when and time_validTo >= @when)

order by subcategory.subcategoryName, SKU.color, SKU.SKU

GO