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?
 
Barry CunneyConnect With a Mentor Commented:
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.