Looking for some assistance with a query

FrankLepkowski

Hello All, I am looking for some help with the following query. The results I get back are fine as long as there is data in the ShiftData table that meets the criteria. If there is no data in the ShiftDaa table then my query returns nothing. What I am after is to return back all LoopCodes and Choppers from MapBushingData table and zeros if there are no items in ShiftData table matching the criterion. Any help is most appreciated.

SELECT MapBushingData.LoopCode,
MapBushingData.Chopper,
Sum(ShiftData.BRKS) AS Total_BRKS,
Sum(ShiftData.BBOH) AS Total_BBOH,
Avg(ShiftData.DTAB) AS Avg_DTAB
FROM ShiftData
INNER JOIN MapBushingData ON ShiftData.[Position] = MapBushingData.LoopCode WHERE ShiftData.ShiftDate >= cast(? as date) AND ShiftData.ShiftDate <= cast(? as date)

GROUP BY MapBushingData.Chopper,MapBushingData.LoopCode

Thanks, Frank

MadMyche

A couple of tweaks is all you are going to need: Using an 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 RIGHT JOIN 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

SELECT m.LoopCode
, m.Chopper
, 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.LoopCode

And 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)
INSERT @ShiftData
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))
INSERT @MapBushingData
VALUES (1, 'Chopper 1')
, (2, 'Chopper 2')
, (3, 'Chopper 3')

Director of Transmogrification Services Shinobi of Query Lang

MadMyche

Just noticed a block of code is missing, will get this updated within the next couple of hours

Director of Transmogrification Services Shinobi of Query Language Master of Yoda Conditional

FrankLepkowski

Reworked my query as shown below and all is good now. Thank you very much for your response.

SELECT m.LoopCode
, m.Chopper
,
SUM(CASE WHEN s.ShiftDate >= cast(? as date) and s.ShiftDate <= cast(? as date)
THEN s.BRKS ELSE 0
END) AS Total_BRKS
,
SUM(CASE WHEN s.ShiftDate >= cast(? as date) and s.ShiftDate <= cast(? as date)
THEN s.BBOH ELSE 0
END) AS Total_BBOH
,
AVG(CASE WHEN s.ShiftDate >= cast(? as date) and s.ShiftDate <= cast(? as date)
THEN s.DTAB ELSE 0
END) AS Avg_DTAB

FROM ShiftData s
RIGHT JOIN MapBushingData m ON s.[Position] = m.LoopCode

GROUP BY m.Chopper,m.LoopCode
ORDER BY m.LoopCode asc

MadMyche

Glad I could be of assistance; and I guess I don't need to push in the other code sample

Director of Transmogrification Services Shinobi of Query Language Master of Yoda Conditional