IN WITH NO OUT

CREATE PROCEDURE RptOnInMotorWithNoOut
@AsOf DATETIME
AS

select
*
from
(
SELECT Y.ModelNo, Y.ChassisNo, Y.EngineNo, Y.KeyNo, Y.TranType, InNo = Y.ReferenceNo, InDate = Y.TransactDate, OutNo = X.ReferenceNo, OutDate = X.TransactDate FROM
(
select modelno, chassisno, engineno, keyno, trantype, referenceno, transactdate from audittrailmotor where
transactdate <= @AsOf
branchcode = '09'
and inveffect = '+'
) AS Y


LEFT JOIN


(
select modelno, chassisno, engineno, keyno, trantype, referenceno, transactdate from audittrailmotor where
branchcode = '09'
and inveffect = '-'
) AS X

ON X.MODELNO = Y.MODELNO
and X.Chassisno = Y.ChassisNo
AND X.EngineNo = Y.EngineNo
AND X.KeyNo = Y.KeyNo

) as z
WHERE OUTDATE IS NULL
ORDER BY MODELNO, CHASSISNO, ENGINENO, KEYNO