Hi Experts,
I have a question about a result. I have a table called player address or “pa”. In this table are multiple rows for addresses that contain an address and a column called mailing which has an N or Y value. What I need is to create logic where if the value of that table in the Mailing column = Y then select the corresponding row in that same table and have that pa.line1 column appear in this aggegated result.
The problem is right now because of the select top 1 subquery in the where statement this always shows up with the first row column regardless of the logic I have used. If i use a where to define that mailing = Y then select the corresponding row then it just turns up a NULL value in the resulting column. I just don't know how to put it together. Here is my code.
SELECT MAX(ISNULL(CAST(p.PlayerID AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.Status AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.Title_old AS VARCHAR), 0))
,MAX(ISNULL(CAST(pn.FirstName AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.MiddleName_old AS VARCHAR), 0)),
MAX(ISNULL(CAST(pn.LastName AS VARCHAR), 0)),
MAX (CAST(CASE WHEN p.DateEnrolled = p.DateEnrolled
THEN CONVERT(VARCHAR(20), p.DateEnrolled, 120)
ELSE CONVERT(VARCHAR(20), p.DateEnrolled, 120)
END AS VARCHAR)),
MAX (CAST(CASE WHEN p.Birthday = p.Birthday
THEN CONVERT(VARCHAR(20), p.Birthday, 120)
ELSE CONVERT(VARCHAR(20), p.Birthday, 120)
END AS VARCHAR)), MAX(ISNULL(CAST(p.AttractionNumber AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.Exempt AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.EnrollmentSource AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.PinNumber AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.AnniversaryDate AS VARCHAR), 0)),
MAX (CAST(CASE WHEN p.CurrentDay = p.CurrentDay
THEN CONVERT(VARCHAR(20), p.CurrentDay, 120)
ELSE CONVERT(VARCHAR(20), p.CurrentDay, 120)
END AS VARCHAR)), MAX(ISNULL(CAST(p.CurrentTrip AS VARCHAR), 0)),
MAX (CAST(CASE WHEN p.CurrentDayBeginDate = p.CurrentDayBeginDate
THEN CONVERT(VARCHAR(20), p.CurrentDayBeginDate, 120)
ELSE CONVERT(VARCHAR(20), p.CurrentDayBeginDate, 120)
END AS VARCHAR)),MAX (CAST(CASE WHEN p.XLastUpdated = p.XLastUpdated THEN CONVERT(VARCHAR(20),
p.XLastUpdated, 120) ELSE CONVERT(VARCHAR(20), p.XLastUpdated, 120) END AS VARCHAR)),
MAX(ISNULL(CAST(p.AbandonedCard AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.Nickname_old AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.ID1 AS VARCHAR), 0)),
MAX(ISNULL(CAST(dmpc.PostalMailOptIn AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.Gender AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.GenerationID AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.CompanyName AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.JobTitle AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.TypeID AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.Line1 AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.Line2 AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.City AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.State AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.ZipCode AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.ZipPlus AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.CountryID AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.Mailing AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.Bad AS VARCHAR), 0)),
MAX(ISNULL(CAST(pp.Phone AS VARCHAR), 0)),
MAX(ISNULL(CAST(pp.Preferred AS VARCHAR), 0)),
MAX(ISNULL(CAST(pp.AllowMessages AS VARCHAR), 0)),
MAX(ISNULL(CAST(pp.Extension AS VARCHAR), 0)),
MAX(ISNULL(CAST(pe.Email AS VARCHAR), 0)),
MAX(ISNULL(CAST(pe.DefaultEmail AS VARCHAR), 0)),
MAX(ISNULL(CAST(ph.HostID AS VARCHAR), 0)),
MAX(ISNULL(CAST(r.description AS VARCHAR), 0)),
MAX(ISNULL(CAST(rc.description AS VARCHAR), 0)),
MAX(ISNULL(CAST([PlayerManagement].[dbo].PlayerBalance.SlotPoints AS VARCHAR), 0)),Sum
(CASE WHEN [PlayerManagement].[dbo].PlayerDayPointsEarn.AccountingDate > = '2016-07-01 00:00:00.000'
THEN [PlayerManagement].[dbo].PlayerDayPointsEarn.Amount ELSE 0 END)
FROM [PlayerManagement].[dbo].Player p (NOLOCK)LEFT JOIN [PlayerManagement].[dbo].PlayerBalance
on p.PlayerID = [PlayerManagement].[dbo].PlayerBalance.PlayerID
LEFT JOIN [PlayerManagement].[dbo].PlayerDayPointsEarn on p.PlayerID = [PlayerManagement].[dbo].PlayerDayPointsEarn.PlayerID
LEFT JOIN [PlayerManagement].dbo.PlayerName as pn on p.PlayerID = pn.PlayerID
LEFT JOIN [PlayerManagement].[dbo].PlayerAddress pa (NOLOCK) on pa.playerid = p.playerid
AND (pa.typeid = (Select top 1 typeid from [PlayerManagement].[dbo].playeraddress
where [PlayerManagement].[dbo].playeraddress.playerid = pa.playerid))
LEFT JOIN [PlayerManagement].[dbo].PlayerEmail pe (NOLOCK) on pe.playerid = p.playerid
LEFT JOIN [PlayerManagement].[dbo].PlayerPhone pp (NOLOCK) on pp.playerid = p.playerid
AND (pp.TypeID = (Select TOP 1 TypeID from [PlayerManagement].[dbo].PlayerPhone
where [PlayerManagement].[dbo].playerphone.playerid = pp.playerid))
LEFT JOIN [PlayerManagement].[dbo].PlayerHost ph (NOLOCK) on ph.playerid = p.playerid
LEFT JOIN [PlayerManagement].[dbo].DirectMarketingPlayerConfig dmpc on dmpc.playerid = p.playerid
LEFT JOIN [PlayerManagement].[dbo].PlayerRestrictions pr (NOLOCK) on pr.playerid = p.playerid
LEFT JOIN [PlayerManagement].[dbo].RestrictionCodes rc (NOLOCK) on rc.RestrictionMaskID = pr.RestrictionMaskID
LEFT Join [PlayerManagement].[dbo].PlayerSiteGroup psg (NOLOCK) on psg.playerid = p.playerid
LEFT Join [PlayerManagement].[dbo].Ranking r (NOLOCK) on r.rankingnumber = psg.rankingID
group by (p.PlayerID)
It is not quite clear to me what you want to achieve, however, the way you have used the max function, is not correct, is what I believe. So please try to put your intention in bit more detail.
As I don't know how fluent you are in T-SQL, please allow me state the following:
if you have rows as follows
col1 col2 col3
1 2 3
2 1 2
3 3 1
You are going to get 3 in all columns. I think what you want is first determine the max of one column (say col1) and get corresponding values of col2 and col3. If yes, that is NOT going to happen the way you have written the query.
Please enlighten me!