CODEWITHSUNDEEP
sqlsql-serverjoin
JShaffer
JShaffer

Reputation: 41

Joining two tables on columns that don't equal

I have a equipment table and a downtime table that I am wanting to join, I am wanting to display all the equipment and the downtime hours. If there is no downtime for a certain piece of equipment then I want to display a zero in the rows where value is null. This is what I have below. It only gives me the equipment that has downtime in the other table.

Select a.EquipNbr,
       ISNULL(Sum(a.Downtime),0)
From MobileDowntime (nolock) a
Join MblEquip (nolock) b on a.EquipNbr = b.EquipNbr
Where b.DelFlg = 0 and
      b.EquipNbr <> 'Clean Shop' and
      a.DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and  DATEADD(month, DATEDIFF(month, -1, getDate()), -1) 
Group By a.EquipNbr
Order by a.EquipNbr Asc

example of what I am trying to accomplish.. But the downtime table on captures data on change so there might not be any downtime for that piece of equipment for the whole month. 66 total pieces of equipment Equipment / Downtime 1717 57
1723 0 1724 0 1725 50 1728 0 1734 35 1738 0

Upvotes: 0

Views: 78

Answers (3)

Menelaos
Menelaos

Reputation: 25727

Final Answer

Select b.EquipNbr, Sum(ISNULL((a.Downtime),0)) From MobileDowntime (nolock) a
RIGHT OUTER Join MblEquip (nolock) b on a.EquipNbr = b.EquipNbr
Where b.DelFlg = 0 and b.EquipNbr != 'Clean Shop' 
AND
(
a.datetm is null or 
(a.DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) 
and DATEADD(month, DATEDIFF(month, -1, getDate()), -1) )
)
Group By b.EquipNbr Order by b.EquipNbr Asc

Fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=cc2c2cce139cda7d7c5878d6c967da34

Step by Step

Step 1:

What you need to do is to use an outer-join, and a function that replaces NULL with zero (that you are doing).

So as a first step you would do the following:

Select b.EquipNbr, ISNULL((a.Downtime),0) From MobileDowntime (nolock) a
RIGHT OUTER Join MblEquip (nolock) b on a.EquipNbr = b.EquipNbr

Step 2: With Group by

Following, you can add the group by to get the following:

Select b.EquipNbr, Sum(ISNULL((a.Downtime),0)) From MobileDowntime (nolock) a
RIGHT OUTER Join MblEquip (nolock) b on a.EquipNbr = b.EquipNbr
Where b.DelFlg = 0 and b.EquipNbr != 'Clean Shop' 
Group By b.EquipNbr Order by b.EquipNbr Asc

The final part is the where condition using the dates.

Update

The conversion error I think was because of the numerical comparison != . I did an experiment and converted the Varchar to Int.

Then I changed the != to not like.

Select b.EquipNbr, Sum(ISNULL((a.Downtime),0)) From MobileDowntime (nolock) a
RIGHT OUTER Join MblEquip (nolock) b on a.EquipNbr = b.EquipNbr
Where b.DelFlg = 0 and b.EquipNbr not like 'Clean Shop' 
AND
(
a.datetm is null or 
(a.DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) 
and DATEADD(month, DATEDIFF(month, -1, getDate()), -1) )
)

Group By b.EquipNbr Order by b.EquipNbr Asc

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You want a left join and to move conditions on the MobileDowntime table to the on clause:

Select e.EquipNbr, coalesce(sum(md.Downtime), 0)
From MblEquip e left join
     MobileDowntime md
     on md.EquipNbr = e.EquipNbr and
        md.DateTm between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
where e.DelFlg = 0 and e.EquipNbr <> 'Clean Shop'  
group by e.EquipNbr 
order by e.EquipNbr Asc;

Note that I replaced your table aliases (hopefully correctly). a and b are meaningless. Instead, I used abbreviations for the table names.

Upvotes: 1

Nishat Anjum Lea
Nishat Anjum Lea

Reputation: 135

You can use left outer join in which it will show null when there is no downtime hours

Upvotes: 0

Related Questions