Need help to modify Query for crystal report

[former_member269992]

Hello Expert,

Posted below is the query for Customer aging report as at current Date.

SELECT

    T0.ShortName, T1.CardName,

    T0.RefDate, T0.DueDate,

    T0.TransType, T0.Ref2, T2.DocNum,

    T2.U_Suffix,

    SUM(T0.BalDueDeb - T0.BalDueCred) AS AmtDue

FROM

    dbo.JDT1 AS T0 INNER JOIN

    dbo.OCRD AS T1

	ON T0.ShortName = T1.CardCode
	LEFT OUTER JOIN

    dbo.OINV AS T2

	ON T0.TransId = T2.TransId
WHERE

    (T0.ShortName = '{?@CustCode}')

GROUP BY

    T0.ShortName, T1.CardName,

    T0.RefDate, T0.TransType,

    T0.Ref2, T2.DocNum,

    T2.U_Suffix, T0.DueDate

HAVING

    (SUM(T0.BalDueDeb - T0.BalDueCred) <> 0)

I want to modify this query as we want this report till 31st march 2015.

Please help to modify this query.

Regards,

Hitul

[KennedyT21 · Accepted solution]

Try This,

SELECT

    T0.ShortName, T1.CardName,

    T0.RefDate, T0.DueDate,

    T0.TransType, T0.Ref2, T2.DocNum,

    T2.U_Suffix,

    SUM(T0.BalDueDeb - T0.BalDueCred) AS AmtDue

FROM

    dbo.JDT1 AS T0 INNER JOIN

    dbo.OCRD AS T1

	ON T0.ShortName = T1.CardCode
	LEFT OUTER JOIN

    dbo.OINV AS T2

	ON T0.TransId = T2.TransId
WHERE	T0.RefDate<='20150331' and

    (T0.ShortName = '{?@CustCode}')

GROUP BY

    T0.ShortName, T1.CardName,

    T0.RefDate, T0.TransType,

    T0.Ref2, T2.DocNum,

    T2.U_Suffix, T0.DueDate

HAVING

    (SUM(T0.BalDueDeb - T0.BalDueCred) <> 0)

Rgds

Kennedy

[former_member269992 · Accepted solution]

Hi Kennedy,

I made changes as instructed by you T0.RefDate<='20150331'

but in report I am only getting A/R credit memo entry. not getting A/R Invoice entry.

Regards,

Hitul

[former_member269992 · Accepted solution]

Following is the screen shot of the report.

Hitul

[KennedyT21 · Accepted solution]

Hi

try this

SELECT

    T0.ShortName, T1.CardName,

    T0.RefDate, T0.DueDate,

    T0.TransType, T0.Ref2, T2.DocNum,

   -- T2.U_Suffix,

    SUM(T0.BalDueDeb - T0.BalDueCred) AS AmtDue

FROM

    dbo.JDT1 AS T0 INNER JOIN

    dbo.OCRD AS T1

	ON T0.ShortName = T1.CardCode
	--LEFT OUTER JOIN    dbo.OINV AS T2

--	ON T0.TransId = T2.TransId
WHERE	T0.RefDate<='20150331' and

    (T0.ShortName = '{?@CustCode}')

GROUP BY

    T0.ShortName, T1.CardName,

    T0.RefDate, T0.TransType,

    T0.Ref2

--, T2.DocNum,

   -- T2.U_Suffix

, T0.DueDate

HAVING

    (SUM(T0.BalDueDeb - T0.BalDueCred) <> 0)

[former_member269992 · Accepted solution]

Hi Kennedy,

I have removed

T2.DocNum,

   -- T2.U_Suffix, from Select

From--LEFT OUTER JOIN    dbo.OINV AS T2

--	ON T0.TransId = T2.TransId

Group By

T2.DocNum,

   -- T2.U_Suffix

Still the same result.

Regards,

Hitul

[former_member212181 · Accepted solution]

Hi Hitul,

Please try below query

SELECT

    T0.ShortName, T1.CardName,

    T0.RefDate, T0.DueDate,

    T0.TransType, T0.Ref2, T2.DocNum,

    Case T0.TransType When  13 then T2.U_Suffix when 14 then T3.U_Suffix else 0 end,

    SUM(T0.BalDueDeb - T0.BalDueCred) AS AmtDue

FROM

    dbo.JDT1 T0 INNER JOIN

    OCRD T1 ON T0.ShortName = T1.CardCode

	LEFT OUTER JOIN OINV T2 ON T0.TransType =13 and  T0.TransId = T2.TransId
	LEFT OUTER JOIN ORIN T3 ON T0.TransType =14 and  T0.TransId = T3.TransId
WHERE	T0.RefDate<='20150331' and

    (T0.ShortName = '{?@CustCode}')

GROUP BY

    T0.ShortName, T1.CardName,

    T0.RefDate, T0.TransType,

    T0.Ref2, T2.DocNum,

   T2.U_Suffix, T3.U_Suffix,

T0.DueDate

HAVING

    (SUM(T0.BalDueDeb - T0.BalDueCred) <> 0)

Thanks

Unnikrishnan

[former_member269992 · Accepted solution]

Hi Unnikrishanan,

I tried this query, but getting following error.

"Conversion failed when convering the nvarchar value 'SS' to data type int. [Database Vendor Code 245]".

Regards,

Hitl

[former_member212181 · Accepted solution]

