Help improving sql query

 Select distinct pc.codeID,pc.codeName,pc.AwardAmmount,pc.description  
                        from PromoCode PC
                     inner join lkupPromoCode_location PL  WITH (NOLOCK) on pc.CodeID = PL.codeID
                     left outer join lkuppromoCode_product PP WITH (NOLOCK) on pp.codeID = PC.CodeID
                 left outer join lkupPromocode_package PPP WITH (NOLOCK) on PPP.codeID = PC.CodeID
                 
                     WHERE PC.status = 1  
                     and PC.StartDate <='#DateFormat(now(),"mm/dd/yyyy")#'  
                 and PC.promoType = 0
                 AND ((pc.genEmail  is NOT NULL and pc.genEmail = (select U.email from users U where u.userKey=#session.userid#)) OR   pc.genEmail  is NULL)

                 and DATEDIFF(D,pc.CodeIssueExpiry,GETDATE()) <= 0
                 and PL.LocationID  IN (Select lkupPromoCode_location.locationID from lkupPromoCode_location  where lkupPromoCode_location.codeID=pc.codeID and lkupPromoCode_location.type = 1) 
                     and (ppp.packageID in 
                                 (Select lkupPromoCode_package.packageID 
                                 from lkupPromoCode_package  
                                 where lkupPromoCode_package.codeID=pc.codeID 
                                 and lkupPromoCode_package.packageID in (#ArrayToList(ListToArray(session.promocode.packageKey))#) 
                              and lkupPromoCode_package.type = 1)
                              OR pp.productID in (
                              Select lkuppromoCode_product.productID 
                              from lkuppromoCode_product  
                              where lkuppromoCode_product.codeID=pc.codeID 
                              and lkuppromoCode_product.productID in (#ArrayToList(ListToArray(session.promocode.productKey))#) and lkuppromoCode_product.type = 1 ))

Select allOpen in new window