Complex SQL Query (to me at least!)

Hi experts.
I have the following code as a SQL query in MS SQL Server

SELECT     dbo.Bookings.FoalStatusDate, dbo.Bookings.MareInFoal, dbo.Clients.ClientForeName, dbo.Clients.ClientSurname, dbo.Clients.Mobile, dbo.Clients.AlternativeTel, 
                      dbo.Mares.MareName, dbo.Mares.MareSire, dbo.Mares.MareDam, dbo.Mares.Studbook, dbo.Mares.RegistrationNumber, dbo.Mares.YOB, dbo.Stallions.StallionName, 
                      dbo.Bookings.Paid, dbo.Bookings.StallionID, dbo.Owners.OwnerBusiness, dbo.Dispatches.DispatchDate
FROM         dbo.Bookings INNER JOIN
                      dbo.Clients ON dbo.Bookings.ClientID = dbo.Clients.ClientID INNER JOIN
                      dbo.Stallions ON dbo.Bookings.StallionID = dbo.Stallions.StallionID INNER JOIN
                      dbo.Mares ON dbo.Bookings.MareID = dbo.Mares.MareID INNER JOIN
                      dbo.Owners ON dbo.Stallions.OwnerID = dbo.Owners.OwnerID INNER JOIN
                      dbo.Dispatches ON dbo.Bookings.BookingsID = dbo.Dispatches.BookingID
WHERE     (dbo.Bookings.Paid = 1)

Open in new window


Each booking has (or may have) multiple records in the dispatches table that relate to it.

I need the query to produce a recordset that has all the detail I have selected BUT only the last value for dbo.Dispatches.DispatchDate

So the records returned will give me the full details from the client AND the last date of dispatch.

Im afraid this is beyond my ability - would really appreciate some help.

Cheers
roblickleyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
SELECT     dbo.Bookings.FoalStatusDate, dbo.Bookings.MareInFoal, dbo.Clients.ClientForeName, dbo.Clients.ClientSurname, dbo.Clients.Mobile, dbo.Clients.AlternativeTel, 
                      dbo.Mares.MareName, dbo.Mares.MareSire, dbo.Mares.MareDam, dbo.Mares.Studbook, dbo.Mares.RegistrationNumber, dbo.Mares.YOB, dbo.Stallions.StallionName, 
                      dbo.Bookings.Paid, dbo.Bookings.StallionID, dbo.Owners.OwnerBusiness, dbo.Dispatches.DispatchDate
FROM         dbo.Bookings INNER JOIN
                      dbo.Clients ON dbo.Bookings.ClientID = dbo.Clients.ClientID INNER JOIN
                      dbo.Stallions ON dbo.Bookings.StallionID = dbo.Stallions.StallionID INNER JOIN
                      dbo.Mares ON dbo.Bookings.MareID = dbo.Mares.MareID INNER JOIN
                      dbo.Owners ON dbo.Stallions.OwnerID = dbo.Owners.OwnerID INNER JOIN
                      dbo.Dispatches ON dbo.Bookings.BookingsID = dbo.Dispatches.BookingID
		      INNER JOIN
				(
					SELECT 
					d.BookingID
					MAX(d.DispatchDate) [LatestDispatchDate]
					FROM Dispatches d
					GROUP BY
					d.BookingID
					
				) latest_dispatch
		      ON Dispatches.BookingID = latest_dispatch.BookingID
		      AND Dispatches.DispatchDate = latest_dispatch.[LatestDispatchDate]	 	
WHERE     (dbo.Bookings.Paid = 1)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
A very handy method for arriving at "the lastest" (or "oldest) is to use row_number(), like this:
SELECT
        dbo.Bookings.FoalStatusDate
      , dbo.Bookings.MareInFoal
      , dbo.Clients.ClientForeName
      , dbo.Clients.ClientSurname
      , dbo.Clients.Mobile
      , dbo.Clients.AlternativeTel
      , dbo.Mares.MareName
      , dbo.Mares.MareSire
      , dbo.Mares.MareDam
      , dbo.Mares.Studbook
      , dbo.Mares.RegistrationNumber
      , dbo.Mares.YOB
      , dbo.Stallions.StallionName
      , dbo.Bookings.Paid
      , dbo.Bookings.StallionID
      , dbo.Owners.OwnerBusiness
      , d.DispatchDate
FROM dbo.Bookings
INNER JOIN dbo.Clients
        ON dbo.Bookings.ClientID = dbo.Clients.ClientID
INNER JOIN dbo.Stallions
        ON dbo.Bookings.StallionID = dbo.Stallions.StallionID
INNER JOIN dbo.Mares
        ON dbo.Bookings.MareID = dbo.Mares.MareID
INNER JOIN dbo.Owners
        ON dbo.Stallions.OwnerID = dbo.Owners.OwnerID
INNER JOIN (
            SELECT
                   BookingID
                 , DispatchDate
                 , row_number() over (partition by BookingID order by DispatchDate DESC) as rn
            FROM dbo.Dispatches
           ) as D
        ON dbo.Bookings.BookingsID = d.BookingID
       AND d.rn = 1
WHERE dbo.Bookings.Paid = 1
;

Open in new window

{= edit} see:  row_number() and over()
0
PortletPaulEE Topic AdvisorCommented:
Actually, as you are only using DispatchDate in the output I think BCBUNNEY has the better approach here. Using row_number() would be useful if you wanted more fields from dbo.Dispatches that related to the most recent record.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.