Solved

Left Outer Join of Multiple Tables

Posted on 2008-06-24
6
1,053 Views
Last Modified: 2012-08-13
With the below stored procedure, we are attempting to return a sum of "Units" from "Schedule" and a sum of the units that were delivered from the "VisitLog & VisitDetail" tables.  The problem is that with the below code, if there is no visit that matches the "Schedule" it will not include the row(s).  This obviously produces incorrect results, and we are needing help creating a left outer join between these tables.


ALTER  PROCEDURE ReportRunSummary
(
@DateStart DateTime,
@DateEnd DateTime,
@DCN int,
@CountiesID int
)
 AS

SELECT DCN, ClientName,
monthlyauthunits as MonthlyAuthUnits, ScheduleExports.ServiceCode,
sum (NumberOfUnits /4.0) As DeliveredUnits, sum(ScheduleExports.units) - sum (NumberOfUnits /4.0) As [Difference], sum(ScheduleExports.units) As ScheduledUnits
FROM scheduleexports, visitlog, VisitDetail, Services
WHERE
visitDate >= @DateStart
and
visitDate <= @DateEnd
and
PatientID = DCN
and
(DCN = @DCN or @DCN = -1)
And
Visit_Date >= @DateStart
And
Visit_Date <= @DateEnd
And
visitDate = Visit_Date
And
VisitDetail.VisitID = VisitLog.VisitId
And
Convert(int,VisitDetail.ServiceCode) = Services.ServicesID
And
ServiceAbbr = ScheduleExports.ServiceCode
and
(County = @CountiesID or @CountiesID =-2)

group by ScheduleExports.servicecode, DCN, ClientName, monthlyauthunits
order by ClientName, ScheduleExports.ServiceCode
0
Comment
Question by:Element74
[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
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21859909
please change the query so that each column name is prefixed with the table name is comes from.
otherwise it will be impossible to know where it comes from, and to change the query to the JOIN  or LEFT JOIN sytnax.
0
 

Author Comment

by:Element74
ID: 21860015

ALTER  PROCEDURE ReportRunSummary
(
@DateStart DateTime,
@DateEnd DateTime,
@DCN int,
@CountiesID int
)
 AS

SELECT ScheduleExports.DCN, ScheduleExports.ClientName, ScheduleExports.monthlyauthunits as MonthlyAuthUnits, ScheduleExports.ServiceCode, sum (VisitLog.NumberOfUnits /4.0) As DeliveredUnits, sum(ScheduleExports.units) - sum (VisitLog.NumberOfUnits /4.0) As [Difference], sum(ScheduleExports.units) As ScheduledUnits
FROM scheduleexports, visitlog, VisitDetail, Services
WHERE
ScheduleExports.visitDate >= @DateStart
and
ScheduleExports.visitDate <= @DateEnd
and
VisitLog.PatientID = ScheduleExports.DCN
and
(ScheduleExports.DCN = @DCN or @DCN = -1)
And
VisitLog.Visit_Date >= @DateStart
And
VisitLog.Visit_Date <= @DateEnd
And
ScheduleExports.visitDate = VisitLog.Visit_Date
And
VisitDetail.VisitID = VisitLog.VisitId
And
Convert(int,VisitDetail.ServiceCode) = Services.ServicesID
And
Services.ServiceAbbr = ScheduleExports.ServiceCode
and
(ScheduleExports.County = @CountiesID or @CountiesID =-2)

group by ScheduleExports.servicecode, ScheduleExports.DCN, ScheduleExports.ClientName, ScheduleExports.monthlyauthunits
order by ScheduleExports.ClientName, ScheduleExports.ServiceCode
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21860100
good !
now, step 1: use the JOIN syntax (and table aliases).

tell, me, won't that be much easier to read?
ALTER  PROCEDURE ReportRunSummary
(
@DateStart DateTime,
@DateEnd DateTime,
@DCN int,
@CountiesID int
)
 AS
 
SELECT se.DCN
, se.ClientName
, se.monthlyauthunits as MonthlyAuthUnits
, se.ServiceCode
, sum (vl.NumberOfUnits /4.0) As DeliveredUnits
, sum(se.units) - sum (vl.NumberOfUnits /4.0) As [Difference]
, sum(se.units) As ScheduledUnits
FROM scheduleexports se
JOIN visitlog vl
  ON vl.PatientID = se.DCN
 AND vl.Visit_Date = se.visitDate 
 AND vl.Visit_Date >= @DateStart
 AND vl.Visit_Date <= @DateEnd
JOIN VisitDetail vd
  ON vd.VisitID = vl.VisitId
JOIN Services s
  ON s.ServicesID = Convert(int , vd.ServiceCode)
 AND s.ServiceAbbr = se.ServiceCode
WHERE se.visitDate >= @DateStart
  AND se.visitDate <= @DateEnd
  AND ( se.DCN = @DCN or @DCN = -1)
  AND ( se.County = @CountiesID or @CountiesID =-2 )
 
GROUP BY se.servicecode
  , se.DCN
  , se.ClientName
  , se.monthlyauthunits
ORDER BY se.ClientName
  , se.ServiceCode

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21860117
proceeding to step 2: return rows even if in visitlog/visitdetail there are no records matching.

huh, that was easy? :)
ALTER  PROCEDURE ReportRunSummary
(
@DateStart DateTime,
@DateEnd DateTime,
@DCN int,
@CountiesID int
)
 AS
 
