Help needed getting max date from 2nd query

• Performance Guard (Shehap)

  Ten Centuries

  Points: 1297

  More actions

  October 13, 2011 at 1:22 am

  #1393839

  Yes, it should be JR.oper_num not Jt.oper_num since no exist of the alias Jt there (it was just missed by me)

  Anyway, I am happy to help ...and if needed more details about performance prespective..please let me know

  [font="Arial Black"]Performance Guard[/font]

  [font="Courier New"]Just ask me to get 0 sec for all queries[/font]

• R.P.Rozema

  SSChampion

  Points: 12317

  More actions

  October 13, 2011 at 9:31 am

  #1394152

  iluvmyelement (10/12/2011)

  ---

  UPDATE: I think I made the necessary change to get what I expected. Now I just need to adapt this to my 'real' database with different tables/fields. Thank you!!!! (I'll update if I get stuck)

  SELECT c.CustomerID, c.CompanyName, c.ContactName, c.City, c.Region, r.RegionDescription, o.MaxShippedDate

  FROM Customers c

  CROSS JOIN Region r

  LEFT OUTER JOIN (

  SELECT o.CustomerID, MAX(o.ShippedDate) AS MaxShippedDate

  FROM Orders o

  group by CustomerID

  ) o ON (o.CustomerID = c.CustomerID AND c.Country = 'usa')

  Yes, that's it. My bad, sorry.

  Did you have a look at the cross join between customers and region, as I suggested? I think you intended to have an inner join there, something along the lines of "from Customers c inner join Region r on r.Region = c.Region"

  ---

  
  Posting Data Etiquette - Jeff Moden[/url]
  Posting Performance Based Questions - Gail Shaw[/url]
  Hidden RBAR - Jeff Moden[/url]
  Cross Tabs and Pivots - Jeff Moden[/url]
  Catch-all queries - Gail Shaw[/url]
  

  ---

  
  If you don't have time to do it right, when will you have time to do it over?

• VSP

  SSC-Addicted

  Points: 467

  More actions

  October 13, 2011 at 6:47 pm

  #1394409

  Assuming that trans_date is coming Table3, please try this..

  select JB.id1, JB.id2, JB.stat, JB.item, JR.oper_num, JR.complete, JR.qty_complete, JT.max_trans_date

  from TABLE1 JB

  join TABLE2 JR on JB.ID1 = JR.ID1 and JB.ID2 = JR.ID2

  left outer join

  (select ID1, ID2, oper_num, max(trans_date) as max_trans_date

  from TABLE3

  group by ID1, ID2, oper_num

  ) JT on JR.ID1 = JT.ID1 and JR.ID2 = JT.ID2 and JR.oper_num = JT.oper_num

  where JB.type = 'j' and JB.stat = 'R'

  and (JR.complete = 0 and (JR.complete >0 or JR.qty_complete >0))

  order by JB.item, JR.oper_num

  1. where clause in your queries doesn't include any columns from table3

  2. join columns between table3 and table2 are id1, id2 and oper_num

  3. so, the subquery will give the max_trans_date, for each combination of id1, id2 and oper_num in table3

  4. when you join table2 with the subquery, you get the corresponding max_trans_date for the id1, id2, oper_num combination in table2.

  Hope this helps.