with allProductCatalogLastUpdate as ( -- level 1 select rp.requestId, s.lastUpdate from tbl_TPM_RequestProducts rp with (nolock) inner join tbl_DW_ProductHierarchy_#qmd_batch.ownerType##qmd_batch.ownerId# ph with (nolock) on ph.skuId = rp.catalogId inner join tbl_PRD_SKUs s with (nolock) on s.ownerId = ph.ownerId and s.fsl_tablecode = ph.ownerType and s.skuId = ph.skuId where rp.catalogType ='SKU' and rp.ownerId = and rp.ownerType = union all -- level 2 select rp.requestId, s.lastUpdate from tbl_TPM_RequestProducts rp with (nolock) inner join tbl_DW_ProductHierarchy_#qmd_batch.ownerType##qmd_batch.ownerId# ph with (nolock) on ph.prodId = rp.catalogId inner join tbl_PRD_SKUs s with (nolock) on s.ownerId = ph.ownerId and s.fsl_tablecode = ph.ownerType and s.skuId = ph.skuId where rp.catalogType ='PRD' and rp.ownerId = and rp.ownerType = union all -- level 3 select rp.requestId, s.lastUpdate from tbl_TPM_RequestProducts rp with (nolock) inner join tbl_DW_ProductHierarchy_#qmd_batch.ownerType##qmd_batch.ownerId# ph with (nolock) on ph.categoryId = rp.catalogId inner join tbl_PRD_SKUs s with (nolock) on s.ownerId = ph.ownerId and s.fsl_tablecode = ph.ownerType and s.skuId = ph.skuId where rp.catalogType ='CAT' and rp.ownerId = and rp.ownerType = union all -- level 4 select rp.requestId, s.lastUpdate from tbl_TPM_RequestProducts rp with (nolock) inner join tbl_DW_ProductHierarchy_#qmd_batch.ownerType##qmd_batch.ownerId# ph with (nolock) on ph.prodline_Id = rp.catalogId inner join tbl_PRD_SKUs s with (nolock) on s.ownerId = ph.ownerId and s.fsl_tablecode = ph.ownerType and s.skuId = ph.skuId where rp.catalogType ='LIN' and rp.ownerId = and rp.ownerType = union all -- all skus select rp.requestId, s.lastUpdate from tbl_TPM_RequestProducts rp with (nolock) inner join tbl_DW_ProductHierarchy_#qmd_batch.ownerType##qmd_batch.ownerId# ph with (nolock) on ph.ownerId = rp.ownerId and ph.ownerType = rp.ownerType inner join tbl_PRD_SKUs s with (nolock) on s.ownerId = ph.ownerId and s.fsl_tablecode = ph.ownerType and s.skuId = ph.skuId where rp.catalogType ='MFR' and rp.ownerId = and rp.ownerType = ), productCatalogLastUpdate as ( select requestId, max( lastUpdate ) lastUpdate from allProductCatalogLastUpdate group by requestId ) select r.requestId, r.assignedContractNbr, r.description, format( r.endDate, 'MM/dd/yyyy' ) endDate, pc.lastUpdate productCatalogDate, ( select top 1 updateDate from tbl_TPM_RequestSkus rs with (nolock) where rs.ownerId = r.ownerId and rs.ownerType = r.ownerType and rs.requestId = r.requestId ) requestSkusDate from tbl_TPM_Requests r with (nolock) inner join productCatalogLastUpdate pc on pc.requestId = r.requestId where r.ownerId = and r.ownerType = and ( not exists ( select * from tbl_TPM_RequestSkus rs with (nolock) where rs.ownerId = r.ownerId and rs.ownerType = r.ownerType and rs.requestId = r.requestId ) or r.updateDate > ( select top 1 updateDate from tbl_TPM_RequestSkus rs with (nolock) where rs.ownerId = r.ownerId and rs.ownerType = r.ownerType and rs.requestId = r.requestId ) or ( ( year( r.endDate ) < year( getDate() ) + 100 ) and dateadd( year, 2, r.endDate ) >= getDate() and pc.lastUpdate > ( select top 1 updateDate from tbl_TPM_RequestSkus rs with (nolock) where rs.ownerId = r.ownerId and rs.ownerType = r.ownerType and rs.requestId = r.requestId )) ) and exists ( select * from tbl_TPM_RequestProducts rp with (nolock) where rp.ownerId = r.ownerId and rp.ownerType = r.ownerType and rp.requestId = r.requestId ) and r.status = 'APPROVED' Nothing to update at this time

Updating Request SKUs for:

update tbl_EAI_inboundBatches set statusTime = getDate(), message = 'Updating #currentRow# of #recordCount#' where batchId = and ownerId = and ownerType = select count( distinct requestProductId ) rawCount, count( * ) as skuCount from tbl_TPM_RequestSkus with (nolock) where ownerId = and ownerType = and requestId =