Solved

Complicated SQL Query

Posted on 2013-02-05
16
313 Views
Last Modified: 2013-02-13
Hello,

I've got quite a complicated SQL Query that I want to output as a table. The query references the following tables

[dbo].[Member] (The main table)
[dbo].[Member-Exchequer-Codes] (1 to 1)
[dbo].PartnerSocialMedia (1 to 1)
[dbo].[Member-Phone] (1 to Many)
[dbo].[Member-Email] (1 to Many)
[dbo].[Admin] (1 to 1)
[dbo].[Member-Address] (1 to Many)
[dbo].[Member-Login] (1 to Many)
[dbo].[RetailVisit]

The list goes on, but as an example, [dbo].[Member-Phone] is written like so --

CREATE TABLE [dbo].[Member-Phone](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[MemberID] [int] NOT NULL,
	[AddressID] [int] NOT NULL,
	[Phone] [nvarchar](20) NULL,
	[Type] [int] NULL,
	[DefaultP] [char](1) NOT NULL,
 CONSTRAINT [Member-Phone_idx] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO 

Open in new window


Please note the [Type] & [DefaultP] columns

I query this within my statement twice, once for a landline and once for a mobile

left join (select P.MemberID, P.Phone from dbo.[Member-Phone] P Where Type = 1 AND DefaultP = 'Y' Group by P.MemberID, P.Phone) P on P.MemberID = M.ID
left join (select MP.MemberID, MP.Phone from dbo.[Member-Phone] MP Where Type = 2 Group by MP.MemberID, MP.Phone) MP on MP.MemberID = M.ID

Open in new window


There should only be 1 default landline, but there maybe multiple mobile numbers for each Member. I only want to out put 1 row for each Member so only the first mobile number for each member should be selected.

As another example the table also needs

[Retail-Score] & [VisitDate] from [dbo].[RetailVisit]

SELECT TOP 1000 [ID]
      ,[PartnerID]
      ,[RetailConsultant]
      ,[Retail-Score]
      ,[VisitDate]
      ,[VisitNotes]
      ,[VisitDocument]
  FROM [dbo].[RetailVisit]

Open in new window


For the last record in [dbo].[RetailVisit] that is related to the Member table.

I've started building this, and you will see from my query below that I'm using LEFT joins, as whilst dbo.member should always have a record, some of the associated tables may have no associated tables....

I need to output in tabular format listing all columns for all members...

My questions are, how do I :

Only select the first mobile number in a scenario where there may be many,

How do I select the last Retail-Score & VisitDate in a scenario where there may have been many retail visits

I also want to include Total Spend & Total Spend for the current year from the following table for each member --

CREATE TABLE [dbo].[OrderDetail](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[OrderID] [int] NOT NULL,
	[ProductID] [int] NOT NULL,
	[ProductName] [nvarchar](50) NOT NULL,
	[ProductSKU] [nvarchar](10) NOT NULL,
	[Price] [decimal](10, 2) NOT NULL,
	[Quantity] [int] NOT NULL,
	[TotalPrice] [decimal](10, 2) NOT NULL,
	[Status] [char](1) NOT NULL
) ON [PRIMARY]

GO

Open in new window


Here's what I have so far --

SELECT M.[ID],
CASE WHEN M.[title] = 1 THEN 'Mr'
WHEN M.[title] = 2 THEN 'Mrs'
WHEN M.[title] = 3 THEN 'Miss'
WHEN M.[title] = 4 THEN 'Ms'
ELSE '' END AS Title,
M.[FirstName],
M.[LastName],
M.[Birthdate],
M.[ClubName] as TradingAs,
M.[AccountType],
CASE 
WHEN M.[UK-Member] = 'Y' THEN 'UK'
WHEN M.[Eire-Member] = 'Y' THEN 'Eire'
WHEN M.[EU-Member] = 'Y' THEN 'EU' END AS PartnerCategory,
RDA.FirstName + ' ' + RDA.LastName RDA,
M.[PGA-Number],
EX.[Exchequer-Code],
M.[Startdate],
M.[ExitDate],
M.[Profits],
M.[VAT-Liable],
P.Phone Phone,
MP.Phone Mobile,
TE.Email TGIEmail,
'AltEmail',
M.[URL],
PS.facebook,
PS.twitter,
M.[RetainerSurvey]
FROM [dbo].[Member] M
Left join dbo.[Member-Exchequer-Codes] EX on EX.MemberID = M.ID
left join dbo.PartnerSocialMedia PS on PS.PartnerID = M.ID
left join (select P.MemberID, P.Phone from dbo.[Member-Phone] P Where Type = 1 AND DefaultP = 'Y' Group by P.MemberID, P.Phone) P on P.MemberID = M.ID
left join (select MP.MemberID, MP.Phone from dbo.[Member-Phone] MP Where Type = 2 Group by MP.MemberID, MP.Phone) MP on MP.MemberID = M.ID
left join (select TE.MemberID,TE.Email from dbo.[Member-Email] TE Where TE.[TGI-Email] = 'Y' AND TE.defaultemail = 'Y' Group by TE.MemberID, TE.Email) TE on TE.MemberID = M.ID
left join dbo.[Admin] A on A.ID = M.[RDA-ID] left join dbo.Member RDA on RDA.ID = A.MID

Open in new window


Appreciate any thoughts and help with this.

Thank you
0
Comment
Question by:garethtnash
  • 6
  • 5
  • 3
  • +1
16 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38855450
I presume reading this article may help to avoid the "duplication" of the results ...
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 38855485
Welcome to the world of CTEs  (Common Table Expressions):
http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx



;WITH MobileCTE (MemberID, Phone)
AS
-- Define the CTE query.
(
    SELECT top 1 memberID, Phone from  [Member-Phone]
    where type = 'Mobile'
    -- grab the default one if there is one marked as default.

order by default desc  )


;with RetailCTE  (PartnerId, Retail-Score, VisitDate)
as
(
    select top 1 partnerId, retail-score, visitdate
    from RetailVisit
    group by partnerid, visitdate, retail-score
    order by visitdate desc
)


--then you c an just use them as tables:

select * FROM [dbo].[Member] M
join MobileCTE mc on m.MemberId = mc.MemberID
join RetailCTE  on --join conditon
where
...
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38855486
you should probably be using the windowing functions to obtain the aggregates and
selective data you wish to retrieve....

 the style is


select .....
         , sum(xxx) over (partition by ("group by columns for this sum") order by  "order by columns for the function if appropriate") as mycol
       ,row_number() over (partion by ... order by ...) as rn

i'll try to construct your require query from the info you've given...
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 38855503
I tried to edit my comment, but when using multiple CTEs the syntax is:


;  WITH a_CTE (<cols>)
   as
   (
     -- query
   )
,
  b_CTE  (<cols>)
  as
  (
    -- query
   )

-- MAIN QUERY.
0
 

Author Comment

by:garethtnash
ID: 38855592
Wow that looks complicated !
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38855629
once you check it with simple examples, it's not complicated at all.
you may not (yet) be used to it, but once you are  :)
0
 

