Getting result with unmatched records as NULL

Question

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

Answer 1

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.

Answer 2

May be simply like this:

with Perimeter as (
    SELECT t.datetime, t.TagName, t.value
    FROM [INSQL].[Runtime].[dbo].[History] t
    WHERE t.datetime between @StartDate and @EndDate
    AND t.TagName IN ('Weightdata2.uiID', 'Weightdata2.uiWater')
)
select f1.Value as uiID, ISNULL(f2.value, 0) as uiWater, 
cast(f1.datetime as datetime2(0)) as dtCreated, 2 as Weightdata
from Perimeter f1 
left outer join Perimeter f2 on f1.datetime=f2.datetime and f2.TagName='Weightdata2.uiWater'
where f1.TagName='Weightdata2.uiID'

Answer 3

You just have to replace the JOIN with a LEFT JOIN. And you can use isnull to return 0 if the value is null

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, ISNULL(b.value, 0) as uiWater, cast(a.datetime as datetime2(0)) 
as dtCreated, 2 as Weightdata
FROM [INSQL].[Runtime].[dbo].[History] a
LEFT 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) OR b.datetime is null)