Hi Hitul,

Please try below

--Changed area      (T0.ShortName = N'{?@CustCode}')

--Change: N added after =

SELECT

    T0.ShortName, T1.CardName,

    T0.RefDate, T0.DueDate,

    T0.TransType, T0.Ref2, T2.DocNum,

    Case T0.TransType When  13 then T2.U_Suffix when 14 then T3.U_Suffix else 0 end,

    SUM(T0.BalDueDeb - T0.BalDueCred) AS AmtDue

FROM

    dbo.JDT1 T0 INNER JOIN

    OCRD T1 ON T0.ShortName = T1.CardCode

	LEFT OUTER JOIN OINV T2 ON T0.TransType =13 and  T0.TransId = T2.TransId
	LEFT OUTER JOIN ORIN T3 ON T0.TransType =14 and  T0.TransId = T3.TransId
WHERE	T0.RefDate<='20150331' and

    (T0.ShortName = N'{?@CustCode}')

GROUP BY

    T0.ShortName, T1.CardName,

    T0.RefDate, T0.TransType,

    T0.Ref2, T2.DocNum,

   T2.U_Suffix, T3.U_Suffix,

T0.DueDate

HAVING

    (SUM(T0.BalDueDeb - T0.BalDueCred) <> 0)

Thanks

Unnikrishnan

[former_member269992 · Accepted solution]

Hi Unnikrishnan,

by this change getting only Credit memo, not giving A/R Invoice in the result.

Regards,

Hitul

[former_member212181 · Accepted solution]

Hi Hitul,

The same query gives both AR Invoice and AR Credit memo in my system.

Are you sure about you have open AR Invoice or AR Credit memo in your system which are created on or before 2015-mar-31 for given customer??

Thanks

Unnikrishnan

[KennedyT21 · Accepted solution]

Try This

SELECT

    T0.ShortName, T1.CardName,

    T0.RefDate, T0.DueDate,

    T0.TransType, T0.Ref2, T2.DocNum,

     T2.U_Suffix,

    SUM(T0.BalDueDeb - T0.BalDueCred) AS AmtDue

FROM

    dbo.JDT1 AS T0 INNER JOIN

    dbo.OCRD AS T1

ON T0.ShortName = T1.CardCode

inner JOIN    dbo.OINV AS T2

	ON T0.TransId = T2.TransId
WHERE	T0.RefDate<='20150331' and

    (T0.ShortName = '{?@CustCode}')

GROUP BY

    T0.ShortName, T1.CardName,

    T0.RefDate, T0.TransType,

    T0.Ref2

, T2.DocNum,

    T2.U_Suffix

, T0.DueDate

UNION ALL

SELECT

    T0.ShortName, T1.CardName,

    T0.RefDate, T0.DueDate,

    T0.TransType, T0.Ref2, T2.DocNum,

     T2.U_Suffix,

    SUM(T0.BalDueDeb - T0.BalDueCred) AS AmtDue

FROM

    dbo.JDT1 AS T0 INNER JOIN

    dbo.OCRD AS T1

ON T0.ShortName = T1.CardCode

inner JOIN    dbo.orin AS T2

	ON T0.TransId = T2.TransId
WHERE	T0.RefDate<='20150331' and

    (T0.ShortName = '{?@CustCode}')

GROUP BY

    T0.ShortName, T1.CardName,

    T0.RefDate, T0.TransType,

    T0.Ref2

, T2.DocNum,

    T2.U_Suffix

, T0.DueDate

Regards

Kennedy

[isaac_kalii]

Hi Hitul,

Please try the below:

SELECT	TOP (100) PERCENT dbo.JDT1.ShortName, OCRD_1.CardName, dbo.OJDT.RefDate, dbo.OJDT.DueDate, dbo.OJDT.TransType, dbo.OJDT.Ref2,
	SUM(dbo.JDT1.BalDueDeb - dbo.JDT1.BalDueCred) AS AmountDue
FROM	dbo.OCRD AS OCRD_1 INNER JOIN
	dbo.OCRD INNER JOIN
	dbo.JDT1 INNER JOIN
	dbo.OJDT ON dbo.JDT1.TransId = dbo.OJDT.TransId ON dbo.OCRD.CardCode = dbo.JDT1.ShortName ON OCRD_1.CardCode = dbo.OCRD.ChannlBP INNER JOIN
	dbo.CRD1 ON dbo.OCRD.ChannlBP = dbo.CRD1.CardCode
WHERE	(dbo.CRD1.AdresType = 'b')

GROUP BY OCRD_1.CardName, dbo.JDT1.ShortName, dbo.OJDT.RefDate, dbo.OJDT.DueDate, dbo.OJDT.TransType, dbo.OJDT.Ref2

HAVING	(SUM(dbo.JDT1.BalDueDeb - dbo.JDT1.BalDueCred) <> 0) AND (SUM(CASE WHEN (DATEDIFF(dd, jdt1.duedate, CURRENT_TIMESTAMP))
	> 0 THEN balduedeb - balduecred ELSE 0 END) > 0)

ORDER BY OCRD_1.CardName

Regards,

Isaac.

[former_member269992]

Hi Isaac,

I tried your suggested query not getting any result(0 lines affected).

Regards,

Hitul

[kothandaraman_nagarajan]

Hi,

Your query itself does not giving credit memo details. Is it complete query?

Thanks.