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

I would like my result like that

Robert Sandu · Answer 1 · 2018-09-29T13:49:45.720

0

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)

edited Sep 29 '18 at 13:49

answered Sep 29 '18 at 13:47

Robert Sandu

673
1
6
15

2

Not quite that simple. Since both tables participate in the `where` clause either it needs to be modified to accommodate nulls or the appropriate conditions need to be moved to the `on` clause. Aside: Good information about `COALESCE` and `ISNULL` is at SQL Server Pro [here](http://sqlmag.com/t-sql/coalesce-vs-isnull) and MSDN Blogs [here](http://blogs.msdn.com/b/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx). – HABO Sep 29 '18 at 13:49
1

The predicates on the `b` columns in the `WHERE` clause must be moved to the `ON` clause of the `LEFT JOIN`. No rows will be returned for non-matching rows since the `NULL` compare will result in `UNKNOWN`. – Dan Guzman Sep 29 '18 at 13:51
I have to agree on the caution with using `ISNULL` and `COALESCE` in a `WHERE` clause. It can often cause the query to become non-SARGable, which can result in long running queries. – Thom A Sep 29 '18 at 14:05

score 0 · Accepted Answer · answered Sep 29 '18 at 14:45

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.

score 0 · Answer 3 · answered Sep 30 '18 at 04:43

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'

Getting result with unmatched records as NULL

3 Answers3