• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 670
  • Last Modified:

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 --

Select
EX.[Exchequer-Code],
M.FirstName + ' ' + M.LastName Name,
AD.ClubName,
AD.Address1,
AD.Address2,
T.Town,
R.Region,
C.Country,
AD.PostCode,
PH.Phone,
EM.Email,
AT.Type,
M.[Map-Reference],
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,
 CONSTRAINT [RetailVisit_pk] PRIMARY KEY CLUSTERED 
(

Open in new window


Thank you
0
garethtnash
Asked:
garethtnash
1 Solution
 
itmarsolutionsCommented:
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.
0
 
CluskittCommented:
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.
0
 
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

LEFT JOIN
(
     select
               VisitID
              ,vISITdETAILS
             ,etc....
            ROWNUMBER over(PARTITION BY pARTENERID,
                                                 ORDER BY Date DESC) VisitRank


) RECENT_VISITS
ON M.PartnerID = RECENT_VISITS.PartnerID and VisitRank = 1
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
0
 
CluskittCommented:
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).
0
 
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 :(

Thanks
0
 
CluskittCommented:
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
0
 
CluskittCommented:
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.
0
 
garethtnashAuthor Commented:
Hi,

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.
0
 
CluskittCommented:
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.
0
 
garethtnashAuthor Commented:
Superb, thank you
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now