SELECT c.title, c.contract, c.clientContract, coalesce(o.companyname,dst.cdr_dstname) as CompanyName, c.orgid, c.orgType, o.MFRCustNum , SortOrder = CASE WHEN ca.ProductLevel = 'ALL' THEN 5 WHEN ca.ProductLevel = 'PDL' THEN 4 WHEN ca.ProductLevel = 'CAT' THEN 3 WHEN ca.ProductLevel = 'PRD' THEN 2 WHEN ca.ProductLevel = 'SKU' THEN 1 ELSE 1 END , pk.master_net_weight, pk.master_net_weight_unit, c.startdate, c.enddate , c.comments, coalesce(o.companyname,dst.cdr_dstname) as CompanyName, c.ExcludeOPR_YN, c.ExcludeDST_YN , c.accrualRate, ca.allowanceid, ca.productlevel, ca.productCode, ca.productdesc, ca.rate , ca.startdate as cntStart, ca.enddate as cntEnd, c.UnitOfMeasure , AdminFeeFieldLabel1, AdminFeeDesc1, AdminFeeFieldLabel2, AdminFeeDesc2 FROM tbl_TBA_Contracts c with (nolock) left outer join tbl_TBA_ContractAllowance ca with (nolock) ON (c.contractid = ca.contractid) left outer join tbl_PRD_Skus s with (nolock) ON (s.OwnerID = AND s.FSL_TableCode = AND s.sku = ca.ProductCode AND ca.productLevel = 'SKU') left outer join tbl_PRD_Packaging pk with (nolock) ON (s.skuid = pk.linkid AND pk.LinkType = 'SKU') left outer join tbl_OPR_ClientOperators o with (nolock) on c.orgid = o.operatorid and c.orgType = 'OPR' left outer join tbl_CDR_distributors dst with (nolock) on c.orgid = dst.cdr_recordID and c.orgType = 'DST' WHERE c.OwnerID = AND c.FSL_TableCode = AND year(c.enddate) >= year( DATEADD( year, #variables.YearBack#, getDate() ) ) ORDER BY SortOrder desc, ca.productCode