Suggestion required for Named Queries and Perspective Property Bindings

Sudhanthiralakshmi_M · September 4, 2023, 6:18am

There is a consumption Mismatch in Energy Consumption Tab. Please refer the Below mentioned Queries and give suggestions inorder to get the same values in all charts.

1.Query Binded for Total Energy :

select sum(Diff) from(  SELECT  top(30) cast(Date as date) as time, SUM(convert(float,KWH)) as kwh,min(convert(float,KWH)) as Minimum,max(convert(float,KWH)) as Maximum,(max(convert(float,KWH))-min(convert(float,KWH))) as Diff
from Main where  BG =:bg and Plant =:pl and  Line =:ll  and Machine =:ma and Date between :st and :sp and KWH !='0'
GROUP BY cast(Date as date)
order by Diff desc) as d

Property Binding for Total Energy:

[
  {
    "type": "ia.display.label",
    "version": 0,
    "props": {
      "style": {
        "fontSize": 18,
        "fontWeight": "bold",
        "textAlign": "center"
      }
    },
    "meta": {
      "name": "Total Energy Value"
    },
    "position": {
      "x": 297.123,
      "y": 89.553,
      "rotate": {
        "anchor": "75% 50%"
      },
      "height": 32,
      "width": 58
    },
    "custom": {},
    "propConfig": {
      "props.text": {
        "binding": {
          "type": "query",
          "config": {
            "returnFormat": "scalar",
            "queryPath": "SumMonth",
            "parameters": {
              "ll": "{.../Line_Dropdown.props.value}",
              "st": "{.../DateTimeInput.props.value}",
              "ma": "{.../Machine_Dropdown.props.value}",
              "bg": "{.../BG_Dropdown.props.value}",
              "pl": "{.../Plant_Dropdown.props.value}",
              "sp": "dateArithmetic({.../DateTimeInput.props.value}, 1, \"days\")"
            }
          },
          "transforms": [
            {
              "formatType": "numeric",
              "formatValue": "####.0",
              "type": "format"
            }
          ]
        }
      }
    }
  }
]

Query Binded For Energy Gauge

Select   (max(convert(float,KWH))-min(convert(float,KWH))) as Diff from  Main WHERE  BG =:bg and Plant =:pl and Line=:ll 
and Machine = :ma and Mod_Date between :st and :sp

Property Binding for Energy Gauge:

[
  {
    "type": "ia.chart.simple-gauge",
    "version": 0,
    "props": {
      "minValue": 1000,
      "maxValue": 35000,
      "startAngle": -90,
      "arc": {
        "color": "#808000"
      },
      "arcBackground": {
        "color": "#D9D900"
      },
      "label": {
        "visible": false
      },
      "animate": true,
      "style": {
        "classes": []
      }
    },
    "meta": {
      "name": "SimpleGauge"
    },
    "position": {
      "basis": "300px"
    },
    "custom": {},
    "propConfig": {
      "props.value": {
        "binding": {
          "type": "query",
          "config": {
            "returnFormat": "scalar",
            "queryPath": "Online_KWH",
            "parameters": {
              "ll": "{..../Line_Dropdown.props.value}",
              "st": "{..../DateTimeInput.props.value}",
              "ma": "{..../Machine_Dropdown.props.value}",
              "bg": "{view.params.BG}",
              "pl": "{view.params.Plant}",
              "sp": "dateArithmetic({..../DateTimeInput.props.value},18,\"hours\")"
            }
          }
        }
      }
    }
  }
]

kindly suggest the required changes in the queries inorder to get the values in Total Energy as like Energy gauge

pascal.fragnoud · September 4, 2023, 8:27am

The whole thing looks... weird.
That total energy query makes little sense from what I understand, but since I'm not very good at SQL I won't take chances and leave that to someone else.

One thing I know for sure though, is that when you need to convert and cast everything in a query, there's something wrong with how your table is set up.

Can you share a bit more about what you expect things to be ?
Maybe share and sample from your table and what you need to get from it ?

Transistor · September 4, 2023, 9:28am

For anyone trying to interpret the SQL query ...

SELECT 
  SUM(Diff) FROM 
  (
    SELECT TOP(30) CAST(Date AS DATE) AS Time
    , SUM(CONVERT(FLOAT,KWH)) AS kWh
    , MIN(CONVERT(FLOAT,KWH)) AS Minimum
    , MAX(CONVERT(FLOAT,KWH)) AS Maximum
    , (MAX(CONVERT(FLOAT,KWH)) - MIN(CONVERT(FLOAT,KWH))) AS Diff
  FROM Main 
  WHERE BG =:bg 
    AND Plant =:pl 
    AND  Line =:ll  
    AND Machine =:ma 
    AND Date BETWEEN :st AND :sp 
    AND KWH != '0'
  GROUP BY CAST(Date AS DATE)
  ORDER BY Diff DESC
) AS d

As @pascal.fragnoud points out it appears that your database column types are not correct as you are having to convert them. For examples,

Why is KWH using a string comparison?
Why is your inner select returning four columns when you only need Diff?

Other comments: There is nothing obvious on the screengrab to tell me over what period the readings are valid. An hour? A day? A week? A month?

The big problem is that your query only returns one value, SUM(Diff).and I suspect that you are expecting four.

