Skip to Content

Get sales quote number to sales order query + order lines on a single line

Hi all,

This is a 2 part question. The first half is probably something really simple but i cant see why it's not working (it works going from quote to sales order but not the other way). I need this to also show the T5.DocDate of the linked sales quotation but i cant get it to display.

SELECT DISTINCT T0.DocNum,T0.[CardCode], T0.[CardName],T1.FirstName,T1.LastName,T0.Address2,T1.Tel1,T1.Cellolar,T1.E_MailL,T2.SlpName,T5.DocDate
 FROM ORDR T0
INNER JOIN OCPR T1 ON T0.[CntctCode] = T1.[CntctCode]
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
INNER JOIN RDR1 T3 ON T3.DocEntry = T0.DocEntry
LEFT JOIN QUT1 T4 ON T4.BaseEntry = T3.DocEntry AND T4.BaseLine = T3.LineNum
LEFT JOIN OQUT T5 ON T5.DocEntry = T4.DocEntry
WHERE T0.series = '73' AND T0.DocStatus = 'O'

The second half is a bit more complicated and i dont know if it can be done. I need to display all the line items (itemcode and quantity) from a sales order on each details line in Crystal reports. Example below

As always, any help is appreciated.

info.jpg (46.0 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on Jul 28, 2020 at 05:28 AM

    Hi Nick,

    Part 1: I think maybe the join needs to go the other way around:

    LEFT OUTER JOIN QUT1 T4 ON T3.BaseEntry = T4.DocEntry AND T3.BaseLine = T4.LineNum

    Part 2: can be done, but is somewhat complicated, and also volatile. What do you expect the report to look like when there are say 25 item lines? Anyway, generally speaking you could use a cursor to dynamically build a query string:

    DECLARE @ItemCode NVARCHAR(30)
    DECLARE @Quantity NUMERIC(19, 6)
    DECLARE @QUERY AS NVARCHAR(MAX) = 'SELECT [all], [the], [other], [columns], '
    DECLARE crsr CURSOR
    FOR SELECT r.ItemCode, r.Quantity
        FROM [dbo].RDR1 r
        etc.
    OPEN crsr
    FETCH NEXT FROM crsr
    INTO @ItemCode, @Quantity
    WHILE @@FETCH_STATUS = 0
     BEGIN
      /*** Build the combination column one row at a time, something like this: ***/
      SET @QUERY = @QUERY + CAST(CAST(@Quantity AS INT) AS NVARCHAR) + ' x ' + @ItemCode + ', '
      FETCH NEXT FROM crsr
      INTO @ItemCode, @Quantity
     END
    CLOSE crsr
    DEALLOCATE crsr
    SET @QUERY = @QUERY + 
    ' FROM ORDER h
           INNER JOIN RDR1 r ON h.DocEntry = r.DocEntry 
      etc. '
    EXEC(@QUERY)
    

    which you would then execute with the EXEC('the query you built with the cursor goes here') function.

    Regards,

    Johan

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Nick,

      Looking back, I was overthinking this completely, and indeed it was not even returning the correct result. So back to the drawing board, and after simplifying the whole thing, please try this:

      SELECT T0.DocNum,T0.[CardCode], T0.[CardName]
      ,T1.FirstName
      ,T1.LastName
      ,T0.Address2,T1.Tel1,T1.Cellolar,T1.E_MailL,T2.SlpName,T9.DocDate
      ,stuff((select ', ' + cast(cast(r.Quantity as int) as nvarchar) + ' x ' + r.ItemCode
        from RDR1 r 
        where r.DocEntry = T0.DocEntry
          AND r.LineStatus = 'O' FOR XML PATH('')), 1, 2, '') AS [Items in order]
      FROM ORDR T0
      INNER JOIN OCPR T1 ON T0.[CntctCode] = T1.[CntctCode]
      INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
      INNER JOIN RDR1 T3 ON T3.DocEntry = T0.DocEntry
      LEFT OUTER JOIN QUT1 T8 ON T3.BaseEntry = T8.DocEntry AND T3.BaseLine = T8.LineNum
      LEFT JOIN OQUT T9 ON T9.DocEntry = T8.DocEntry
      WHERE T0.series = '73' AND T0.DocStatus = 'O'
      GROUP BY T0.DocEntry, T0.DocNum,T0.[CardCode], T0.[CardName]
      ,T1.FirstName
      ,T1.LastName
      ,T0.Address2,T1.Tel1,T1.Cellolar,T1.E_MailL,T2.SlpName,T9.DocDate

      Regards,

      Johan

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.