Query for ourgoing payments on Account

[kinyanjui_kamau]

Hi Experts,

I have the following query that I want to use to query outgoing payments for a CashBook I want to build. This is for Accounts only:

SELECT DISTINCT

T0.[RefDate], T1.[ContraAct], T0.[Ref2], T1.[Debit], T1.[Credit], T2.[Address], T3.Descrip AS 'Description'

FROM

OJDT T0 INNER JOIN

JDT1 T1 ON T0.[TransId] = T1.[TransId] INNER JOIN

OVPM T2 ON T0.[TransId] = T2.[TransId] INNER JOIN

VPM4 T3 ON T2.[DocEntry] = T3.[DocNum]

WHERE T0.RefDate BETWEEN '04-01-2015' AND '04-30-2015' AND T2.DocEntry=T3.DocNum

AND T2.CounterRef = '478' AND T2.DocType IN ('A')

When I run the above query, I get the following results:

It returns 8 rows for the following outgoing payment that has two lines:

How do I tweak the query above to return just the 2 rows as in the image above such that the result of the query is:

Any help appreciated.

Regards,

Kinyanjui.

[former_member212181 · Accepted solution]

Hi Kinyanjul,

Please try below query

SELECT T0.[DocDate], T1.[AcctCode], T0.[CounterRef]

, Case When T1.SumApplied >0 then T1.SumApplied else 0 end [Debit]

, Case When T1.SumApplied <0 then T1.SumApplied else 0 end [Credit]

, T0.[Address], T2.[AcctName]

FROM OVPM T0

INNER JOIN VPM4 T1 ON T0.[DocEntry] = T1.[DocNum]

INNER JOIN OACT T2 ON T1.AcctCode = T2.[AcctCode]

Where T0.DocDate >=[%0] and T0.DocDate<=[%1] and T0.[CounterRef] ='478'

Its taking data directly from OVPM and VPM4.

Thanks

Unnikrishnan

[kinyanjui_kamau · Accepted solution]

Hi Unnikrishnan,

I need the amount data to be taken from JDT1, this is just a part of a larger query to combine out going payments (CounterRefs A,S,C) and incoming payments to make a complete cash book.

Regards,

Kinyanjui.

[former_member212181 · Accepted solution]

Please try below.

Changes Done: 1) add one more condition in join OVPM T2   (T0.TransType =46 ).

2) one more condition in where condition to filter Cash account row - T1.Account <> T2.CashAcct

SELECT Distinct T1.TransID,

T0.[RefDate], T1.[ContraAct], T0.[Ref2], T1.[Debit], T1.[Credit], T2.[Address], T3.Descrip AS 'Description'

FROM

OJDT T0 INNER JOIN

JDT1 T1 ON T0.[TransId] = T1.[TransId]

INNER JOIN OVPM T2 ON T0.TransType =46 and   T0.[TransId] = T2.[TransId]

INNER JOIN VPM4 T3 ON T2.[DocEntry] = T3.[DocNum]

WHERE T0.RefDate BETWEEN '01-04-2015' AND '10-07-2015' AND T2.DocEntry=T3.DocNum

AND T2.CounterRef = '478' AND T2.DocType IN ('A') and T1.Account <> T2.CashAcct

Thanks

Unnikrishnan

[kinyanjui_kamau · Accepted solution]

Hi Unnikrishnan,

Your query returns:

Regards,

Kinyanjui.

[former_member212181 · Accepted solution]

Hi,

Please try below Query

SELECT Distinct T0.[RefDate], T1.Account, T0.[Ref2], T1.[Debit], T1.[Credit], T2.[Address], T3.Descrip AS 'Description'

FROM

OJDT T0 INNER JOIN

JDT1 T1 ON T0.[TransId] = T1.[TransId]

INNER JOIN OVPM T2 ON T0.TransType =46 and   T0.[TransId] = T2.[TransId] and T1.Account <> T2.CashAcct

INNER JOIN VPM4 T3 ON T2.[DocEntry] = T3.[DocNum] and T1.Account = T3.AcctCode

WHERE T0.RefDate BETWEEN '01-04-2015' AND '10-07-2015' AND T2.DocEntry=T3.DocNum

