Skip to Content
Former Member
Nov 22, 2016 at 11:03 AM

Outstanding Query Help


Hi ,

Im using this query to check the outstanding,there is a small issue in this query that if one invoice has more than one item the corresponding outstanding amount gets appearing with respect to the no. of if billno. 200 has three items the outstanding amount for that customer will displayed 3 times .How can i resolve this?

--CEYENAR INTERNAL-- DECLARE @Date AS DATETIME DECLARE @Series AS varchar(30) DECLARE @Branch AS varchar(30) DECLARE @Exec AS varchar(30) /* SELECT FROM [dbo].[OINV] S0 WHERE */ SET @Date = /* S0.DocDate*/ '[%0]' /* SELECT FROM [dbo].[NNM1] S2 WHERE */ SET @Series = /* S2.SeriesName*/ '[%2]' /* SELECT FROM [dbo].[OLCT] S3 WHERE */ SET @Branch = /* S3.Location*/ '[%3]' /* SELECT FROM [dbo].[OSLP] S4 WHERE */ SET @Exec = /* S4.SlpName*/ '[%4]' if( @Exec ='') set @Exec ='%' if( @Series ='') set @Series ='%' if( @Branch ='') set @Branch ='%' if( @Date ='') set @Date =GETDATE() Select OINV.CardName [Customer Name],OCRG.GroupName,OSLP.slpname , ISNULL(NNM1.SeriesName ,'-')+'/'+ CAST(OINV.DocNum as varchar) [Bill No.], OINV.DocDate [Bill Date],INV1.Dscription,OITB.ItmsGrpNam,INV1.Quantity,INV1.Price, OINV.DocTotal - OINV.PaidToDate [Out Standing Amount], DATEDIFF(dd,OINV.DocDate,@Date)[No of Days] from OINV inner join INV1 on OINV.DocEntry = INV1.DocEntry inner join OLCT on INV1.LocCode = OLCT.Code inner join NNM1 on OINV.Series = NNM1.Series inner join OSLP on OINV.SlpCode = OSLP.SlpCode INNER JOIN OITM on INV1.ItemCode = OITM.ItemCode inner join OITB on OITM.ItmsGrpCod = OITB.ItmsGrpCod inner join OCRD ON OINV.CardCode = OCRD.CardCode INNER JOIN OCRG ON OCRD.GroupCode = OCRG.GroupCode where OINV.DocType ='I' and (OINV.DocTotal - OINV.PaidToDate)>0 and NNM1.SeriesName like @Series and OINV.DocDate<= @Date and OLCT.Location like @Branch and OSLP.SlpName like @Exec