12 Replies Latest reply on Oct 28, 2018 5:17 AM by Galt

    mod(today()-3/31/2014,28)+1

    Galt

      The above formula is an excel formula to calculate the position in a 28 day rotation.  I need to use this information in a odbc query in order to select the correct record.  Long story short I don't know how to put together the equivalent formula together to use in the query.  Any help would be appreciated.

        • Re: mod(today()-3/31/2014,28)+1
          Galt

          tougher problem than I thought I take it.

            • Re: mod(today()-3/31/2014,28)+1
              Dan Fishman

              In T-SQL you could do something similar to: DATEDIFF (day,DATEFROMPARTS(2014,3, 31),  CONVERT (date, GETDATE())) % 28 + 1

               

              If you want to use PE tags, you could always try using a PE expression such  as (Int('t'-ParseTime("3/31/2014"))/86400) Mod 28 +1

              1 of 1 people found this helpful
                • Re: mod(today()-3/31/2014,28)+1
                  Galt

                  Thanks for the answer.   I'll give them both a try because quite honestly I don't know how the query is being build language wise.  I thought at first that it was sql but commands like mod and datediff don't seem to be recognized when I use them.  I do see that you used components the get the start date and devided it by seconds.  I didn't try either of those. 

                   

                  Right now I'm thinking I need to find some reference materials to read up.  My company uses Processbook 2012.

              • Re: mod(today()-3/31/2014,28)+1
                kduffy

                Is your ODBC query using PI OLEDB Provider or PI OLEDB Enterprise?

                 

                And what client are you using to execute the ODBC query? Would it be possible for that client to calculation the day number and parse it into the query?

                 

                Kelsey

                  • Re: mod(today()-3/31/2014,28)+1
                    Galt

                    I honestly don't know the answer to that.  I'm relatively new to Pi Processbook and this is my first time even trying to work an ODBC.

                      • Re: mod(today()-3/31/2014,28)+1
                        John Messinger

                        So the answer we can now provide at this point is actually, "It depends". It depends on what the source database is that you are trying to query from ProcessBook via ODBC, and what ODBC driver you are using to access that data source. The answer that Dan Fishman provided was based on using T-SQL for Microsoft SQL Server. While there is a standard for SQL, there are also many database vendor specific extensions. Knowing what the data source is, and what ODBC driver (and version) will get you a fair distance in being able to determine what SQL query functions will produce the result you are looking for, largely because you can determine how much standard SQL and how much vendor specific extensions you can use in constructing your query.

                        2 of 2 people found this helpful
                        • Re: mod(today()-3/31/2014,28)+1
                          kduffy

                          Sorry James, I didn't realize you were using an ODBC dataset in ProcessBook. I thought you were using the PI ODBC Driver, which exposes the PI System as an ODBC data source. The PI ODBC Driver queries are parsed by either the PI OLEDB Provider or PI OLEDB Enterprise query engines on the backend, hence my question, but this does not apply to you.

                           

                          Ignore my previous question; John Messinger's on the right track.

                           

                          Kelsey