|A couple of tweaks is all you are going to need:
INNER JOIN requires matching record to be in both tables.
To get all records from one table regardless if it matches or not you would use either a LEFTRIGHT JOIN. As you want all the records from the second table in the
ON connector it would be a
The second thing is dealing with returning 0 if no records match. Relatively easy, just slap an
ISNULL (or COALESCE for ANSI SQL) with 0 as the null replacement.
My first attempt at this came up with
, Total_BRKS = IsNull(Sum(s.BRKS), 0)
, Total_BBOH = IsNull(Sum(s.BBOH), 0)
, Avg_DTAB = IsNull(Avg(s.DTAB), 0)
FROM @ShiftData s
RIGHT JOIN @MapBushingData m ON s.[Position] = m.LoopCode
WHERE ( s.ShiftDate >= cast('02/03/2011' as date)
AND s.ShiftDate <= cast('03/04/2013' as date)
OR s.ShiftDate IS NULL
GROUP BY m.Chopper,m.LoopCodeAnd in testing I found out that IF there is a matched record BUT the date was out of range it would not return the record.
If this is desired; great.
If not, we are going to tweak how the data is JOINed together by relocating the WHERE clauses to also be part of the JOIN connections. And this is what I came up with
DECLARE @ShiftData TABLE (ndx int identity(1,1) not null, Position int, ShiftDate Date, BRKS int, BBOH int, DTAB int)
VALUES (1, '10/11/2012', 1, 2, 3)
, (2, '12/11/2013', 3, 4, 5)
, (3, '10/11/2011', 1, 2, 3)
DECLARE @MapBushingData TABLE (ndx int identity(1,1) not null, LoopCode int, Chopper varchar(10))
VALUES (1, 'Chopper 1')
, (2, 'Chopper 2')
, (3, 'Chopper 3')
Director of Transmogrification Services
Shinobi of Query Language
Master of Yoda Conditional