Historian retrieve data

Question

I have an Excel worksheet that I have the data pulled from a Historian database for emission tags in a Wonderware server. I need to know how i can write the SQL query that uploads data between two date ranges.starte date end date.

I have collected the data and display I want, I just dont know how I can implement between two date ranges the user types in on another form. I DONT KNOW HOW TO DO THIS.

I have tried nothing because im not sure how to implement a parameter query with excel.

SET QUOTED_IDENTIFIER OFF
SELECT * FROM OPENQUERY(INSQL, "SELECT DateTime, [AB01_PE01], [NU_DC01_DX01_DISPLAY],
 [NU_DC01_DX02_DISPLAY], [NU_DC02_DX01_DISPLAY], [NU_DC02_DX02_DISPLAY], 
 [NU_DC03_DX01_DISPLAY], [NU_DC03_DX02_DISPLAY], [NU_DC05_DX01_DISPLAY]

    FROM WideHistory
    WHERE wwRetrievalMode = 'Cyclic'
     AND wwCycleCount = 14
     AND wwVersion = 'Latest'
     AND DateTime >= '20190501 06:00:00.000'
     AND DateTime <= GetDate()")

what I want is where Datetime >= is I need to set up where it ask the user the start date and end date? I do not know how to do this in excel I am new to the historian add in.

I'm confused what you're trying to achieve here. Please clarify this first: 1. Are you trying to query the data or insert the data somewhere (you wrote "query that uploads")? 2. Are you trying to query the historian DB or the Excel with the data already exported from Historian? Also, are you aware you can query Historian directly with an SQL client, without Excel? — PiotrS, Jun 22 '19 at 21:39
Im trying to get data from the Historian into excel but i need it user friendly not all the user know how to use historian so i want to creat a excel sheet where the user clicks on a button it loads the data from the server on the worksheet based on the date range the user puts in on start date and end date. I am not aware that i query the data without excel but the department manager wants the data inputted in excel. — Jeannie, Jun 27 '19 at 20:00

PiotrS · Answer 1 · 2019-06-29T21:11:24.927

0

Based on your comment response, I understand your real question is: how to parameterize SQL query in Excel. This video shows step by step guide to do that: https://youtu.be/xPalEw4xw1w?t=129

edited Jun 29 '19 at 21:11

answered Jun 28 '19 at 13:03

PiotrS

180
3
16

That will work if i am running from SQL server but I am getting the data from a Wonderware Historian Connection with the Historian add-in in excel so how can I do the exact same thing with the Historian Add-in? – Jeannie Jul 01 '19 at 18:21
@Jeannie: But Wonderware Historian runs on SQL server. You can write SQL queries and execute them against the historian database to retrieve historical data. There are limitations on query structure and the historian services need to be running. The inner query in your original post (starting from "SELECT DateTime") could be executed from a compatible SQL client against the historian database to retrieve historical data. – PiotrS Jul 02 '19 at 17:52
can you show me an example like I said Im new to this – Jeannie Jul 03 '19 at 20:00

Historian retrieve data

1 Answers1