Skip to main content
InSource Solutions

TN Hist255 How to perform a simply left join of two values from a SQL Database such as the Wonderware Historian

  1. Last updated
  2. Save as PDF

InSource_Logo_Transparent.png

Description

This article from InSource shows how to create a simple left join to show two values in the same query result.

  • Author: Joseph Hefner
  • Published: 06/20/2016
  • Applies to: SQL Server 2005 or later

Details

First identify the queries that you wish to join and give them a qualifying name in to perform the join.  In this example, we will perform a join on the 2 queries below:

 

Query 1:  (Returns a Value for Tag1 at a provided start date and end date)

SELECT a2.DateTime , a2.vValue as "Value1" From ( SELECT * FROM History WHERE History.TagName IN ('Tag1') AND vValue is not null AND len(vValue) > 0 AND wwRetrievalMode = 'Full'
AND wwVersion = 'Latest'
AND DateTime >= @StartDate
AND DateTime <= @EndDate) a2)

 

Query 2 : (Returns a Value for Tag2 at a provided start date and end date)

select a1.DateTime , a1.vValue as "Value2" From ( SELECT * FROM History WHERE History.TagName IN ('Tag2') AND vValue is not null AND len(vValue) > 0 AND wwRetrievalMode = 'Full'
AND wwVersion = 'Latest'
AND DateTime >= @StartDate
AND DateTime <= @EndDate) a1)

 

To join the two simply give each a qualifying name and tell them the column you wish to join on .  In This example, we will peform a join on DateTime:

Joined Query:

Select Q1.Datetime,Q2.Value2,Q1.Value1
from

(SELECT a2.DateTime , a2.vValue as "Value1" From ( SELECT * FROM History WHERE History.TagName IN ('Tag1') AND vValue is not null AND len(vValue) > 0 AND wwRetrievalMode = 'Full'
AND wwVersion = 'Latest'
AND DateTime >= @StartDate
AND DateTime <= @EndDate) a2) Q1

LEFT JOIN

(select a1.DateTime , a1.vValue as "Value2" From ( SELECT * FROM History WHERE History.TagName IN ('Tag2') AND vValue is not null AND len(vValue) > 0 AND wwRetrievalMode = 'Full'
AND wwVersion = 'Latest'
AND DateTime >= @StartDate
AND DateTime <= @EndDate) a1) Q2

on Q1.DateTime = Q2.DateTime

 

The final solution should look similar to the screenshot below:SS1.JPG

The output will give you a joined table as below:

SS3.JPG