Sql-server – Need to Bring Back most Recent Departure Date from Sales table for Customers in Recipient table

distinctduplicationjoin;maxsql server

Looking at 3 Tables – Recipient, HouseholdScores, Sales.

I need to bring back the most Recent tsDepart (departure date) from sales for the iCustomerId in the Recipient Table. There is no Depart date information in the Recipient Table so i can only join the two tables on iCustomerID.

What I have Tried (Using – 'Microsoft SQL Server Management studio 17':

SELECT DISTINCT

iRecipientId as RecipientID,NmsRecipient.iCustomerId as Cust_Num,sFirstName as FirstName,sLastName as LastName,sEmail as Email,
iBlacklistPostalMail as NoMail,iBlacklistEmail as NoEmail,iBlacklistMailGCCL as DontMailGCCL,iBlacklistGCCL as DontEmailGCCL,
iBlacklistMailOAT as DontMailOAT,iBlacklistOAT as DontEmailOAT,sLastBrand as LBT,sFrequencyDetail as Frequency,sRecency as Recency,GccTbl_HouseholdScores.iNumTripsBkd as Trips,
GccTbl_Sales.tsDepart as Departure_Date

FROM ((neolane.NmsRecipient

Inner Join [neolane].[GccTbl_HouseholdScores] ON NmsRecipient.iRecipientId = GccTbl_HouseholdScores.iHouseHoldId)
Inner Join [neolane].[GccTbl_Sales] ON NmsRecipient.iCustomerId = GccTbl_Sales.iCustomerId)

WHERE iIrateFlag= 1
    AND iOperationalFlag= 1
    AND iEmployeeFlag= 0
    AND iDeceased= 0

Order By iblacklistPostalMail,iBlacklistEmail,iBlacklistMailGCCL,iBlacklistGCCL,iBlacklistMailOAT,iBlacklistOAT;

Results are bringing back Duplicate iCustomerID where i only want 1 instance for each with the Most Recent Departure date

Best Answer

If the only two references from the table GccTbl_Sales are the columns iCustomerId and tsDepart, this should work:

SELECT DISTINCT

iRecipientId AS RecipientID, NmsRecipient.iCustomerId AS Cust_Num, sFirstName AS FirstName, sLastName AS LastName, sEmail AS Email,
iBlacklistPostalMail AS NoMail, iBlacklistEmail AS NoEmail, iBlacklistMailGCCL AS DontMailGCCL, iBlacklistGCCL AS DontEmailGCCL,
iBlacklistMailOAT AS DontMailOAT, iBlacklistOAT AS DontEmailOAT, sLastBrand AS LBT,sFrequencyDetail AS Frequency, sRecency AS Recency,
GccTbl_HouseholdScores.iNumTripsBkd AS Trips, GccTbl_Sales.tsDepart AS Departure_Date

FROM (neolane.NmsRecipient

INNER JOIN neolane.GccTbl_HouseholdScores ON NmsRecipient.iRecipientId = GccTbl_HouseholdScores.iHouseHoldId)
INNER JOIN (
            SELECT GccTbl_Sales.iCustomerId, MAX(GccTbl_Sales.tsDepart) AS tsDepart
            FROM neolane.GccTbl_Sales
            GROUP BY GccTbl_Sales.iCustomerId
            ) AS GccTbl_Sales ON NmsRecipient.iCustomerId = GccTbl_Sales.iCustomerId

WHERE iIrateFlag= 1
    AND iOperationalFlag= 1
    AND iEmployeeFlag= 0
    AND iDeceased= 0

ORDER BY iblacklistPostalMail,iBlacklistEmail,iBlacklistMailGCCL,iBlacklistGCCL,iBlacklistMailOAT,iBlacklistOAT;

And if the DISTINCT was put there in order to remove the duplicates of iCustomerID, I think you're safe to remove it now.

Related Question