Skip to Content
avatar image
Former Member

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers