The external data source import utility allows you to import data from any OLEDB compliant database (Access, MS SQL, Oracle...). In variable edit, you can setup an SQL Query to pull data from a database and place the result in the WIMS Variable. After you have setup the data source connection and the SQL Query, use the Import, from External Data Source in Utilities (see External Data Source Import) to get the data.
FIELDS:
External Source: The name given to the External Sources connection that is setup in External Data Source Setup.
SQL Query: The SQL statement that returns a value that will be stored in the WIMS database. The import utility will import the value in the first column/first row of the result set. To use dates in the query, WIMS provides two constants that represent the Start and End Date/Time of the Variable Slot for which you are retrieving data. See setting dates of queries below.
Data Approval: The data approval level that the data imported is set to.
Using Dates in query:
WIMS provides two constants that represent the Start and End Date/Time of the Variable Slot for which you are retrieving data. #SD# is located using the 
button and represents the Start Date Time of the Data Slot for the variable. #ED# is located using the 
button and represents the End Date Time of the Data Slot for the variable. You can also add or subtract from the times using the syntax #SD+-nI# where n represents the number of I (intervals) to add to the Start Date or End Date.
For a Daily Variable getting data for Feb 18, 2016:
| #SD# |
2/18/2016 00:00:00 |
| #ED# |
2/18/2016 23:59:59 |
| #SD-7D# |
2/11/2016 00:00:00 (subtract 7 days) |
| #SD+8H# |
2/18/2016 08:00:00 |
| #ED+8H# |
2/19/2016 07:59:59 |
| #SDBOM# |
2/1/2016 00:00:00 (BOM - beginning of month) |
| #SDEOM# |
3/1/2016 00:00:00 (EOM - End of Month, use less than this date to get all data in the month) |
For an Hourly Variable getting data for Feb 18, 2016 8AM:
| #SD# |
2/18/2016 08:00:00 |
| #ED# |
2/18/2016 08:59:59 |
| #SD-1H# |
2/18/2016 07:00:00 (subtract 1 hour) |
| #SD+15N# |
2/18/2016 08:15:00 (N sets the interval to minutes) |
| #ED+1S# |
2/18/2016 08:00:00 |
| #SDBOM# |
2/1/2016 00:00:00 (BOM - beginning of month) |
| #SDEOM# |
3/1/2016 00:00:00 (EOM - End of Month, use less than this date to get all data in the month) |
| #SDEOM+7H# |
3/1/2016 07:00:00 |
Supported Intervals:
| Interval |
Description |
| Y |
Day of Year |
| Q |
Quarter |
| M |
Month |
| D |
Day |
| W |
Weekday |
| H |
Hour |
| N |
Minute |
| S |
Second |
SEE ALSO: Setup and Use the Import from External Data Sources Feature to Import Data From a Microsoft SQL Server database
EXAMPLES:
1. Get the number of labor hours for a day from Job Plus:
Select SUM(Hours) from WOLABOR where Date >= #SD# and Date <= #ED#
When data is imported for October 15, 2006 the query becomes:
Select SUM(Hours) from WOLABOR where Date >= #10/15/2006 00:00:00# and Date <= #10/15/2006 23:59:59#
2. Get the number of samples received from Lab Cal
SELECT COUNT(*) FROM LC_SAMPLE WHERE LC_SAMPLE.SAMPLEDTTIME >= '2006-12-31 00:00:00' and LC_SAMPLE.SAMPLEDTTIME <= '2006-12-31 23:59:59'
Note: The query used the MSSQL date format (i.e. yyyy-mm-dd hh:mm:ss) because the WIMS External Source Setup specifies that the date format as MSSQL.
HINT: Use the Test button to run the query for the current day and see results.
3. Get the Standard Deviation from an Hourly WIMS variable from 8AM to 8AM of the next day
SELECT STDEV(CURVALUE) FROM DATADDH WHERE DATESTAMP >=#SD+8H# AND DATESTAMP <= #ED+8H# AND VARID = 93