declare @now datetime set @now = getDate()
declare @quorterID integer
declare @parentSKU varchar(50)
declare @sqFt integer
declare @subordinateSKU varchar(50)
declare @ratio float
declare @subcategoryID integer
declare @categoryID integer
declare @price money
declare @qty integer

/* Cursor for going through all the parent items in the lineItem table */
declare c_lineItem cursor for
select distinct quorterID, SKU
from quorter_lineItem
where time_validFrom <= @now and time_validTo >= @now and subordinate = 0

open c_lineItem
fetch c_lineItem into @quorterID, @parentSKU

while (@@fetch_status = 0) begin
	/* Get all the subordinate items for this parent item 
        that aren't already in the lineItems table.  We're going to use another cursor for this */
	declare c_sub cursor for
	select distinct
		li.sqFt,
		Subordinate.SubordinateSKU, subordinate.ratio,
		Subcategory.subcategoryID, Subcategory.CategoryID,
		coalesce(SubcategorySKU.Price, Subcategory.Price) as Price

	FROM Quorter_LineItem li
		LEFT JOIN Subordinate ON li.SKU = Subordinate.SKU
		LEFT JOIN SubcategorySKU ON Subordinate.SubordinateSKU = SubcategorySKU.SKU
		LEFT JOIN Subcategory ON SubcategorySKU.SubCategoryID = Subcategory.subcategoryID

	WHERE li.SKU = @parentSKU
		and li.quorterID = @quorterID
		and li.Subordinate = 0
		and subcategory.time_validFrom <= @now
		and subcategory.time_validTo >= @now
		and subcategorySKU.time_validFrom <= @now
		and subcategorySKU.time_validTo >= @now
		and li.time_validFrom <= @now
		and li.time_validTo >= @now
		and ratio is not NULL
		and ratio > 0

		and subordinateSKU not in (select distinct SKU from quorter_lineItem where quorterID = @quorterID 
                and parentSKU = @parentSKU and subordinate = 1 and time_validFrom <= @now and time_validTo >= @now)

	open c_sub
	fetch c_sub into @sqFt, @subordinateSKU, @ratio, @subcategoryID, @categoryID, @price

	-- Go through each of the subordinate items for this parent SKU / quorter
	while (@@fetch_status = 0) begin
		if (@ratio >= 999999) begin
			set @qty = 1
		end else begin
			set @qty = ceiling(@sqFt / @ratio)
		end

		insert into quorter_lineItem (
			quorterID,
			SKU,
			categoryID,
			subcategoryID,
			price,
			qty,
			subordinate,
			parentSKU,
			time_transactionStart,
			time_transactionEnd,
			time_validFrom,
			time_validTo,
			modifiedBy
		)

		values (
			@quorterID,
			@subordinateSKU,
			@categoryID,
			@subcategoryID,
			@price,
			@qty,
			1,
			@parentSKU,
			@now,
			'9999-12-31',
			@now,
			'9999-12-31',
			5
		)

		fetch c_sub into @sqFt, @subordinateSKU, @ratio, @subcategoryID, @categoryID, @price
	end

	close c_sub
	deallocate c_sub

	fetch c_lineItem into @quorterID, @parentSKU
end

close c_lineItem
deallocate c_lineItem

GO

/*****************************************************************************/
/* ADD ALL THE SUBORDINATE ITEMS FOR ALL PARENT ITEMS IN THE LINEITEMS TABLE */
/*****************************************************************************/
print 'Starting "ADD ALL THE SUBORDINATE ITEMS FOR ALL PARENT ITEMS IN THE LINEITEMS TABLE"'

declare @now dateTime set @now = getDate()

insert into quorter_lineItem (
	quorterID,
	SKU,
	subcategoryID,
	categoryID,
	price,
	qty,
	subordinate,
	sqFt,
	parentSKU,
	time_validFrom,
	time_validTo,
	time_transactionStart,
	time_transactionEnd,
	modifiedBy
)

select distinct
	li.quorterID,
	Subordinate.SubordinateSKU as SKU,
	Subcategory.subcategoryID,
	Subcategory.CategoryID,
	coalesce(SubcategorySKU.Price, Subcategory.Price) as Price,
	cast(ceiling(sqFt / ratio) as integer) as qty,
	1 as subordinate,
	li.sqFt as sqFt,
	li.SKU as parentSKU,
	'1980-01-01' as time_validFrom,
	'9999-12-31' as time_validTo,
	@now as time_transactionStart,
	'9999-12-31' as time_transactionEnd,
	5 as modifiedBy

FROM Quorter_LineItem li
	LEFT JOIN Subordinate ON li.SKU = Subordinate.SKU
	LEFT JOIN SubcategorySKU ON Subordinate.SubordinateSKU = SubcategorySKU.SKU
	LEFT JOIN Subcategory ON SubcategorySKU.SubCategoryID = Subcategory.subcategoryID

WHERE li.Subordinate = 0
	and subcategory.time_validFrom <= @now
	and subcategory.time_validTo >= @now
	and subcategorySKU.time_validFrom <= @now
	and subcategorySKU.time_validTo >= @now
	and li.time_validFrom <= @now
	and li.time_validTo >= @now
	and subordinate.ratio is not NULL
	and subordinate.ratio > 0
	and li.quorterID is not NULL

	and not exists (select ID from quorter_lineItem li2 where li2.quorterID = li.quorterID 
                        and li2.parentSKU = li.parentSKU and li2.SKU = subordinate.subordinateSKU 
                        and subordinate = 1 and time_validFrom <= @now and time_validTo >= @now)

GO

select distinct
	li.quorterID,
	Subo.SubordinateSKU as SKU,
	sc.subcategoryID,
	sc.CategoryID,
	coalesce(sSKU.Price, sc.Price) as Price,
	cast(ceiling(sqFt / ratio) as integer) as qty,
	1 as subordinate,
	li.sqFt as sqFt,
	li.SKU as parentSKU

into #temptable

FROM quorter_lineItem_new li
	LEFT JOIN Subordinate subo ON li.SKU = Subo.SKU
	LEFT JOIN SubcategorySKU sSKU ON Subo.SubordinateSKU = sSKU.SKU
	LEFT JOIN Subcategory sc ON sSKU.SubCategoryID = sc.subcategoryID

WHERE li.Subordinate = 0
	and subo.ratio is not NULL
	and subo.ratio > 0
	and li.quorterID is not NULL

-- Proof of concept
select *
from #tempTable t
where not exists (
	select * from quorter_lineItem_new li

	where li.quorterID = t.quorterID 
		and li.parentSKU = t.parentSKU
		and li.SKU = t.SKU
		and li.subordinate = 1
)


drop table #temptable