Author Comment

by:garethtnash
ID: 38855791
ged325

I'm looking at CTEs, but am i correct in my thinking that -

;with RetailCTE  (PartnerId, Retail-Score, VisitDate)
as
(
    select top 1 partnerId, retail-score, visitdate
    from RetailVisit 
    group by partnerid, visitdate, retail-score
    order by visitdate desc
)

Open in new window


Only results in one record being returned, not one record for each Member?

Sorry CTEs are new to me.

Thanks

Thanks All
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38855831
correct that why you need the windowing /OVER functions

together with cte's perhaps
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:garethtnash
ID: 38855861
Great thanks Lowfatspread, looking forward to your example :)

Sorry guys I'm a keen novice when it comes to SQL, although I have learnt more from you guys than I did when i did the bootcamp :)
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 300 total points
ID: 38855873
you don't appear to have included enogh information about the order tables and how they link to the member

presumably there is an order table with the memberid on it ... and the order date

do we need to go down to the order detail table or is the order value summarised on the order table...?

what dates / statii are you looking for?
how are you defining the Last year  (calendar e.g just 2013 or last 12 months last 365 days...) ?

SELECT [ID],
 Title,
[FirstName],
[LastName],
[Birthdate],
 TradingAs,
[AccountType]
, PartnerCategory,
 RDA,
