SQL Server 2014
I am trying to learn better SQL code practices to improve performance. I am inheriting some old code, and wanted to get an idea of how someone who actually knows what they're doing would improve it.
I have tried to shorten the code to just the main part I'm working on right now. It seems like there is a better way to get the Reason Codes (PersonalTraining, Tennis, etc.) than running multiple Correlated Subqueries? But, so far I'm having trouble getting the results to return on one row per customer.
The final results must stay the same for a 3rd party.
A customer can have zero to multiple Reason Codes that are stored in the table "asajoinmbr"
Table asajoinmbr:
CREATE TABLE [dbo].[ASAJoinmbr](
[cust_code] [char](10) NOT NULL,
[mbr_code] [char](10) NOT NULL,
[reason_code] [char](3) NULL,
[associate_code] [char](10) NULL,
[join_note] [ntext] NULL,
[wants_contact] [char](1) NULL,
[club] [smallint] NULL,
[region] [char](4) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Table asamembr:
CREATE TABLE [dbo].[ASAMembr](
[cust_code] [nvarchar](10) NULL,
[mbr_code] [nvarchar](10) NULL,
[lname] [nvarchar](20) NULL,
[fname] [nvarchar](20) NULL,
[status] [nvarchar](6) NULL,
[bdate] [datetime] NULL,
[email] [nvarchar](80) NULL,
[Club] [nvarchar](3) NULL,
) ON [PRIMARY]
Table strcustr:
CREATE TABLE [dbo].[StrCustr](
[cust_code] [nvarchar](10) NULL,
[bridge_code] [nvarchar](10) NULL,
[bus_name] [nvarchar](30) NULL,
[status] [nvarchar](1) NULL,
[phone] [nvarchar](20) NULL,
[address1] [nvarchar](30) NULL,
[address2] [nvarchar](30) NULL,
[city] [nvarchar](20) NULL,
[state] [nvarchar](10) NULL,
[post_code] [nvarchar](10) NULL,
[cntry_abbrev] [nvarchar](3) NULL,
[cust_type] [nvarchar](10) NULL,
[obtained_date] [date] NULL,
[geo_code] [nvarchar](9) NULL,
[email] [nvarchar](80) NULL,
[club] [smallint] NULL,
) ON [PRIMARY]
GO
Any thoughts on improving any part of this code?
select
SC.Club as [Location],
(LTRIM(RTRIM(SC.Cust_Code))+Mem.Mbr_code) as [Customer Number],
Replace(LTRIM(RTRIM(mem.fname)),'"','-') as [Customer First Name],
LTRIM(RTRIM(mem.lname)) as [Customer Last Name],
mem.email as [Customer Email Address],
(select 'Personal Training' from ASAJoinmbr ajm where sc.cust_code =
ajm.cust_code and reason_code = 'PT' group by cust_code) as PersonalTraining,
(select 'Group Fitness' from ASAJoinmbr ajm where sc.cust_code =
ajm.cust_code and (reason_code = 'GE' or reason_code = 'GF') group by
cust_code) as GroupFitness,
(select 'Cardio/Weight'from ASAJoinmbr ajm where sc.cust_code =
ajm.cust_code and (reason_code = 'CT' or reason_code = 'CV' or reason_code =
'WT') group by cust_code) as CardioWeight,
(select 'Tennis'from ASAJoinmbr ajm where sc.cust_code = ajm.cust_code and
(reason_code = 'TE' or reason_code = 'TN') group by cust_code) as Tennis,
(select 'Aquatics' from ASAJoinmbr ajm where sc.cust_code = ajm.cust_code
and reason_code = 'AQ' group by cust_code) as Aquatics
from strcustr SC
INNER JOIN ASAMEMBR MEM
on SC.cust_code=MEM.Cust_code
and sc.club=mem.Club
Where
DATEDIFF(year,(mem.bdate),GETDATE() ) >19
and (DATEDIFF(day, convert(date,sc.obtained_date,101),GETDATE() )= 14
or (DATEDIFF(day, convert(date,sc.obtained_date,101),GETDATE() )=93
and sc.status='A'))
and mem.status = 'A'
and mem.email is not null
and mem.email <>''
and (sc.bridge_code <> '94811' or sc.geo_code <> 'Goldsmbr')
and sc.cust_type<>'E'
group by
sc.club,sc.cust_code,mem.mbr_code,mem.fname,mem.lname,mem.email
Results:
