Help With SQL Query

Hello Experts,

Am trying to build a query below, that access many tables, I have two issues with this query -

1) I'm getting 'Conversion failed when converting the varchar value 'None' to data type int.' messages for lines 18 & 19

2) For the section 'Left join dbo.RetailVisit Visit on Visit.PartnerID = M.ID' There may have been many Retail Visits for each Partner, I only want to return the results for the most recent visit to each Partner.

Here's my query so far --

M.FirstName + ' ' + M.LastName Name,
CT.Type ClubType,
CASE WHEN Enews.EnewsPackage IS NULL THEN 'None' ELSE ENP.Name END AS Enews,
Case When Pem.EPOSID IS NULl then 'None' ELSE Epos.EPOSModel End AS Epos,
CASE WHEN Visit.PartnerID IS NULL then 'None' ELSE Visit.[Retail-Score] End AS 'Last Retail Visit',
CASE WHEN Visit.PartnerID IS NULL then 'None' ELSE Visit.VisitDate End AS 'Last Retail Visit',
LG.Username 'MYTGI Username',
LG.Password 'MYTGI Password',
RDA.FirstName +' '+ RDA.LastName RDA
from dbo.Member M
inner join dbo.[Member-Exchequer-Codes] EX
ON EX.MemberID = M.ID
inner join dbo.[Member-Address] AD
ON AD.MemberID = M.ID
inner join dbo.Town T
on AD.Town = T.ID
inner join dbo.Region R
on AD.Region = R.ID
inner join dbo.Country C
on AD.Country = C.ID
inner join dbo.[Club-Course-Type] CT on CT.ID = AD.[Club-Course-Type]
inner join dbo.[Member-Email] EM
ON EM.MemberID = M.ID
inner join dbo.[Member-Phone] PH
ON PH.MemberID = M.ID
inner join dbo.[Member-Account-Type] AT
ON M.AccountType = AT.ID
inner join dbo.Admin A ON M.[RDA-ID] = A.ID
inner join dbo.Member RDA on RDA.ID = A.MID
inner join dbo.[Member-Login] LG on LG.MemberID = M.ID
left join dbo.MemberEnews Enews on Enews.MID = M.ID
LEFT join dbo.EnewsPackage ENP on ENP.ID = Enews.EnewsPackage
LEFT JOIN dbo.PartnerEposModel Pem on Pem.PartnerID = M.ID
Left join dbo.EPOSSys Epos on Epos.ID = Pem.EPOSID
Left join dbo.RetailVisit Visit on Visit.PartnerID = M.ID
WHERE AT.ID not in (5,6,8,9,10)
AND EM.defaultemail = 'Y'
AND PH.DefaultP = 'Y'
AND AD.[Default-Address] = 'Y'
AND LG.ServiceID = 1

Open in new window

And here's the structure of dbo.RetailVisit -

CREATE TABLE [dbo].[RetailVisit](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[PartnerID] [int] NOT NULL,
	[RetailConsultant] [int] NOT NULL,
	[Retail-Score] [int] NOT NULL,
	[VisitDate] [datetime] NOT NULL,
	[VisitNotes] [nvarchar](max) NULL,
	[VisitDocument] [nvarchar](255) NULL,

Open in new window

Thank you
Who is Participating?
You have to change it in both the select and the group statements:
LEFT JOIN (dbo.RetailVisit Visit INNER JOIN (SELECT PartnerID AS PID, MAX(VisitDate) AS vd FROM dbo.RetailVisit GROUP BY PartnerID) t ON PartnerID=PID AND VisitDate=vd) ON Visit.PartnerID = M.ID
for the first one.

1) I'm getting 'Conversion failed when converting the varchar value 'None' to data type int.' messages for lines 18 & 19

you have to change the 'None' to any int value. because in that case statement you checking the Visit.PartnerID is an int.
1) That is because the data type for the field is int. You're mixing two data types when you switch between 'None' and int. To prevent this, either change 'None' to 0, or the best option, case the field: CAST(Visit.[Retail-Score] AS varchar(50)) (however many characters it has)

2) What you want is:
LEFT JOIN (dbo.RetailVisit INNER JOIN (SELECT [Retail-Score] AS rs, MAX(VisitDate) AS vd FROM dbo.RetailVisit GROUP BY [Retail-Score]) t ON [Retail-Score]=rs AND VisitDate=vd) ON Visit.PartnerID = M.ID

You can add as many fields as you wish to use in that table.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Barry CunneyCommented:
1. CASE WHEN Visit.PartnerID IS NULL then 'None' ELSE cast(Visit.[Retail-Score] as char(4)) End AS 'Last Retail Visit',
CASE WHEN Visit.PartnerID IS NULL then 'None' ELSE cast(Visit.VisitDate as char(10)) End AS 'Last Retail Visit',

2. Use a ranked subquery to get most recent visits

                                                 ORDER BY Date DESC) VisitRank

ON M.PartnerID = RECENT_VISITS.PartnerID and VisitRank = 1
garethtnashAuthor Commented:
Hello All,

Thanks for your help so far. I have converted cast the columns as needed.

Cluskitt, thanks I tried your solution, but SSMS gave me some red lines, so I changed it to -

LEFT JOIN (dbo.RetailVisit Visit INNER JOIN (SELECT [Retail-Score] AS rs, MAX(VisitDate) AS vd FROM dbo.RetailVisit GROUP BY [Retail-Score]) rs ON [Retail-Score]=rs AND VisitDate=vd) ON Visit.PartnerID = M.ID

Open in new window

But I'm still getting duplicates for Partners?

BCUNNEY, your solution looks interesting, but again I'm getting red lines, could you double check for me please?

Thanks All
Well, I was grouping by Retail-Score. I assume this isn't what you want. You want to group by whatever is the defining key(s) in your table. Probably PartnerID? I'm not sure if you need more fields (it depends on how you use your data, but usually this would be enough).
garethtnashAuthor Commented:
You are correct, it is PartnerID, I've tried changing it, but I get

Column 'dbo.RetailVisit.Retail-Score' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Sorry I'm being really dumb here, but I'm confused :(

Basically, what you're doing is selecting the MAX(VisitDate) for each PartnerID. However, that is not enough. You need more fields, like Retail-Score. So you INNER JOIN it with the same table. That way, you end up with a single row for each PartnerID, but with all the fields available (and, in fact, ID and date will be available twice). You can then LEFT JOIN it normally.
garethtnashAuthor Commented:

Thanks, I think something is missing though as i get -

Msg 1011, Level 16, State 1, Line 1
The correlation name 't' is specified multiple times in a FROM clause.
If you're using the alias T already for a table, you can't use it for this one. Just use t1, or Table1, or RetailVisitMax... it just has to be unique.
garethtnashAuthor Commented:
Superb, thank you
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.