Skip to Content
0

Outstanding Query Help

Nov 22, 2016 at 11:03 AM

30

avatar image

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 items.eg: 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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers