Help With SQL Query

Posted on 2012-08-28
Last Modified: 2012-08-28
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
Question by:garethtnash
    LVL 2

    Expert Comment

    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.
    LVL 18

    Expert Comment

    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.
    LVL 17

    Expert Comment

    by:Barry Cunney
    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

    Author Comment

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

    Expert Comment

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

    Author Comment

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

    LVL 18

    Accepted Solution

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

    Expert Comment

    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.

    Author Comment


    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.
    LVL 18

    Expert Comment

    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.

    Author Closing Comment

    Superb, thank you

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now