OUT WITH NO IN

CREATE PROCEDURE RptOnOutMotorWithNoIn
@AsOf DATETIME
AS

select
*
from
(
SELECT Y.ModelNo, Y.ChassisNo, Y.EngineNo, Y.KeyNo, Y.TranType, OutNo = Y.ReferenceNo, OutDate = Y.TransactDate, InNo = X.ReferenceNo, InDate = 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 InDate IS NULL
ORDER BY MODELNO, ChassisNo, EngineNo, KeyNo

GO