[PGA-Number],
[Exchequer-Code],
[Startdate],
[ExitDate],
[Profits],
[VAT-Liable],
Phone,
 Mobile,
 TGIEmail,
'AltEmail',
[URL],
facebook,
twitter,
[RetainerSurvey]
[Retail-Score],[VisitDate] 
from (
SELECT M.[ID],
CASE  M.[title] when 1 THEN 'Mr'
WHEN 2 THEN 'Mrs'
WHEN  3 THEN 'Miss'
WHEN 4 THEN 'Ms'
ELSE '' END AS Title,
M.[FirstName],
M.[LastName],
M.[Birthdate],
M.[ClubName] as TradingAs,
M.[AccountType],
CASE 
WHEN M.[UK-Member] = 'Y' THEN 'UK'
WHEN M.[Eire-Member] = 'Y' THEN 'Eire'
WHEN M.[EU-Member] = 'Y' THEN 'EU' END AS PartnerCategory,
RDA.FirstName + ' ' + RDA.LastName RDA,
M.[PGA-Number],
EX.[Exchequer-Code],
M.[Startdate],
M.[ExitDate],
M.[Profits],
M.[VAT-Liable],
P.Phone Phone,
MP.Phone Mobile,
TE.Email TGIEmail,
'AltEmail',
M.[URL],
PS.facebook,
PS.twitter,
M.[RetainerSurvey]
rv.[Retail-Score],rv.[VisitDate] 
,row_number() over (partition by m.id order by rv.visitdate desc) as VisitRN
,row_number() over (partition by m.id order by p.hone asc) as PhoneRN
,row_number() over (partition by m.id order by mp.phone asc) as MPhoneRN
FROM [dbo].[Member] M
Left join dbo.[Member-Exchequer-Codes] EX on EX.MemberID = M.ID
left join dbo.PartnerSocialMedia PS on PS.PartnerID = M.ID
left join (select P.MemberID, P.Phone from dbo.[Member-Phone] P Where Type = 1 AND DefaultP = 'Y' ) P on P.MemberID = M.ID
left join (select MP.MemberID, MP.Phone from dbo.[Member-Phone] MP Where Type = 2 ) MP on MP.MemberID = M.ID
left join (select TE.MemberID,TE.Email from dbo.[Member-Email] TE Where TE.[TGI-Email] = 'Y' AND TE.defaultemail = 'Y' ) TE on TE.MemberID = M.ID
left join dbo.[Admin] A on A.ID = M.[RDA-ID] left join dbo.Member RDA on RDA.ID = A.MID
left outer join [dbo].[RetailVisit] as rv 
 on rv. = m.id
) as x
where Visitrn=1 and phonern=1 and mphonern=1

order by id

Open in new window


gets a single phone and the last vist dates/score ...

in general you should have reference tables for the decodes of the title and country/territory information....
(ps... you probably have a reasonable proportion of Doctors and Parsons at least even before you start thinking military etc...)
0
 

Author Comment

by:garethtnash
ID: 38855982
Wow,

Thanks Lowfatspread,

I think I'm following your logic, I've updated this slightly

SELECT [ID],
 Title,
[FirstName],
[LastName],
[Birthdate],
 TradingAs,
[AccountType],
PartnerCategory,
 RDA,
[PGA-Number],
[Exchequer-Code],
[Startdate],
[ExitDate],
[Profits],
[VAT-Liable],
Phone,
 Mobile,
 TGIEmail,
