with orders as ( SELECT o.ordernum, o.orderid, o.orderdate, o.FSPro_UserID, o.RequesterFName, o.RequesterLName, o.RequesterFName + ' ' + o.RequesterLName as RequesterName, isnull(o.shp_Company,'') as shp_Company, o.shp_firstName + ' ' + o.shp_lastName as shiptoName ,o.Email, o.proxyOrderPlacedByUsrId, o.dtneeded, o.status, o.shp_Date, o.rejectReason, o.smpl_orderType, o.approverUserId, t.orderTypeCode, t.orderTypeDesc, t.orderTypeSysType, e.eventName, b.broker_name, floor( 10191817 * sqrt( orderid * log10( orderid ))) as tk, coalesce(w.requestId, null) as reqId, w.disposition FROM tbl_POS_Orders o with (nolock) INNER JOIN tbl_POS_OrderTypes t with (noLock) ON t.orderTypeId = o.smpl_orderTypeId LEFT OUTER JOIN tbl_EVT_Events e with (nolock) ON o.smpl_eventId = e.eventId left outer join tblBrokers b with (nolock) on b.broker_id = o.requesterOwnerId and 'BRO' = o.requesterOwnerType left outer join tbl_WRK_UserRequests w on w.disposition = 'Z' and w.ownerId = o.ownerId and w.ownertype = o.fsl_tablecode and w.config like CONCAT('ORDERID=', o.orderid) and w.toUserId = and w.toUserId = and w.response like 'This request expired%' WHERE o.OwnerID = AND o.FSL_TableCode = AND o.status IN () AND (o.ApproverUserId = OR o.FSPro_UserID = OR o.proxyOrderPlacedByUsrId = ) AND (o.FulfillmentUserId = OR o.FSPro_UserID = OR o.proxyOrderPlacedByUsrId = ) AND (o.TerritoryID IN () OR o.FSPro_UserID = OR o.proxyOrderPlacedByUsrId = ) AND (o.FSPro_UserID = OR o.proxyOrderPlacedByUsrId = ) AND o.smpl_orderTypeId = AND o.FSPro_UserID = AND (o.orderNum LIKE OR o.RequesterFName LIKE OR o.RequesterLName LIKE ) ) #forJSON( 'orders', attributes.dataStore, 'orderdate desc' )#