Query Help

[Former Member]

Hi ,

Need to add oinv.docnum,docdate,orct.docnum,docdate, and the difference between invoice date and collection date to this query.

declare @Branch nvarchar(max)=''

declare @Division nvarchar(max)=''

declare @SlpName nvarchar(max)=''

declare @BP nvarchar(max)=''

declare @FDate nvarchar(max)=''

declare @TDate nvarchar(max)=''

declare @Date nvarchar(max)=''

declare @_Date nvarchar(max)=''

declare @Query nvarchar(max)=''

/* SELECT FROM [dbo].[ORCT] S3 WHERE */ SET  @FDate = /* S3.DocDate*/ '[%3]'

/* SELECT FROM [dbo].[ORCT] S4 WHERE */ SET  @TDate = /* S4.DocDate*/ '[%4]'

/* SELECT FROM [dbo].[OSLP] S5 WHERE */ SET  @SlpName = /* S5.SlpName*/ '[%5]'

if @SlpName ='' set @SlpName ='%' if(@FDate='')set @FDate=getdate() if(@TDate='')set @TDate=getdate()

select [Sales Employee],[Customer Code],CardName,SUM([Collection])[Collection],SUM([Adjustment])[Adjustment],SUM(InvDocTotal)[InvDocTotal],SUM(RtnDocTotal)RtnDocTotal,DocNum from (

select  [Sales Employee],[Customer Code],CardName,case when Retrn<>0 then  (Collection)-isnull((Retrn),0) else Collection end [Collection],0 [Adjustment],isnull((select DocTotal from OINV where DocEntry= ORCT.[DocNum]),0)InvDocTotal,isnull(Retrn,0) RtnDocTotal,[DocNum] from(

select distinct OSLP.SlpName[Sales Employee], ORCT.CardCode[Customer Code],OCRD.CardName,sum(RCT2.SumApplied)[Collection],0[Retrn],0 RtnDoc,RCT2.DocEntry [DocNum]  From ORCT

inner join OCRD on ORCT.CardCode =OCRD.CardCode

INNER JOIN RCT2 ON ORCT.DocEntry = RCT2.DocNum

left join OSLP on OCRD.SlpCode=OSLP.SlpCode

where  (ORCT.DocDate between @FDate and @TDate) and OSLP.SlpName  like @SlpName and ORCT.Canceled<>'Y' and OCRD.CardType='C' 

group by OSLP.SlpName,ORCT.CardCode,OCRD.CardName,RCT2.DocEntry

union all

select OSLP.SlpName[Sales Employee], OVPM.CardCode[Customer Code],OCRD.CardName,0[Collection],sum(OVPM.DocTotal)[Retrn],0,'' [DocNum] from OVPM 

inner join OCRD on OVPM.CardCode =OCRD.CardCode 

left join OSLP on OCRD.SlpCode=OSLP.SlpCode

where  (OVPM.DocDate between @FDate and @TDate) and OSLP.SlpName  like @SlpName and OVPM.Canceled<>'Y'

group by OVPM.CardCode,osLP.SlpName,OCRD.CardName

)ORCT

union all

select [Sales Employee],[Customer Code],CardName,SUM(DocTotal)-SUM(Differ) [Collection],SUM(DocTotal)-SUM(Differ)[Adjustment],0 InvDocTotal,0 RtnDocTotal,''[DocNum] from

(select distinct OSLP.SlpName[Sales Employee], ORCT.CardCode[Customer Code],OCRD.CardName,0[DocTotal],isnull(sum(RCT2.SumApplied),0)[Differ],0[Retrn],'' [DocNum],0 RtnDoc  From ORCT

inner join OCRD on ORCT.CardCode =OCRD.CardCode

INNER JOIN RCT2 ON ORCT.DocEntry = RCT2.DocNum

left join OSLP on OCRD.SlpCode=OSLP.SlpCode

where  (ORCT.DocDate between @FDate and @TDate) and OSLP.SlpName  like @SlpName and ORCT.Canceled<>'Y' and OCRD.CardType='C' 

group by OSLP.SlpName,ORCT.CardCode,OCRD.CardName

union all

select distinct OSLP.SlpName[Sales Employee], ORCT.CardCode[Customer Code],OCRD.CardName,sum(ORCT.DocTotal)[DocTotal],0 [Differ],0[Retrn],'' [DocNum],0 RtnDoc  From ORCT

inner join OCRD on ORCT.CardCode =OCRD.CardCode

left join OSLP on OCRD.SlpCode=OSLP.SlpCode

where  (ORCT.DocDate between @FDate and @TDate) and OSLP.SlpName  like @SlpName and ORCT.Canceled<>'Y' and OCRD.CardType='C' 

group by OSLP.SlpName,ORCT.CardCode,OCRD.CardName)DIFFRENCE

group by [Sales Employee],[Customer Code],CardName)ORCT

group by [Sales Employee],[Customer Code],CardName,docnum

[kothandaraman_nagarajan · Accepted solution]

Any update?

[Former Member · Accepted solution]

Nagarajan ,

I've resolved this issue

[kothandaraman_nagarajan]

Hi,

Why OVPM table is included in above query?

Thanks & Regards,

Nagarajan