AND T2.CounterRef = '478' AND T2.DocType IN ('A')

Thanks

Unnikrishnan

[kinyanjui_kamau · Accepted solution]

Thanks Unnikrishnan. That is it.

Regards,

Kinyanjui.

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT T0.[DocNum], T0.[DocDate], T2.[ContraAct], T2.[Debit], T2.[Credit] FROM OVPM T0  INNER JOIN OJDT T1 ON T0.[TransId] = T1.[TransId] INNER JOIN JDT1 T2 ON T1.[TransId] = T2.[TransId] WHERE T1.[RefDate] between [%0] and [%1] and  T0.[DocType]  = 'A' and  T0.[CounterRef]  =  '478'

Thanks

[Former Member]

Hi Kamau,

Check below Query,

SELECT DISTINCT

T0.[RefDate], T1.[ContraAct], T0.[Ref2], T1.[Debit], T1.[Credit], T2.[Address], T3.Descrip AS 'Description'

FROM

OJDT T0 INNER JOIN

JDT1 T1 ON T0.[TransId] = T1.[TransId] INNER JOIN

OVPM T2 ON T0.[TransId] = T2.[TransId] INNER JOIN

VPM4 T3 ON T2.[DocEntry] = T3.[DocNum]

WHERE T0.RefDate BETWEEN '20150709' AND '20150709' 

AND T2.CounterRef = '255' AND T2.DocType IN ('A')

Hope this helps

Thanks

--

--

Regards::::

Atul Chakraborty

[kinyanjui_kamau]

Hi, Atul,

Tried above query, still brings duplicates.

Regards,

Kinyanjui

[Former Member]

Hi Kamau,

Try this query

SELECT DISTINCT

T0.[RefDate], T1.[ContraAct], T0.[Ref2], Sum(T1.[Debit]), Sum(T1.[Credit]), T2.[Address], T3.Descrip AS 'Description'

FROM

OJDT T0 INNER JOIN

JDT1 T1 ON T0.[TransId] = T1.[TransId] INNER JOIN

OVPM T2 ON T0.[TransId] = T2.[TransId] INNER JOIN

VPM4 T3 ON T2.[DocEntry] = T3.[DocNum]

WHERE T0.RefDate BETWEEN '04-01-2015' AND '04-30-2015' AND T2.DocEntry=T3.DocNum

AND T2.CounterRef = '478' AND T2.DocType IN ('A')

Group By

T0.[RefDate], T1.[ContraAct], T0.[Ref2],  T2.[Address], T3.Descrip

Regards,

Harshal Makwana

[kinyanjui_kamau]

Thanks for the reply Harshal,

Not the results I'm looking for, I get 4 lines returned plus i don't want the sum, just the individual debit or credit entries.

Regards,

Kinyanjui.

[Former Member]

Hi Kamau,

Try this work around,

SELECT DISTINCT

T0.[RefDate], T1.[ContraAct], T0.[Ref2], T1.[Debit], T1.[Credit], T2.[Address],

Max(T3.Descrip) AS 'Description 1st Row',

Min(T3.Descrip) AS 'Description 2nd Row'

FROM

OJDT T0 INNER JOIN

JDT1 T1 ON T0.[TransId] = T1.[TransId] INNER JOIN

OVPM T2 ON T0.[TransId] = T2.[TransId] INNER JOIN

VPM4 T3 ON T2.[DocEntry] = T3.[DocNum]

WHERE T0.RefDate BETWEEN '04-01-2015' AND '04-30-2015' AND T2.DocEntry=T3.DocNum

AND T2.CounterRef = '478' AND T2.DocType IN ('A')

Group by T0.[RefDate], T1.[ContraAct], T0.[Ref2], T1.[Debit], T1.[Credit], T2.[Address]

I guess this will work in your case.

Thanks,

Harshal Makwana

[kinyanjui_kamau]

Hi Harshal,

Your query returns this: (4 rows)

Regards,

Kinyanjui

[Former Member]

Hi Kamau,

Do you require description ? If not, you can try removing description it will give you correct result.

Regards,

Harshal Makwana