0

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

  • Please, add the DDL of the tables you're using so that it's possible to run the query. That way it's gonna be easier to help you. – Ronaldo Apr 29 at 15:28
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.