I want to have the details of service calls with the invoice if done.
Columns in my query are Callid , CallDate , Customer , ServiceProduct , Fault Reported , Call Status , Invoice Number , Invoice Date
My query is as follows
select oscl.callid [Call Id] , OSCL.createDate [Call Date] , OSCL.custmrName [Service Customer], OSCL.itemName [Service Product],
OSCL.subject [Fault Reported] , oscs.name [Call Status] ,
--SCL4.DocAbs , SCL4.Object ,
Oo.DocNum [Invoice No#], Oo.DOCDATE [Invoice Date], Oo.U_lrno , Oo.U_lrdate , Oo.DocEntry , SCL4.DocAbs
from OSCL inner join SCL4 on OSCL.callID = SCL4.SrcvCallID
(select oinv.docentry , oinv.docnum , OINV.docdate , OINV.u_lrno , OINV.u_lrdate from oinv ) as Oo on Oo.DocEntry = SCL4.DocAbs and SCL4.Object = 13
inner join OSCS on OSCL.status = OSCS.statusID
WHERE OSCL.CREATEDATE >= '01/APR/2012'
ORDER BY OSCL.createDate
Above one is giving me the details of only those calls whose invoicing has been done.
If I use left outer join then the records are getting repeated per expense document.
Please help me.