'AltEmail',
[URL],
facebook,
twitter,
[RetainerSurvey],
[Retail-Score],
[VisitDate] 
from (
SELECT M.[ID],
CASE  M.[title] when 1 THEN 'Mr'
WHEN 2 THEN 'Mrs'
WHEN  3 THEN 'Miss'
WHEN 4 THEN 'Ms'
ELSE '' END AS Title,
M.[FirstName],
M.[LastName],
M.[Birthdate],
M.[ClubName] as TradingAs,
M.[AccountType],
CASE 
WHEN M.[UK-Member] = 'Y' THEN 'UK'
WHEN M.[Eire-Member] = 'Y' THEN 'Eire'
WHEN M.[EU-Member] = 'Y' THEN 'EU' END AS PartnerCategory,
RDA.FirstName + ' ' + RDA.LastName RDA,
M.[PGA-Number],
EX.[Exchequer-Code],
M.[Startdate],
M.[ExitDate],
M.[Profits],
M.[VAT-Liable],
P.Phone Phone,
MP.Phone Mobile,
TE.Email TGIEmail,
'AltEmail',
M.[URL],
PS.facebook,
PS.twitter,
M.[RetainerSurvey],
rv.[Retail-Score],
rv.[VisitDate] 
,row_number() over (partition by m.id order by rv.visitdate desc) as VisitRN
,row_number() over (partition by m.id order by p.hone asc) as PhoneRN
,row_number() over (partition by m.id order by mp.phone asc) as MPhoneRN
FROM [dbo].[Member] M
Left join dbo.[Member-Exchequer-Codes] EX on EX.MemberID = M.ID
left join dbo.PartnerSocialMedia PS on PS.PartnerID = M.ID
left join (select P.MemberID, P.Phone from dbo.[Member-Phone] P Where Type = 1 AND DefaultP = 'Y' ) P on P.MemberID = M.ID
left join (select MP.MemberID, MP.Phone from dbo.[Member-Phone] MP Where Type = 2 ) MP on MP.MemberID = M.ID
left join (select TE.MemberID,TE.Email from dbo.[Member-Email] TE Where TE.[TGI-Email] = 'Y' AND TE.defaultemail = 'Y' ) TE on TE.MemberID = M.ID
left join dbo.[Admin] A on A.ID = M.[RDA-ID] left join dbo.Member RDA on RDA.ID = A.MID
Left join (SELECT [MemberID], SUM([OrderSubTotal]) TotalDirectSpend FROM [dbo].[Ordertb] Where OrderStatus in (1,2) GROUP BY MemberID) TD on TD.MemberID = M.ID
LEFT JOIN (SELECT [MemberID], SUM([OrderSubTotal]) TotalDirectSpend FROM [dbo].[Ordertb] Where OrderStatus in (1,2) AND DATEPART(YYYY, OrderDate) = DATEPART(YYYY, GETDATE()) GROUP BY MemberID) CTD on CTD.MemberID = M.ID
left outer join [dbo].[RetailVisit] as rv 
 on rv.PartnerID = m.id
) as x
where Visitrn=1 and phonern=1 and mphonern=1
order by id

Open in new window


Hopefully this deals with the issue of hoe the order table is joined to the member table?

The 'Current Annual' is actually incorrect here, in that it should be within the companies financial year, which I have written elsewhere as --

SELECT ISNULL(SUM(O.OrderSubTotal),0.00) SubTotal FROM dbo.Ordertb O WHERE OrderDate >= ( select  case  when MONTH(GETDATE())>= F.Month AND DAY(GETDATE())>= F.Day  then convert(datetime, cast(year(getdate())-0 as varchar) +'-'+ cast(F.Month as varchar) +'-'+ cast(F.Day as varchar), 110) Else convert(datetime, cast(year(getdate())-1 as varchar) +'-'+ cast(F.Month as varchar) +'-'+ cast(F.Day as varchar), 110) End as FinanceDate From dbo.FinanceYear F) And O.MemberID = ?

Open in new window


I just need to think about how to do this......

what dates / statii are you looking for? ? can you clarify please?

Couple of questions..?

This -

left outer join [dbo].[RetailVisit] as rv 
 on rv.PartnerID = m.id
) as x

Open in new window


gave me

Msg 207, Level 16, State 1, Line 60
Invalid column name 'hone'.
Msg 8155, Level 16, State 2, Line 74
No column name was specified for column 19 of 'x'.

Also, does this -

where Visitrn=1 and phonern=1 and mphonern=1

Mean that rows will still be produced for members that have not been visited, don't have a mobile listed?

Thanks for all of the above...

