SSRS SQL Server 2008 Query to display all records from one table in report
Posted on 2012-03-19
I have a report based on two related tables. one table is drivers and the other is load records. tables are joined by driver number. I have a report (query below) that counts loads a day per driver. This works fine for drivers that had a load that day. for example the driver table has 25 drivers in it, only 15 drivers had loads on 2/12 so the names of only 15 drivers show up. The user wants ALL drivers to show up on the report and if they had no loads it will report a 0.
I have tried a join showing all records from driver table, but still do not get all drivers listed if they had no loads.
Any ideas on how to list all drivers regardless if they had loads that day?
WITH ShippersPerDriver AS (SELECT D.ENumber, COUNT(DISTINCT M.Shipper) AS ShipperCNT, M.ShDate
FROM Malt AS M INNER JOIN
Tbl_Driver AS D ON M.Driver = D.ENumber
GROUP BY D.ENumber, M.ShDate)
SELECT Malt.ShDate, Malt.FrtBill, Malt.Shipper, Tbl_Driver.LastName, Tbl_Driver.FirstName, SHP.ShipperCNT, SHP.ENumber, SHP.ShDate AS Expr1
FROM Malt LEFT OUTER JOIN
ShippersPerDriver AS SHP ON Malt.Driver = SHP.ENumber AND Malt.ShDate = SHP.ShDate LEFT OUTER JOIN
Tbl_Driver ON Malt.Driver = Tbl_Driver.ENumber
WHERE (Malt.ShDate BETWEEN @StartDate AND @EndDate)
ORDER BY Tbl_Driver.LastName