Full query here:
SELECT GenderIsMale,
Count(GenderIsMale) as Sex,
SUM(CASE When Age Between 18 and 25 Then 1 Else 0 End) As Age1825,
SUM(CASE When Age Between 26 and 35 Then 1 Else 0 End) As Age2635,
SUM(CASE When Age Between 36 and 50 Then 1 Else 0 End) As Age3650,
SUM(CASE When Age Between 51 and 65 Then 1 Else 0 End) As Age5165,
SUM(CASE When Age >65 Then 1 Else 0 End) As Age65plus,
SUM(CASE When CountryName = 'Scotland' Then 1 Else 0 End) As Scotland,
SUM(CASE When CountryName = 'Wales' Then 1 Else 0 End) As Wales,
SUM(CASE When CountryName = 'Northern Ireland' Then 1 Else 0 End) As NIreland,
SUM(CASE When CountryName = 'England' and EnglandRegion ='NE' Then 1 Else 0 End) As NEast,
SUM(CASE When CountryName = 'England' and EnglandRegion ='NW' Then 1 Else 0 End) As NWest,
SUM(CASE When CountryName = 'England' and EnglandRegion ='EM' Then 1 Else 0 End) As EMidlands,
SUM(CASE When CountryName = 'England' and EnglandRegion ='WM' Then 1 Else 0 End) As WMidlands,
SUM(CASE When CountryName = 'England' and EnglandRegion ='SE' Then 1 Else 0 End) As SEast,
SUM(CASE When CountryName = 'England' and EnglandRegion ='SW' Then 1 Else 0 End) As SWest,
SUM(CASE When CountryName = 'England' and EnglandRegion IS NULL Then 1 Else 0 End) As GLondon
FROM (
SELECT u.PKID,
u.EmailAddress,
u.Title,
u.FirstName,
u.LastName,
ud.Address1,
ud.Address2,
ud.Address3,
ud.PostTown,
ud.County,
CountryName,
ud.PostCode,
DATEDIFF(hour,ud.DateOfBirth,GETDATE())/8766 AS Age,
reg.RegistrationDateTime,
ud.GenderIsMale,
--ROW_NUMBER() OVER (PARTITION BY ud.Address1, ud.Address2, ud.PostTown, ud.County, ud.PostCode ORDER BY u.PKID DESC) AS DedupeNumberPostalAddress,
ROW_NUMBER() OVER (PARTITION BY Telephone.TelephoneNumber ORDER BY u.PKID DESC) AS DedupeNumberTelephone
FROM PrizePunch..DataSalesUser
INNER JOIN PrizePunch.dbo.PrizePunchUser u WITH(NOLOCK)
ON u.PKID = DataSalesUser.UserID
INNER JOIN PrizePunch.dbo.PrizePunchUserDetail ud WITH(NOLOCK)
ON ud.UserID = u.PKID
INNER JOIN PrizePunch.dbo.PrizePunchUserRegistration reg WITH(NOLOCK)
ON reg.UserID = u.PKID
INNER JOIN PrizePunch..UserEmailSubscription WITH(NOLOCK)
ON UserEmailSubscription.UserID = u.PKID
AND UserEmailSubscription.EmailSubscriptionID = 2 -- Third party email subscription
INNER JOIN (
SELECT TelephoneNumber, UserID
FROM (
SELECT TelephoneNumber, UserID, RANK() OVER(PARTITION BY UserID ORDER BY UpdatedDateTime DESC) AS [Recency]
FROM PrizePunch..PrizePunchUserTelephone
WHERE Deleted = 0
AND CleanseResult = 1
AND ValidationStatusID = 2
--AND TelephoneTypeID = 2 --2=Mobile Number
) sub
WHERE Recency = 1
GROUP BY TelephoneNumber, UserID
) telephone
ON telephone.UserID = u.PKID
INNER JOIN UserListTransfer..PostcodeInfo
ON ud.PostCode LIKE PostcodeInfo.Incode + '%'
WHERE
u.Unsubscribed = 0
AND ISNULL(reg.ThirdPartyOptOut, 0) = 0
-- Cleansing
AND ud.Address1 != 'Address'
AND ud.Address1 != '10 DOWNING STREET'
AND OptInIP NOT IN ('103.4.16.137', '178.32.240.210', '103.4.18.71', '178.32.240.205', '185.7.149.234')
-- UK
AND u.MemberbaseID = 1
-- With name
AND u.NameCleanseResult = 1
-- With address
AND LEN(ud.Address1) > 1
AND LEN(ud.Postcode) > 1
AND LEN(ud.PostTown) > 1
) data
WHERE DedupeNumberTelephone = 1
GROUP BY GenderIsMale
Full error message:
Msg 207, Level 16, State 1, Line 15
Invalid column name 'EnglandRegion'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'EnglandRegion'.
Msg 207, Level 16, State 1, Line 17
Invalid column name 'EnglandRegion'.
Msg 207, Level 16, State 1, Line 18
Invalid column name 'EnglandRegion'.
Msg 207, Level 16, State 1, Line 19
Invalid column name 'EnglandRegion'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'EnglandRegion'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'EnglandRegion'.