:)
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38856055
typo line 60 above

,row_number() over (partition by m.id order by p.phone asc) as PhoneRN

sorry (col 19)
hadn't realised this was a literal
'AltEmail',
change it to
'AltEmail' as altEmail,

where Visitrn=1 and phonern=1 and mphonern=1

Mean that rows will still be produced for members that have not been visited, don't have a mobile listed?

yes because of the left outer joins at a minimum we get 1 row per "source table"



the question about dates and status was basically to with again you haven;t identified what dates you have and which you wanted to use are the calculations based on date ordered or date received/dispatched or actually paid...  you have a status on each order detail line so again which line are included do some statii mean returned/outstanding/cancelled etc
also do you want gross amount or net of any qualifying discounts (if you have them) and or is tax to be included or ignored...
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38856110
SELECT ISNULL(SUM(O.OrderSubTotal),0.00) SubTotal FROM dbo.Ordertb O WHERE OrderDate >= ( select  case  when MONTH(GETDATE())>= F.Month AND DAY(GETDATE())>= F.Day  then convert(datetime, cast(year(getdate())-0 as varchar) +'-'+ cast(F.Month as varchar) +'-'+ cast(F.Day as varchar), 110) Else convert(datetime, cast(year(getdate())-1 as varchar) +'-'+ cast(F.Month as varchar) +'-'+ cast(F.Day as varchar), 110) End as FinanceDate From dbo.FinanceYear F) And O.MemberID = ?

Open in new window


no i don't believe the above simplifies the situation as you still ignore order status
and i would expect the code to fail
1 ,... becase of the as financedate
2 ... because you use varchar and don't append leading zeros to the month./day components...

plus its overly complex...

tip  always try to format/deal with dates as YYYYMMDD strings (ISO format)  convert style 112
0
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 200 total points
ID: 38856125
re: only returning the top row is correct:
You have to use row_number and filter by that (been a while since I needed to use these myself)

;WITH MobileCTE (MemberID, Phone)
AS
-- Define the CTE query.
(
    SELECT memberID, Phone,
    ROW_NUMBER() over (partition by memberID order by Default desc) row
    from  [Member-Phone]
    where type = 'Mobile'
)


, RetailCTE  (PartnerId, Retail-Score, VisitDate)
as
(
    select partnerId, retail-score, visitdate,
    ROW_NUMBER() over (partition by partnerId order by visitdate desc) row
    from RetailVisit
)

in your select or join:

select * from retailCTE where row = 1;

select * from member m
join MobileCTE  mCTE on m.MemberId = mCTE.MemberId and row =1
0
 

Author Comment

by:garethtnash
ID: 38883611
Morning All,

Sorry for the late response, I've just managed to (almost completely) finish writing the SQL statement, (with the exception of sales value for current year). I've not yet looked at CTE's to do this, they do look a lot cleaner, but I need to look at examples, before I go with CTE's (ged325) If you could provide further detail that would be great.

Lowfatspread, thank you, for all of your input, its been great. and I think I've learnt some more SQL programming.