Transistor · September 4, 2023, 9:48am

Similar clean-up of energy gauge query:

SELECT 
  (
    MAX(CONVERT(FLOAT,KWH)) - MIN(CONVERT(FLOAT,KWH))
  ) AS Diff
FROM  Main 
WHERE BG = :bg 
  AND Plant = :pl 
  AND Line = :ll 
  AND Machine =  :ma 
  AND Mod_Date BETWEEN :st AND :sp

I recommend always adding a space between operators and variables for legibility.
You can use upper or lowercase for SQL keywords but be consistent!

Sudhanthiralakshmi_M · September 4, 2023, 10:17am

I have only one SQL table called Main where I update all my tag values in that we have 2 columns w.r.t Date
column 1 - Date has the records as per the calendar date and time.
column 2 - since I need to Update the records from 12:00AM to 6:00AM with same as previous date i gave one expression and update the record into Mod_Date

Sudhanthiralakshmi_M · September 4, 2023, 10:19am

I was expecting my total energy value as similar to gauge value, but i dont where i need to modify the query

Sudhanthiralakshmi_M · September 4, 2023, 10:22am

Why is KWH using a string comparison? As my kWh column has Exponential value i will convert that into float and display the value for easy interpretation
Why is your inner select returning four columns when you only need Diff? since all parameters has individual kWh value it need to select the record based on the four parameters

Sudhanthiralakshmi_M · September 4, 2023, 10:24am

There is nothing obvious on the screengrab to tell me over what period the readings are valid. An hour? A day? A week? A month? I was expecting my value for A day

Transistor · September 4, 2023, 11:38am

As my kWh column has Exponential value i will convert that into float and display the value for easy interpretation

Well then the line
AND KWH != '0'
will always return TRUE as you're comparing a number type with a string. Maybe you mean
AND KWH != 0

Since all parameters has individual kWh value it need to select the record based on the four parameters.

But you only read the Diff column. That means that this query should return the same result. (I've removed the casting of the Date column (although I suspect that it may be being saved as a string and that's not a good practice).

SELECT 
  SUM(Diff) FROM 
  (
    SELECT TOP(30) Date
    , (MAX(CONVERT(FLOAT,KWH)) - MIN(CONVERT(FLOAT,KWH))) AS Diff
    FROM Main 
    WHERE BG =:bg 
      AND Plant =:pl 
      AND Line =:ll  
      AND Machine =:ma 
      AND Date BETWEEN :st AND :sp 
      AND KWH != '0'
    GROUP BY CAST(Date AS DATE)
    ORDER BY Diff DESC
) AS d

I was expecting my value for a day.

Well then make it clear on the readout. Units should be "kWh/day".

Tip: to quote someone else's text highlight it and a "Quote button will appear. Alternatively, type a > at the start of the line.

Transistor · September 4, 2023, 1:29pm

I suggest a slightly different approach.

Create a named query with all the WHERE parameters. Get it to return the four summary values that you are looking for. It would look something like this:

SELECT 
    TOP(30) Date
    , SUM(CONVERT(FLOAT,KWH)) AS kWh
    , MIN(CONVERT(FLOAT,KWH)) AS Minimum
    , MAX(CONVERT(FLOAT,KWH)) AS Maximum
    , (MAX(CONVERT(FLOAT,KWH)) - MIN(CONVERT(FLOAT,KWH))) AS Diff
  FROM Main 
  WHERE BG =:bg 
    AND Plant =:pl 
    AND Line =:ll  
    AND Machine =:ma 
    AND Date BETWEEN :st AND :sp 
    AND KWH != 0
  GROUP BY Date
  ORDER BY Diff DESC

Check that this query returns only one row and five columns.
The idea here is that we will make only one trip to the database to retrieve all values.

Create an expression binding on each of the readouts and use that to extract the value from the column of interest. e.g.,
{path-to-custom-property-dataset}[2]
The Simple Gauge can use the same dataset to extract the value it requires.

pascal.fragnoud · September 4, 2023, 1:47pm

OCD checking in.

Transistor · September 4, 2023, 1:54pm

The space before Line? Fixed. In my defense, it was in the original post.

pascal.fragnoud · September 4, 2023, 1:56pm

Everything else was so neatly aligned, this one space was really standing out.
Which is clearly not the case in the original query :X

mdemaris · September 4, 2023, 9:55pm

I also noticed that you don't have anything in the query that refers to the selected shift, unless you are using Dates to select a shift.

Once you get the Diff SQL solved, you can use the MIN and MAX functions to provide your "Low" and "Peak" consumption, as well as AVG for the "Average", using the same WHERE, GROUP BY and ORDER BY clauses.

And, I would label the result of the SUM function something like "Total_kWh" to help reduce confusion with anything else named "KWH"/"kWh".

My two cents.

Sudhanthiralakshmi_M · September 5, 2023, 5:31am

i dont have any separate column Diff in SQL table, the only column is Kwh from there it will take the data and compute max , min and diff by ordering and grouping

mdemaris · September 5, 2023, 3:53pm

Correct, Diff, Min, Max and Average are calculated "fields" in the SQL statement. What I'm saying is that instead of naming your column "Diff" or "KWH" in the select statement, name it something more descriptive.