14 Replies Latest reply on Mar 4, 2016 11:42 AM by Rashmi

    Monthlyplanned sum values

    Rashmi

      I have data defined like below in AF.

       

      I have applied this logic to get the month number in stead of text.

       

       

      SELECT * FROM OPENQUERY([PIAF_DEMO],'

      SELECT ea.Name, i.Value,MONTH(''Mar'')

          FROM [EntegaDB].[Asset].[ElementHierarchy] eh

          INNER JOIN [EntegaDB].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID,

          [EntegaDB].[Data].[Archive] i

          WHERE eh.Name = ''Haiger''

          AND i.ElementAttributeID = ea.ID

          AND ea.Name =''monthlyplanned''

       

          OPTION (FORCE ORDER, EMBED ERRORS)')

       

      My requirement is if i want to get the sum value for March, it should give me the result for (jan value+ feb Value+march value).

      I was trying to get apply the logic like select sum(values) from table_name where month number between 1 and current month number, but did not work. somewhere I must be syntactically incorrect.

       

      Then I changed my idea stating :

       

        

      CASE WHEN MONTH(''feb'') = 2 THEN SUM(jan .value+fen.value) AS feb

       

      but I'm unsure about sql server syntax.

       

      Please can anyone help me how to apply the logic for my requirement?

        • Re: Monthlyplanned sum values
          Roger Palmen

          Best option i can think of is to build a query for each month (so 12 queries), UNION those together, and select the correct result from the 12 rows out of the union.

          The model in AF is not easy to handle, so that has it's effect on the query.

            • Re: Monthlyplanned sum values
              Rashmi

              Roger,

               

              my requirement is :

               

              Suppose I wants the value for month of May, then it should display me the combination of Jan value+fe value+march value and so till May.

               

              in AF, the months have been defined in text. hence I used the command i.Value,MONTH(''Mar'')  (for ex:) to display me in number. next to this I have to write the comamnd such that it should give me the result with summation of current month +previous months values.

              • Re: Monthlyplanned sum values
                Rashmi

                I reckon I have to use Case function, but I'm not getting the correct synta for summation

                 

                case WHEN MONTH(''feb'') = 2 THEN SUM(jan .value+fen.value) AS feb

                case WHEN MONTH(''mar'') = 3 THEN SUM(jan .value+fed.value + mar value) AS feb

                 

                if this logic is true then I'm still seraching for right syntax for summation

                  • Re: Monthlyplanned sum values
                    Roger Palmen

                    Still looking at a query. Having a TVF / Transpose function would help, but that would require to have a template for the Element.

                    But during my look at the query i thought, why not solve this in AF? Simply create a cumulative attribute for each month (except January....), where you add the value of the current month to the cumulative value of the previous month:

                     

                     

                    Then you can just query the cumulative value attributes. Much easier!

                     

                    PS: for summing values in SQL use "+" : BNF syntax here: https://livelibrary.osisoft.com/LiveLibrary/content/en/oledb-ent-v1/GUID-C45C60B1-0C93-4AD5-A09D-83F23AD2DA30

                    1 of 1 people found this helpful
                      • Re: Monthlyplanned sum values
                        Roger Palmen

                        And now the query below. Expand to 12 months, and use this in a FROM-clause to select the row with the month you require.

                         

                        SELECT 'Jan', s.ValueInt
                        FROM Sandbox.Asset.ElementHierarchy eh
                        INNER JOIN Sandbox.Asset.ElementAttribute ea ON (ea.ElementID = eh.ElementID)
                        INNER JOIN Sandbox.Data.Snapshot s ON (s.ElementAttributeID = ea.ID)
                        WHERE eh.Path = '\' AND eh.Name = 'GeneralSandbox'
                        AND ea.Path = '\SumChild\'
                        AND ea.Name = 'Jan'
                        UNION
                        SELECT 'Feb', sum(s.ValueInt)
                        FROM Sandbox.Asset.ElementHierarchy eh
                        INNER JOIN Sandbox.Asset.ElementAttribute ea ON (ea.ElementID = eh.ElementID)
                        INNER JOIN Sandbox.Data.Snapshot s ON (s.ElementAttributeID = ea.ID)
                        WHERE eh.Path = '\' AND eh.Name = 'GeneralSandbox'
                        AND ea.Path = '\SumChild\'
                        AND ea.Name IN ('Jan', 'Feb')
                        GROUP BY 1
                        UNION
                        SELECT 'Mar', sum(s.ValueInt)
                        FROM Sandbox.Asset.ElementHierarchy eh
                        INNER JOIN Sandbox.Asset.ElementAttribute ea ON (ea.ElementID = eh.ElementID)
                        INNER JOIN Sandbox.Data.Snapshot s ON (s.ElementAttributeID = ea.ID)
                        WHERE eh.Path = '\' AND eh.Name = 'GeneralSandbox'
                        AND ea.Path = '\SumChild\'
                        AND ea.Name IN ('Jan', 'Feb','Mar')
                        GROUP BY 1
                        
                  • Re: Monthlyplanned sum values
                    kduffy

                    Hi Rashmi,

                     

                    This might be a little too messy for you, but I have it working on my machine. I made another table on my SQL server that converts 3 letter month names to month numbers, joins this table to the openquery table, filters it with a where clause for where the month number is less or equal to the month of interest, and then finally sums the resulting output.

                     

                    First, I made a table on my SQL server that is being used to house the linked server object. It's simple enough, just pairs the 3 letter month (4 letters for sept) with the month number. I also put your AF data into an element in my AF database.

                    made months tables.png    af data.png

                    Then I used the openquery that you had earlier to prove I had the data coming into SSMS:

                    query for all months.png

                    But then to join this output with the initial month number table I made, I had to use a commonTableExpression to get it to join with the table I have above. Messy, but it works.

                     

                    ytd Total.png

                    Here I have it summing Jan, Feb, Mar, and Apr.

                     

                    Let me know if you're able to get this working, it's the only way I could think of that doesn't require 11 more attributes to be created that are running totals for each month.

                     

                    Kelsey

                      • Re: Monthlyplanned sum values
                        kduffy

                        Actually, I think I found a much much easier way to do this, but it would depend on whether this is something you can implement in your AF database.

                         

                        I first made a table that contains three columns. The month name, the month number, and the value. You can just do data entry into this table (or pull linked table data if needed). This also depends on how you're currently getting data into these attributes. All I can tell from the screenshot is that they're not PI tags. Are these manual entry?

                        table location.pngAF month table.png

                        Then, you can make 12 table lookup data reference child attributes that each look for their specific row. You can either have it configured to look for the month name matching that attribute name, or just hard code the month number in the query (which is what I did).

                        table lookup DR.png

                        Then you have two more attributes: the summation and the "current month" pointer.

                        all attributes.png

                        The way you build the summation attribute is a table lookup on that original table that has all the data, and then find where monthnumber <= current month number attribute value.

                        final sum DR.png

                        You can see it works simply by changing the month number attribute, checking in the change, then refreshing the element:

                        Jan.pngapr.pngOct.png

                         

                        This method is really clean, but it may or may not be an option for you based on how you pull in the data.

                         

                        Kelsey

                        3 of 3 people found this helpful
                          • Re: Monthlyplanned sum values
                            Rashmi

                            Hello Kelsey,

                             

                            Many thanks for all your help.

                             

                            I found little complex with your first suggestion and 2nd method is quite straight forward though.

                             

                            Our objective is to get write a sql query only and get the result on management studio. The intention of writing the query is same query should work on cogent (front end) tool where end users can see the data in graphical format. Hence, I was asked to writ eth sql query only.

                             

                            However I can get the data for one month by using the below query, but unabe to sum it with next month.

                             

                            SELECT * FROM OPENQUERY([PIAF_DEMO],'

                            SELECT ea.Name, i.Value

                                FROM [EntegaDB].[Asset].[ElementHierarchy] eh

                                INNER JOIN [EntegaDB].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID,

                                [EntegaDB].[Data].[Archive] i

                                WHERE eh.Name = ''Haiger''

                                AND i.ElementAttributeID = ea.ID

                                AND ea.Name = ''jan''

                            OPTION (FORCE ORDER, EMBED ERRORS)')

                             

                            Also, I applied Roger's above query and can see NULL for jan,feb and March.

                            • Re: Monthlyplanned sum values
                              Roger Palmen

                              I agree solving the problem mainly in the AFmodel is the easiest way out. But we need input from Rashmi Das if that is viable. Just doing the sum calculations in AF, use an Element Template and a Transpose function, and voila, just one simple select statement requried.

                                • Re: Monthlyplanned sum values
                                  Rashmi

                                  Hello Roger,

                                   

                                  I execute the queries on management studio window only. If I would have used PI SQL commander window, it would have easy for me to create a template and use that in query.

                                   

                                  In mgmt studio, I created a linked server and running the query. I'm not sure whether any method how to create the AF template in management studio window.

                                  • Re: Monthlyplanned sum values
                                    Rashmi

                                    Also I was asked to write the sql query in such that that would give us the required output so that the same query can be used on cogent tool and users can see the data in graphical format .

                                  • Re: Monthlyplanned sum values
                                    Rashmi

                                    Kelsey,

                                     

                                    I modified the query like this

                                     

                                    SELECT * FROM OPENQUERY([PIAF_DEMO],'

                                    SELECT ea.Name, i.Value, MONTH(ea.Name) MonthNumber,

                                    SUM(i.Value) OVER (ORDER BY MONTH(ea.Name) ROWS UNBOUNDED PRECEDING) RunningTotal

                                        FROM [EntegaDB].[Asset].[ElementHierarchy] eh

                                        INNER JOIN [EntegaDB].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID,

                                        [EntegaDB].[Data].[Archive] i

                                        WHERE eh.Name = ''Haiger''

                                        AND i.ElementAttributeID = ea.ID

                                       AND ea.Name IN (''Jan'', ''Feb'', ''mar'', ''apr'',''may'',''jun'')

                                      AND MONTH(ea.Name) < Month(''jul'')

                                      OPTION (FORCE ORDER, EMBED ERRORS)')

                                     

                                    The line SUM(i.Value) OVER (ORDER BY MONTH(ea.Name) ROWS UNBOUNDED PRECEDING) RunningTotal should fetch me current and previous months values, unsure where is it syntactically wrong.

                                     

                                    The error is:

                                    [Line 3:19] Missing <IDENTIFIER> at '('".  Any advise?

                                      • Re: Monthlyplanned sum values
                                        Roger Palmen

                                        The SUM OVER functionality is only supported by SQLserver. So you would need to split the query into first getting the data from PI (e.g. using your OPENQUERY), and then instead of * try to implement the SUM OVER functionality.

                                        1 of 1 people found this helpful
                                          • Re: Monthlyplanned sum values
                                            Rashmi

                                            Many Thanks Roger.

                                             

                                            The query worked perfectly fine.

                                             

                                             

                                            Select H.Mon, H.tagval,H.MonthNumber,Sum(Cast(h.tagval as Float)) over (Order By H.MonthNumber Rows Unbounded preceding) Runningttl From

                                            (SELECT * FROM OPENQUERY([PIAF_DEMO],'

                                            SELECT ea.Name as Mon, i.Value as Tagval, MONTH(left(ea.Name,3)) as MonthNumber

                                            FROM [EntegaDB].[Asset].[ElementHierarchy] eh

                                            INNER JOIN [EntegaDB].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID,

                                            [EntegaDB].[Data].[Archive] i

                                            WHERE eh.Name = ''Haiger''

                                            AND i.ElementAttributeID = ea.ID

                                            AND ea.Name IN (''jan'', ''feb'', ''mar'', ''apr'',''may'',''jun'',''jul'',''aug'',''sept'',''oct'',''nov'',''dec'')

                                            AND MONTH(left(ea.Name,3)) < Month(''nov'')

                                            OPTION (FORCE ORDER, EMBED ERRORS)') ) H