Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSRS SQL Server 2008 Query to display all records from one table in report

Posted on 2012-03-19
3
Medium Priority
?
464 Views
Last Modified: 2012-03-21
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
0
Comment
Question by:rtay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 37740590
I think you're making things too complicated too soon in your development.

Try just linking your Driver and Load tables on driver number (using inner join) and pulling only the driver name fields and, from the Load table, the date. Then constrain it to 2/12 and you'll get those 15 records.

Then try the various other types of join (left and right outer, and full outer). One will give you your full set of drivers, and nulls in some records for the field  from the Load table. Then build it from there.

hth

Mike
0
 
LVL 5

Author Comment

by:rtay
ID: 37740664
I have done that in my testing.  I just sent my over all working code.  What I have been unable to get is the nulls for for drivers with no records.  I have tried inner joins and get a result of let's say 10 drivers with load records instead of 15 drivers, 5 with loads.  

Your are correct though, that query example is more complicated than than the help I am looking for.  I just need a simple example that will supply the nulls.

Thanks
0
 
LVL 16

Accepted Solution

by:
DcpKing earned 2000 total points
ID: 37741573
Inner Joins won't cut it. You need to use outer joins to get the missing rows too. Try this:

select d.LastName, d.FirstName, m.ShDate
from Tbl_Driver d
left outer join Malt m
on m.Driver = d.eNumber
where m.ShDate = @dtDate

with @dtDate set to whatever date will give you known results - like 2/12, I think you said.

You should get all the drivers, but nulls in some rows in the date column.

Expand the complexity from there.

hth

Mike
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question