J.E query for particular account

[former_member646404]

Hello Experts,

I need to correct the following query.

SELECT T0.Number, T0.[RefDate],t0.ref1, T2.[Credit],T2.[Debit],T3.[AcctName],T2.LineMemo, T3.[FormatCode],T0.Memo , T0.Project , case when T2.[Credit] = 0 then 'DR' else 'CR' end as 'DRCR'
FROM OJDT T0 left JOIN JDT1 T2 ON T0.[TransId] = T2.[TransId] left JOIN OACT T3 ON T2.Account = T3.AcctCode

where t0.number = 10006071

i get the desired result

but when i want the entries related to one particular account i get just ONE line data.

I need whole entries in which this *('PREPAID IMPORT EXPENSES (KHI, GTS)')* particular account is used between selected dates.

Regards.

[jitin_chawla]

Hi,

From the result of the query run by you actually we can see only 1 line for Account Name "('PREPAID IMPORT EXPENSES (KHI, GTS)". Not sure what the exact expectations is but may be you can instead the date range and see if more rows where the Account Name is used appears.

Check this:

SELECT T0.Number, T0.[RefDate],t0.ref1, T2.[Credit],T2.[Debit],T3.[AcctName],T2.LineMemo, T3.[FormatCode],T0.Memo , T0.Project , 
case when T2.[Credit] = 0 then 'DR' else 'CR' end as 'DRCR'
FROM OJDT T0 left JOIN JDT1 T2 ON T0.[TransId] = T2.[TransId] left JOIN OACT T3 ON T2.Account = T3.AcctCode
WHERE T2.RefDate between '20191007' and '20191007' and T3.AcctName like '%PREPAID IMPORT EXPENSES (KHI, GTS)%'
--where t0.number = 10006071

[kothandaraman_nagarajan]

Hi,

Replace GL account number with your GL account,

SELECT T0.Number, T0.[RefDate],T2.account ,t0.ref1, T2.[Credit],T2.[Debit],T3.[AcctName],T2.LineMemo, T3.[FormatCode],T0.Memo , T0.Project , case when T2.[Credit] = 0 then 'DR' else 'CR' end as 'DRCR'

FROM OJDT T0 INNER JOIN JDT1 T2 ON T0.[TransId] = T2.[TransId] left JOIN OACT T3 ON T2.Account = T3.AcctCode

where T2.account = 410000

Regards,

Nagarajan