Too Many Joins and Max Statements

[Khanson82]

I'm getting still about 290 records more than I'm supposed to from a few duplications it's taking in for the results.Any ideas after looking at the below?From looking at the end data I think the duplication is coming from the highlighted section.

select a.clientid,(a.status + ' / ' + a.statusreason) as Status, a.agency as [Plan], a.applid as Applid, a.caseno as Caseno, a.applname as [Member Name],
a.rcvdt as [Date Received],a.fudt as [Callback Date], a.state as State, a.agent as County, a.city as City, a.zip as [Zip Code], b.providerid as [Provider Id],
b.appt_DateTime as [Appointment Date], c.fudate as [Follow Up], d.requirement as Requirement, d.adddttm as [Note Date],
d.notes as Notes

from (table1) e with(nolock)

inner join (table3) a on a.clientid = e.clientid

left join (table2) d with(nolock)
on a.clientid = d.clientid
and a.serial = d.appserial
and d.serial in
(select max(serial) from (table2) with(nolock)
where notes like ('% %')
group by appserial)

left join (table4) b with(nolock)
on e.clientname = b.coid
and a.serial = b.appserial
and b.serial in
(select max(serial) from (table4) with(nolock)
Where appt_status not in ('assessment complete qc','corrections submitted', 'paperwork submitted')
group by appserial)

left join (table5) c with(nolock)
on a.serial = c.appserial
and a.clientid = c.clientid
order by status,applid

 

[r937]

i think your subqueries (one of which you highlighted in red) were returning the max(serial) value fine, but there were multiple rows with the same max value, causing your dupes

you need to match on appserial as well

select a.clientid
     , a.status + ' / ' + a.statusreason AS Status
     , a.agency AS [Plan]
     , a.applid AS Applid
     , a.caseno AS Caseno
     , a.applname AS [Member Name]
     , a.rcvdt AS [Date Received]
     , a.fudt AS [Callback Date]
     , a.state AS State
     , a.agent AS County
     , a.city AS City
     , a.zip AS [Zip Code]
     , b.providerid AS [Provider Id]
     , b.appt_DateTime AS [Appointment Date]
     , c.fudate AS [Follow Up]
     , d.requirement AS Requirement
     , d.adddttm AS [Note Date]
     , d.notes AS Notes
  FROM table1 AS e WITH (NOLOCK)
INNER 
  JOIN table3 AS a 
    ON a.clientid = e.clientid 
[red]LEFT OUTER
  JOIN ( SELECT appserial
              , MAX(serial) AS maxserial 
           FROM table2 WITH (NOLOCK)
          WHERE notes LIKE ('% %')
         GROUP 
             BY appserial ) AS max2
    ON max2.appserial = a.serial[/red]
LEFT OUTER
  JOIN table2 AS d WITH (NOLOCK)
    ON d.clientid = a.clientid
   AND d.appserial = a.serial
   [red]AND d.serial = max2.maxserial  [/red]
[blue]LEFT OUTER
  JOIN ( SELECT appserial
              , MAX(serial) AS maxserial 
           FROM table4 WITH (NOLOCK)
          WHERE appt_status NOT IN 
            ( 'assessment complete qc'
            , 'corrections submitted'
            , 'paperwork submitted' )
         GROUP 
             BY appserial ) AS max4
    ON max4.appserial = a.serial[/blue]  
LEFT OUTER
  JOIN table4 AS b WITH (NOLOCK)
    ON b.coid = e.clientname
   AND b.appserial = a.serial
   [blue]AND b.serial = max4.maxserial[/blue]
LEFT OUTER
  JOIN table5 AS c WITH (NOLOCK)
    ON a.serial = c.appserial
   AND a.clientid = c.clientid
ORDER 
    BY status
     , applid

note: BOL has this warning --

Caution Because the query optimizer of SQL Server usually selects the best execution plan for a query, it is recommended that <join_hint>, <query_hint>, <table_hint>, and <view_hint> only be used as a last resort by experienced developers and database administrators.

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Khanson82]

That worked! Thanks so much for taking the time to look into that mess..