Query based on date

Sort:
You are not authorized to post a reply.
Author
Messages
JonA
Veteran Member
Posts: 1162
Veteran Member
    Can I create a query that will pull data based on a date calculation? I want to pull monthly purchase order information but only for 1 year back from the current date. So I can schedule the report to run at the end of each month and not have to change the date range every time. Something like <current date> - 365.
    Jon Athey - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
    FireGeek21
    Veteran Member
    Posts: 84
    Veteran Member
      In the Report -> Selection Formulas type:

      {PurchaseOrderTable.PurchaseOrderDate} in CurrentDate-365 to CurrentDate
      Arvin Ojales
      Advanced Member
      Posts: 24
      Advanced Member
        Are you using ODBC connection? If using ODBC, you can add something like this to the WHERE clause of your select statement:

        WHERE PO_DATE > SYSDATE - 365.

        Arvin Ojales
        Char
        Veteran Member
        Posts: 62
        Veteran Member
          Depending on how you run your reports (if you use LBI or Enterprise for example where there are historical instances) CurrentDate and DataDate can produce very different results. I'd generally recommend using datadate and the record selection would be {PurchaseOrderTable.PurchaseOrderDate} in Date(year(datadate)-1, month(datadate), day(datadate)) to DataDate - this will also assure correct results for leap year
          FireGeek21
          Veteran Member
          Posts: 84
          Veteran Member
            Char,

            Please explain the difference between CurrentDate and DataDate.

            Thank you!
            Char
            Veteran Member
            Posts: 62
            Veteran Member
              DataDate is the date the query executed against the database whereas CurrentDate is the date you're looking at the report. Let's say for Example that you have a formula that says "if {table.field} = currentdate -1 then {table.amount}" and you run the report on Monday thereby creating a historical instance. If you go and look at that historical instance on Friday then the formula will try to display data for Thursday since it's the day before Friday and there will be none since you ran the report on Monday and you'll see 0. If the formula had used DataDate then the numbers stay consistent over time because you're always looking at that fixed point in time that is the date the query executed. This same type of issue can arise when you use certain functions such as LastFullMonth because they are based internally on the currentdate rather than than the datadate. I hope that's clear......
              FireGeek21
              Veteran Member
              Posts: 84
              Veteran Member
                THANK YOU CHAR!!!

                Very clear explanation. Guess there are some reports I need to rewrite!
                JonA
                Veteran Member
                Posts: 1162
                Veteran Member
                  I'm connecting via OLE DB. It looks like the solution is found after the query, within the report itself, letting Crystal do the work?
                  Jon Athey - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
                  JonA
                  Veteran Member
                  Posts: 1162
                  Veteran Member
                    Thanks Char! Your formula worked beautifully!
                    Jon Athey - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
                    You are not authorized to post a reply.