SELECT se.DCN
, se.ClientName
, se.monthlyauthunits as MonthlyAuthUnits
, se.ServiceCode
, sum (vl.NumberOfUnits /4.0) As DeliveredUnits
, sum(se.units) - sum (vl.NumberOfUnits /4.0) As [Difference]
, sum(se.units) As ScheduledUnits
FROM scheduleexports se
LEFT JOIN visitlog vl
  ON vl.PatientID = se.DCN
 AND vl.Visit_Date = se.visitDate 
 AND vl.Visit_Date >= @DateStart
 AND vl.Visit_Date <= @DateEnd
LEFT JOIN VisitDetail vd
  ON vd.VisitID = vl.VisitId
LEFT JOIN Services s
  ON s.ServicesID = Convert(int , vd.ServiceCode)
 AND s.ServiceAbbr = se.ServiceCode
WHERE se.visitDate >= @DateStart
  AND se.visitDate <= @DateEnd
  AND ( se.DCN = @DCN or @DCN = -1)
  AND ( se.County = @CountiesID or @CountiesID =-2 )
 
GROUP BY se.servicecode
  , se.DCN
  , se.ClientName
  , se.monthlyauthunits
ORDER BY se.ClientName
  , se.ServiceCode

Open in new window

0
 

Author Comment

by:Element74
ID: 21860345
Thanks for the quick responses. We ran the query this way and the numbers are too high. I suspect some rows are being pulled in from the joined table that we don't need.

I.e. the services table is a straight join between the schedule table, the service table, and the visit log table. What it does is allows us to join the schedule table to the visit log on the service code.

The schedule table uses a character abbreviation in its service code, but the visit log uses a number code, so the join table links this. I think the left join may be matching too many rows between those tables.

The same thing may be true for the visitlog and visitdetail tables. They are a one to many relationship. Each visit may have multiple visitdetails, but each visitdetail only has one visit.

I apologize, but I am really unfamiliar with outer joins and I am not really a database person, so I hope my questions don't sound too stupid.

Do you have any idea if we are on the right track towards fixing this? What other information will help answer our question?

Thanks
0
 

Accepted Solution

by:
Element74 earned 0 total points
ID: 22016042
Here is the solution that we finally came up with.  This query, along with setting up multi-column indexes, improved the runtime of our webform from over 2 minutes to around 5 - 6 seconds.

DECLARE @DateStart datetime
DECLARE @DateEnd datetime
DECLARE @DCN int
DECLARE @FilterMode int
Declare @COuntiesID int
 
Select @CountiesID = -2
 
SELECT @DateStart = '5/1/2008'
SELECT @DateEnd = '5/31/2008'
--SELECT @DCN = 62530682
--SELECT @DCN = 58299465
SELECT @DCN = -1
 
-- @FilterMode
--   1 = All For Date Range
--   2 = More Than Necessary Units
--   3 = Less Than Necessary Units
--   4 = No Units At All
 
SELECT @FilterMode = 3
 
SELECT v.*
  FROM (SELECT se_i.DCN,
               se_i.ClientName,
               se_i.monthlyauthunits as MonthlyAuthUnits,
               se_i.ServiceCode,
               se_i.VisitStart,
               se_i.VisitEnd,
               se_i.units,
               (ISNULL ((SELECT sum (vl.NumberOfUnits)
                           FROM visitlog vl,
                                visitdetail vd,
                                services s
                           WHERE s.ServicesID = Convert(int , vd.ServiceCode)
                             AND s.ServiceAbbr = se_i.ServiceCode        
                             AND vd.VisitID = vl.VisitId
                             AND vl.PatientID = se_i.DCN
                             AND vl.Visit_date = se_i.VisitDate
                                                     ), 0) / 4.0) As DeliveredUnits
          FROM scheduleexports se_i
          WHERE se_i.visitDate >= @DateStart
            AND se_i.visitDate <= @DateEnd
            AND (@DCN = -1 or se_i.DCN = @DCN )
            AND (@CountiesID =-2 or se_i.County = @CountiesID)) as v
   WHERE ((@FilterMode = 1)
             OR (@FilterMode = 2 AND v.DeliveredUnits > v.units)
             OR (@FilterMode = 3 AND v.DeliveredUnits < v.units)
             OR (@FilterMode = 4 AND v.DeliveredUnits = 0))
   ORDER BY v.ClientName, v.ServiceCode
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql, how to change the data type after data loading? 9 59
store vs query adhoc - no show rows 4 37
What is needed to become a DBA? 7 56
SQL: Transformation or Pivot 3 36
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

734 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