Skip to Content

Query to view all Open Sales Orders on Past Dates

Can a query be made that would show all Open Orders and Rows based on a given/searchable date? For example, I want to see a list of all Open Sales Orders on Jan 1, 2020.

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Aug 04, 2020 at 11:49 PM


    Try this query,

    SELECT T1.[DocEntry], T0.[DocNum], T0.[DocDate], T0.[CardName], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity] FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocStatus] = 'O' and T0.[DocDate] between [%0] and [%1]



    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 06, 2020 at 07:08 PM

    This seems to meet my requirements:

    SELECT T0.[DocNum], T0.[DocDate], T0.[UpdateDate] as [Closed Date], T0.[DocTotal] FROM ORDR T0 WHERE T0.[DocStatus] ='c' and T0.[DocDate] <=[%0] and T0.[UpdateDate] >[%0]

    Add a comment
    10|10000 characters needed characters exceeded

    • Your query have 2 issues:
      1. your query shows only Sales Order with Document Status 'Closed' only. NOT 'OPEN" as your original question.
      2. 'UpdateDate' is not 'ClosedDate'. You can close the Order on 1 Jan 2020, user then update the order Remark on 2 Jan 2020. Your query will show you 'ClosedDate' as 2 Jan 2020.

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.