2 Replies Latest reply on Mar 28, 2008 2:19 PM by Anthony Flores

    SQL Query writer

    Anthony Flores

      I am having a problem with one of my queries. I am trying to generate a report  that will provide the last invoice number, last invoice date, last activity date etc of a BP.  In my query, attached, I can get it to give the last invoice number and date but when I add the last activity (OCLG T3) it returns all associated with the BP and I just looking for the last activity. 

      Can anyone  help?

      Thanks

      Anthony

      SELECT T0.CardCode
          , T0.CardName
          , T0.State1 as "Bill-To State"
          , T1.SlpName
          , MAX(T2.DocNum) as "Lst Invoice Number"
          , MAX(T2.DocDate) as "Lst Invoice Date"
          , MAX(T3.enddate) as "Lst Contact Date"
          , T0.U_LastActivity as "Lst Activity from MOM"
          , T0.frozenFor as "Credit Hold"

      FROM OCRD T0
          INNER JOIN OSLP T1 ON T1.SlpCode = T0.SlpCode
         
        LEFT JOIN OINV T2 ON T2.CardCode = T0.CardCode
         
        OR T2.CardCode IN
         (SELECT T10.CardCode
          FROM OCRD T10
          WHERE T10.FatherCard = T0.CardCode)
         

      LEFT JOIN OCLG T3 ON T3.Cardcode = T0.Cardcode   
        

      WHERE T0.QryGroup4 = 'Y'
          AND T1.SlpName LIKE '%%sean%%'
          AND T0.cardcode ='c12413'
      GROUP BY T0.CardCode
          , T0.CardName
          , T0.State1
          , T1.SlpName
          , T0.U_LastActivity
          , T3.enddate
          , T0.frozenFor

        • 1. RE: SQL Query writer
          Mike Grant

          Anthony:  I don't write SQL or work with Queries much, just Crystal Reports, so...this is just a shot at helping you since nobody else took a crack at it. Your statement "LEFT JOIN OCLG T3 ON T3.Cardcode = T0.Cardcode" connects the BP table (OCRD) to the Activities table (OCLG). So it does not suprise me that you get all the activities for a BP, not just the one you want.  And I don't see any WHERE statements that would select that one activity from OCLG.  Try changing your links (JOINs).  Hope this helps a little.  Mike

          • 2. RE: SQL Query writer
            Anthony Flores

            Thanks for responding. I did finally figure it out. The problem was that  I had set the T3.enddate to group as well and that was conflicting.

            If you have any issues in the future let me know. We use to use Crystal Reports with Mail Order Manager (before SAPB1) and the reporting capability was limited. One of the things I really like about SQL is that you can pull any information from the software with only limitation of experience and sql education.

            Anthony