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...

I dont know if I get this right:

You said

"and this is my query2:

SELECT Orders.CustomerID, customers.companyname, max(Orders.ShippedDate)

FROM Orders INNER JOIN

Customers ON Orders.CustomerID = Customers.CustomerID

where Customers.Country='usa'

GROUP BY Orders.CustomerID, Customers.CompanyName

I want query 1...

Good example of what I just told you:

http://riteshshah.wordpress.com/2009/03/03/use-of-rank-and-partition-by-clause-in-sql-server-2005/

what table is trans_Date coming from and what are the other columns in that table ?

Just isolate the trans_date and the other columns you need to group on...

have you ever tried using Rank By Partition() ?

They dont need to be sub-packages.

If they can run-in parallel, based on the condition of what ID it is.. Its more than enough..

Thanks a million!

Thanks for your reply. I have developed a few packages earlier, but I am not able to follow the flow in your answer.

To summarize the whole scenarion:

I want...