Skip to Content

How to write a query which looks reports back the order status each day

I have a requirement to write a query which will run on a scheduled basis late at night and for it to export out to a txt file.

Specifically - I need the query to look at all orders which have been updated during the day and provide an updated order status.

Right now I have this query in place:

Declare @Date1 Date Set @Date1 = GETDATE()

SET NOCOUNT ON

SELECT T0.[DocNum], convert(varchar(10), T0.[DocDate], 103) as 'Doc Date', T0.[CardCode], T0.[CardName], T0.[DocStatus] FROM ORDR T0

WHERE T0.[UpdateDate]=@date1

ORDER BY T0.[DocNum]

This query as I've discovered is flawed and the ORDR UpdateDate doesn't log anything when an order is converted into an invoice.

I need to somehow amend this query to include the order statuses for all orders affected in a given day.

Any help would be appreciated!

Cheers

Rick

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Jul 26, 2020 at 08:10 AM

    Hi,

    There is no update when SO is converted to Invoice in sales order table.Alternatively, we can link to AR Invoice to get posting date becomes sales order closing date.

    Try this query,

    Declare @Date1 Date Set @Date1 = GETDATE() SELECT T0.[DocNum],T0.Docdate,T0.CardName, T0.DocStatus, T3.Docdate as 'Closing Date' FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] LEFT JOIN INV1 T2 ON T2.[BaseEntry] = T1.[DocEntry] AND T2.[BaseLine] = T1.[LineNum] AND T2.[BaseType] = 17 INNER JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry] WHERE T3.[DocDate] = @date1 GROUP BY T0.[DocNum],T0.Docdate,T0.CardName, T0.DocStatus,T3.Docdate

    Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Nagarajan,

      Thanks - but this doesn't pick up the new orders that have been created today...

      When I run it for today - nothing shows - but when I look at what orders were created today separately - there are 5 orders.

      I expect to see these listed all as open...

      So - rather than just showing sales orders relating to invoices - it needs to show new and/or manually closed orders which don't relate to an invoice yet.

      Regards

      Rick

  • Posted on Jul 26, 2020 at 11:34 AM

    Hi,

    Try this query,

    Declare @Date1 Date Set @Date1 = GETDATE() SELECT T0.[DocNum],T0.Docdate,T0.CardName, T0.DocStatus, T3.Docdate as 'Closing Date' FROM ORDR T0 LEFT JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] LEFT JOIN INV1 T2 ON T2.[BaseEntry] = T1.[DocEntry] AND T2.[BaseLine] = T1.[LineNum] AND T2.[BaseType] = 17 LEFT JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry] WHERE T0.[DocDate] = @date1 GROUP BY T0.[DocNum],T0.Docdate,T0.CardName, T0.DocStatus,T3.Docdate

    Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

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.