sql-servert-sqlwonderware

Getting result with unmatched records as NULL


I'm trying to query a table in wonderware for certain data. I know there is some data in the 'a' part of the query with TagName equal to 'Weightdata2.uiID'. But there is no matching data in the 'b' part and because of that the query returns empty dataset. But I would like to get the data for both the 'a' part and 'b' with NULL or zero in the column uiWater if there is no matching data there.

Here is my query:

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
set @StartDate = '2018-09-18 08:00:00.000'
set @EndDate = '2018-09-18 09:00:00.000'

SELECT a.Value as uiID, b.value as uiWater, cast(a.datetime as datetime2(0)) 
as dtCreated, 2 as Weightdata
FROM [INSQL].[Runtime].[dbo].[History] a
JOIN [INSQL].[Runtime].[dbo].[History] b ON a.datetime=b.datetime
WHERE a.TagName IN ('Weightdata2.uiID') and a.datetime>=@StartDate and 
a.datetime<=@EndDate and a.Value!=0
and b.TagName IN ('Weightdata2.uiWater') and b.datetime>=@StartDate and 
b.datetime<=@EndDate

I would like my result like that


Solution

  • This is more likely a job for PIVOT:

    ;with cteData as (
        SELECT t.datetime, t.TagName, t.value
        FROM [INSQL].[Runtime].[dbo].[History] t
        WHERE t.datetime>=@StartDate and t.datetime<=@EndDate
        AND t.TagName IN ('Weightdata2.uiID', 'Weightdata2.uiWater')
    )
    SELECT 
      d.dtCreated,
      NULLIF(p.[Weightdata2.uiID], 0) as uiID,
      p.[Weightdata2.uiWater] as uiWater
    FROM (
      SELECT 
        cast(d.datetime as datetime2(0)) as dtCreated,
        d.TagName, 
        d.value
      FROM cteData d
    ) d
    PIVOT (
      MAX(d.value) for d.TagName in ([Weightdata2.uiID], [Weightdata2.uiWater])
    ) p
    

    Which will return data in all cases: when there is uiID row but no uiWater, when both exist, when no uiID but uiWater is present.

    And is easily adjusted for longer tag list.