Here's the code as it stands, completed with the exception sales by financial year -

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[PartnerListComplete]
AS
SELECT [ID],
[Title],
[FirstName],
[LastName],
[Birthdate],
[TradingAs],
[Clubname],
[Add1],
[Add2],
[Town],
[County],
[Country],
[Postcode],
[ClubTypeID],
[ClubType],
[Tuition],
[AccountType],
[PartnerCategory],
[RDA],
[RDAID],
[PGA#],
[Exchequer],
[Startdate],
[ExitDate],
[Profits],
[VAT],
[Phone],
[Mobile],
[TGIEmail],
[AltEmail],
[URL],
[facebook],
[twitter],
[RetainerSurvey],
[RetailScore],
[VisitDate],
[RetainerValue],
[FeeCommission], 
[TotalRetainer],
[ReviewDate],
[RetainerDateCompleted],
[TotalDirectSpend],
[MYTGIUsername],
[MYTGIPassword],
[LastMYTGIVisit],
[LastOLPSVisit],
[LastEnewsVisit],
[LastTGIDirectVisit],
[LastPriceGuideVisit],
[LastForumVisit],
[EnewsActive],
[EnewsPackage],
[EnewsStartDate],
[EnewsExitDate],
[ORAFINANCEYEAR],
[ORACredit],
[ORARetail],
[ORATurnover],
[ORARanking],
[FivePointYear],
[FivePointTGIDirect],
[FivePointSupplierSurvey],
[FivePointRetainerSurvey],
[FivePointGolfshow],
[FivePointRegionalMeeting],
[CashDistribution],
[EPOSID],
[EPOSModel]
from (
SELECT M.[ID],
CASE  M.[title] when 1 THEN 'Mr'
WHEN 2 THEN 'Mrs'
WHEN  3 THEN 'Miss'
WHEN 4 THEN 'Ms'
ELSE '' END AS Title,
M.[FirstName],
M.[LastName],
Case 
When M.[Birthdate] = '01/01/1900' then NULL
when M.[Birthdate] IS NULL then NULL 
ELSE CONVERT(VARCHAR(10), M.[Birthdate], 103) END AS [Birthdate],
M.[ClubName] as TradingAs,
MA.ClubName as Clubname,
MA.Address1 as Add1,
MA.Address1 as Add2,
T.Town as Town,
R.Region as County,
C.Country as Country,
MA.PostCode as Postcode,
MA.[Club-Course-Type] ClubTypeID,
CT.[Type] ClubType,
MA.[Tuition-Available] Tuition,
M.[AccountType],
CASE 
WHEN M.[UK-Member] = 'Y' THEN 'UK'
WHEN M.[Eire-Member] = 'Y' THEN 'Eire'
WHEN M.[EU-Member] = 'Y' THEN 'EU' END AS PartnerCategory,
A.ID AS RDAID,
RDA.FirstName + ' ' + RDA.LastName RDA,
M.[PGA-Number] PGA#,
EX.[Exchequer-Code] Exchequer,
CONVERT(VARCHAR(10), M.[Startdate],103) AS Startdate,
CONVERT(VARCHAR(10), M.[ExitDate],103) as ExitDate,
M.[Profits],
M.[VAT-Liable] VAT,
P.Phone Phone,
MP.Phone Mobile,
TE.Email TGIEmail,
AE.Email as AltEmail,
M.[URL],
PS.facebook,
PS.twitter,
M.[RetainerSurvey],
rv.[Retail-Score] RetailScore,
CONVERT(VARCHAR(10), rv.[VisitDate],103) [VisitDate],
Ret.RetainerValue,
Ret.FeeCommission, 
Ret.TotalRetainer,
CONVERT(VARCHAR(10), Ret.ReviewDate, 103) ReviewDate,
CONVERT(VARCHAR(10), Ret.[Date],103) as RetainerDateCompleted,
row_number() over (partition by m.id order by AE.Email desc) as EmailRN,
row_number() over (partition by m.id order by rv.visitdate desc) as VisitRN,
row_number() over (partition by m.id order by p.phone asc) as PhoneRN,
row_number() over (partition by m.id order by mp.phone asc) as MPhoneRN,
ROW_NUMBER() over (partition by m.id order by ORA.[FinancialYearID] desc) as ORARN,
ROW_NUMBER() over (partition by m.id order by FivePoint.[FinancialYearID] desc) as FPointRN,
CASE WHEN TD.TotalDirectSpend IS NULL THEN '0.00' ELSE TD.TotalDirectSpend END as TotalDirectSpend,
MYTGI.Username MYTGIUsername,
MYTGI.[Password] MYTGIPassword,
CONVERT(VARCHAR(10), MYTGIV.VDate, 103) as LastMYTGIVisit,
CONVERT(VARCHAR(10), OLPSV.VDate, 103) as LastOLPSVisit,
CONVERT(VARCHAR(10), EnewsV.VDate, 103) as LastEnewsVisit,
CONVERT(VARCHAR(10), DirectV.VDate, 103) as LastTGIDirectVisit,
CONVERT(VARCHAR(10), PriceGV.VDate, 103) as LastPriceGuideVisit,
CONVERT(VARCHAR(10), ForumV.VDate, 103) as LastForumVisit,
CASE WHEN Enews.StartDate IS NOT NULL AND Enews.ExitDate IS NULL THEN 'Y' ELSE 'N' END AS EnewsActive,
Case
When Enews.EnewsPackage = 1 then 'Silver Unlimited'
When Enews.EnewsPackage = 2 then 'Gold Unlimited'
When Enews.EnewsPackage = 3 then 'Platinum Unlimited' END as EnewsPackage,
CONVERT(VARCHAR(10), Enews.StartDate, 103) EnewsStartDate,
CONVERT(VARCHAR(10), Enews.ExitDate, 103) EnewsExitDate,
ORA.FinancialYearID as ORAFINANCEYEAR,
ORA.CreditRanking ORACredit,
ORA.ORARanking ORARanking,
ORA.RetailRanking ORARetail,
ORA.TurnoverRanking ORATurnover,
FivePoint.FinancialYearID FivePointYear,
FivePoint.SupportedTGIDirect FivePointTGIDirect,
FivePoint.SupplierSurvey FivePointSupplierSurvey,
FivePoint.RetainerSurvey FivePointRetainerSurvey,
FivePoint.GolfShow FivePointGolfshow,
FivePoint.RegionalMeeting FivePointRegionalMeeting,
FivePoint.CashDistribution CashDistribution,
EPS.EPOSID,
EPS.EPOSModel
FROM [dbo].[Member] M
LEFT JOIN dbo.[Member-Exchequer-Codes] EX on EX.MemberID = M.ID
LEFT JOIN dbo.PartnerSocialMedia PS on PS.PartnerID = M.ID
LEFT JOIN(select P.MemberID, P.Phone from dbo.[Member-Phone] P Where Type = 1 AND DefaultP = 'Y' ) P on P.MemberID = M.ID
LEFT JOIN(select MP.MemberID, MP.Phone from dbo.[Member-Phone] MP Where Type = 2 ) MP on MP.MemberID = M.ID
LEFT JOIN(select TE.MemberID,TE.Email from dbo.[Member-Email] TE Where TE.[TGI-Email] = 'Y' AND TE.defaultemail = 'Y') TE on TE.MemberID = M.ID
LEFT JOIN(select AE.MemberID,AE.Email from dbo.[Member-Email] AE Where AE.[TGI-Email] <> 'Y' AND AE.defaultemail <> 'Y' ) AE on AE.MemberID = M.ID
LEFT JOIN dbo.[Admin] A on A.ID = M.[RDA-ID] 
LEFT JOIN dbo.Member RDA on RDA.ID = A.MID
LEFT JOIN(SELECT [MemberID], SUM([OrderSubTotal]) TotalDirectSpend FROM [dbo].[Ordertb] Where OrderStatus in (1,2) GROUP BY MemberID) TD on TD.MemberID = M.ID
LEFT JOIN(SELECT [MemberID], SUM([OrderSubTotal]) TotalDirectSpend FROM [dbo].[Ordertb] Where OrderStatus in (1,2) AND DATEPART(YYYY, OrderDate) = DATEPART(YYYY, GETDATE()) GROUP BY MemberID) CTD on CTD.MemberID = M.ID
LEFT JOIN(Select Ret.ID, Ret.MemberID, Ret.RetainerValue, Ret.FeeCommission, Ret.[Date], Ret.TotalRetainer, Ret.ReviewDate from dbo.RetainerSurvey Ret where Ret.Live = 'Y') Ret on Ret.MemberID = M.ID
LEFT JOIN(Select MA.MemberID, MA.ClubName, MA.Address1, MA.Address2, MA.PostCode, MA.Town, MA.Region, MA.Country, MA.[Club-Course-Type], MA.[Tuition-Available] from dbo.[Member-Address] MA where MA.[Default-Address] = 'Y') MA on MA.MemberID = M.ID
LEFT JOIN(SELECT MYTGI.[MemberID],MYTGI.[Username],MYTGI.[Password] FROM [dbo].[Member-Login] MYTGI Where MYTGI.ServiceID = 1) MYTGI on MYTGI.MemberID = M.ID
LEFT JOIN(Select MYTGIV.MemberID, MAX(MYTGIV.VisitDate) VDate from dbo.[Service-Visit-History] MYTGIV Where MYTGIV.ServiceID = 1 Group BY MYTGIV.MemberID) MYTGIV on MYTGIV.MemberID = M.ID
LEFT JOIN(Select OLPSV.MemberID, MAX(OLPSV.VisitDate) VDate from dbo.[Service-Visit-History] OLPSV Where OLPSV.ServiceID = 2 Group BY OLPSV.MemberID) OLPSV on OLPSV.MemberID = M.ID
LEFT JOIN(Select EnewsV.MemberID, MAX(EnewsV.VisitDate) VDate from dbo.[Service-Visit-History] EnewsV Where EnewsV.ServiceID = 3 Group BY EnewsV.MemberID) EnewsV on EnewsV.MemberID = M.ID
LEFT JOIN(Select DirectV.MemberID, MAX(DirectV.VisitDate) VDate from dbo.[Service-Visit-History] DirectV Where DirectV.ServiceID = 4 Group BY DirectV.MemberID) DirectV on DirectV.MemberID = M.ID
LEFT JOIN(Select PriceGV.MemberID, MAX(PriceGV.VisitDate) VDate from dbo.[Service-Visit-History] PriceGV Where PriceGV.ServiceID = 5 Group BY PriceGV.MemberID) PriceGV on PriceGV.MemberID = M.ID
LEFT JOIN(Select ForumV.MemberID, MAX(ForumV.VisitDate) VDate from dbo.[Service-Visit-History] ForumV Where ForumV.ServiceID = 6 Group BY ForumV.MemberID) ForumV on ForumV.MemberID = M.ID
LEFT JOIN(SELECT Enews.[MID], Enews.[StartDate], Enews.[ExitDate], Enews.[EnewsPackage] FROM [dbo].[MemberEnews] Enews) Enews on Enews.MID = M.ID 
LEFT JOIN(SELECT ORA.[PartnerID], FY.[Year] as FinancialYearID, ORA.[CreditRanking], ORA.[RetailRanking], ORA.[TurnoverRanking], ORA.[ORARanking] FROM [dbo].[PartnerORARanking] ORA inner join dbo.FinancialYear FY on ORA.FinancialYearID = FY.ID) ORA on ORA.PartnerID = M.ID
LEFT JOIN(SELECT PE.[PartnerID],PE.[EPOSID], ES.EPOSModel FROM [dbo].[PartnerEposModel] PE INNER JOIN dbo.EPOSSys ES on PE.EPOSID = ES.ID) EPS on EPS.PartnerID = M.ID
LEFT JOIN(SELECT FivePoint.[PartnerID], FY.[Year] as FinancialYearID, FivePoint.[GolfShow], FivePoint.[RegionalMeeting], FivePoint.[SupplierSurvey], FivePoint.[RetainerSurvey], FivePoint.[SupportedTGIDirect], FivePoint.[CashDistribution] FROM [dbo].[PartnerFivePointCriteria] FivePoint inner join dbo.FinancialYear FY on FivePoint.FinancialYearID = FY.ID) FivePoint on FivePoint.PartnerID = M.ID
inner join (select T.ID, T.Town from dbo.Town T) T on T.ID = MA.Town
Inner join (Select R.ID, R.Region from dbo.Region R) R on R.ID = MA.Region 
inner join (select C.ID, C.Country from dbo.Country C) C on C.ID = MA.Country
LEFT JOIN(select CT.ID, CT.[Type] from dbo.[Club-Course-Type] CT) CT on CT.ID = MA.[Club-Course-Type]
left outer join [dbo].[RetailVisit] as rv 
 on rv.PartnerID = m.id
 Where M.AccountType in (1,2,3,4,5,6,7,9)
)as x
where Visitrn=1 and phonern=1 and mphonern=1
GO

Open in new window


I'm awarding points 60/40,

Thank you both
0
 

Author Closing Comment

by:garethtnash
ID: 38883613
Thank you
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 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

12 Experts available now in Live!

Get 1:1 Help Now