Skip to Content

Need help to modify Query for crystal report

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Jun 05, 2015 at 05:10 AM

    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.TransIdWHERET0.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

    Add comment
    10|10000 characters needed characters exceeded

    • 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.TransIdWHERET0.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.TransIdWHERET0.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

  • Jun 06, 2015 at 12:02 AM

    Hi,

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

    Thanks.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 06, 2015 at 05:32 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded