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
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.