The Following SQL Statement Can Be Used to Ascertain Figures for the SB02, SC02 and SD02

The following SQL Statement can be used to ascertain figures for the SB02, SC02 and SD02 figures on the RMAR-J report that we don’t currently provide in any of the reporting modules.

This version of the SQL Statement is for Access databases and is based on the Sales Account. It shows the payaways to Introducing Firms.

To choose the Accounting Period range you will need to replace the pairs of numbers in blue with values that relate to the accounting period that you are searching across.

Please pay attention to the parts of the statement written in blue. These are to be amended to reflect the accounting periods. To choose your accounting period, each blue number is represented by two parts. The last two numbers represent the period in the year. The first number or numbers represent the year itself. 16 means 2011/2012, 17 means 2012/2013, 18 means 2013/2014 and so on.

The pairs of numbers should be consistent all through the document.

Having altered the dates, please highlight the text below in its entirety. Then right click over the highlighted part and choose Copy. You can then open Query Builder, choose Search, Execute SQL and hit Paste. The entire statement will be pasted into the SQL Statement window. Then hit ‘Run’ and the report will take a few seconds to compile.

SELECT 'Direct Policy Remuneration' AS [Income Type], Sources.FullName AS [Intro Firm],

IIF(IIF(ISNULL(Policies.Owner), 0, Policies.Owner) = 0, IIF(ISNULL(Clients.PartnerSurname), Clients.Surname + ' - partner', Clients.PartnerSurname), Clients.Surname) AS [Surname],

IIF(IIF(ISNULL(Policies.Owner), 0, Policies.Owner) = 0, IIF(ISNULL(Clients.PartnerForenames), Clients.Forenames + ' - partner', Clients.PartnerForenames), Clients.Forenames) AS [Forenames],

IIF(IIF(ISNULL(Policies.Owner), 0, Policies.Owner) = 0, IIF(ISNULL(Clients.PartnerPostcode), Clients.HomePostcode, Clients.PartnerPostcode), Clients.HomePostcode) AS [Postcode],

IIF(ISNULL(Policies.PolicyNumber), 'PR' + Policies.ProposalRef, Policies.PolicyNumber) AS [Policy Number], Schemes.SchemeName,

Debtors.Amount AS [Posted], Debtors.DatePosted AS [Date Posted],

IIF(Schemes.RMARJ_SectionC = 0, 'Blank',

IIF(Schemes.RMARJ_SectionC = 1, 'Excluded',

IIF(Schemes.RMARJ_SectionC = 2, 'SE02',

IIF(Schemes.RMARJ_SectionC = 3, 'SB02',

IIF(Schemes.RMARJ_SectionC = 4, 'SC02',

IIF(Schemes.RMARJ_SectionC = 5, 'SD02', '')))))) AS [RMAR J Category],

IIF(Sources.RegulatedFirm = -1, 'Yes’ , 'No’) AS [Regulated],

IIF (Schemes.SchemeType = 1, 'Investment Bond',

IIF (Schemes.SchemeType = 2, 'Savings / Protection plans',

IIF (Schemes.SchemeType = 3, 'Individual Pension plans',

IIF (Schemes.SchemeType = 4, 'Group Pension plans',

IIF (Schemes.SchemeType IN (5, 15), 'PEPs / ISAs',

IIF (Schemes.SchemeType = 6, 'Guaranteed Contracts',

IIF (Schemes.SchemeType = 7, 'Group Protection plans',

IIF (Schemes.SchemeType = 8, 'Regular Investment plans',

IIF (Schemes.SchemeType = 9, 'Managed Portfolios',

IIF (Schemes.SchemeType = 10, 'Investments',

IIF (Schemes.SchemeType = 11, 'Pensions in payment',

IIF (Schemes.SchemeType = 12, 'Cash investments',

IIF (Schemes.SchemeType = 13, 'Long term care',

IIF (Schemes.SchemeType = 14, 'General Insurance', 'Unknown')))))))))))))) AS [SchemeTypeName]

FROM (((((((Debtors LEFT JOIN CommissionPayaways ON (Debtors.FKeyRef = CommissionPayaways.CommPayRef AND CommissionPayaways.FKeyType = 1 AND CommissionPayaways.Type = 3))

LEFT JOIN Sources ON CommissionPayaways.SourceRef = Sources.SourceRef)

LEFT JOIN CommnEntries ON CommissionPayaways.FKeyRef = CommnEntries.CommnEntryRef)

LEFT JOIN CommnPremiums ON CommnEntries.CommnPremRef = CommnPremiums.CommnPremRef)

LEFT JOIN Policies ON CommnPremiums.PolicyRef = Policies.PolicyRef)

LEFT JOIN Schemes ON Policies.SchemeRef = Schemes.SchemeRef)

LEFT JOIN Clients ON Policies.ClientRef = Clients.ClientRef)

WHERE Debtors.FKeyType = 2

AND Debtors.AccountingPeriod BETWEEN 1701 AND 1712

AND CommissionPayaways.Type = 3

UNION

SELECT 'Direct Portfolio Remuneration' AS [Income Type], Sources.FullName AS [Intro Firm],

IIF(IIF(ISNULL(FundHoldings.Owner), 0, FundHoldings.Owner) = 0, IIF(ISNULL(Clients.PartnerSurname), Clients.Surname + ' - partner', Clients.PartnerSurname), Clients.Surname) AS [Surname],

IIF(IIF(ISNULL(FundHoldings.Owner), 0, FundHoldings.Owner) = 0, IIF(ISNULL(Clients.PartnerForenames), Clients.Forenames + ' - partner', Clients.PartnerForenames), Clients.Forenames) AS [Forenames],

IIF(IIF(ISNULL(FundHoldings.Owner), 0, FundHoldings.Owner) = 0, IIF(ISNULL(Clients.PartnerPostcode), Clients.HomePostcode, Clients.PartnerPostcode), Clients.HomePostcode) AS [Postcode],

Transactions.ContractRef AS [Policy Number], UnitPrices.UnitName,

Debtors.Amount AS [Posted], Debtors.DatePosted AS [Date Posted],

'SD02' AS [RMAR J Category],

IIF(Sources.RegulatedFirm = -1, 'Yes’ , 'No’) AS [Regulated],

'' as [SchemeTypeName]

FROM (((((((Debtors LEFT JOIN CommissionPayaways ON (Debtors.FKeyRef = CommissionPayaways.CommPayRef AND CommissionPayaways.FKeyType = 3 AND CommissionPayaways.Type = 3))

LEFT JOIN Sources ON CommissionPayaways.SourceRef = Sources.SourceRef)

LEFT JOIN PortfolioCommissions ON CommissionPayaways.FKeyRef = PortfolioCommissions.PortfolioCommnRef)

LEFT JOIN Transactions ON PortfolioCommissions.TransactionRef = Transactions.TransactionRef)

LEFT JOIN FundHoldings ON Transactions.FundHoldRef = FundHoldings.FundHoldRef)

LEFT JOIN UnitPrices ON FundHoldings.UnitPriceRef = UnitPrices.UnitPriceRef)

LEFT JOIN Clients ON FundHoldings.FKeyRef = Clients.ClientRef)

WHERE Debtors.FKeyType = 2

AND Debtors.AccountingPeriod BETWEEN 1701 AND 1712

AND FundHoldings.FKeyType = 1

AND CommissionPayaways.Type = 3

UNION

SELECT 'Direct Portfolio Remuneration' AS [Income Type], Sources.FullName AS [Intro Firm],

IIF(IIF(ISNULL(Policies.Owner), 0, Policies.Owner) = 0, IIF(ISNULL(Clients.PartnerSurname), Clients.Surname + ' - partner', Clients.PartnerSurname), Clients.Surname) AS [Surname],

IIF(IIF(ISNULL(Policies.Owner), 0, Policies.Owner) = 0, IIF(ISNULL(Clients.PartnerForenames), Clients.Forenames + ' - partner', Clients.PartnerForenames), Clients.Forenames) AS [Forenames],

IIF(IIF(ISNULL(Policies.Owner), 0, Policies.Owner) = 0, IIF(ISNULL(Clients.PartnerPostcode), Clients.HomePostcode, Clients.PartnerPostcode), Clients.HomePostcode) AS [Postcode],

Transactions.ContractRef AS [Policy Number], UnitPrices.UnitName + ' held under ' + Schemes.SchemeName,

Debtors.Amount AS [Posted], Debtors.DatePosted AS [Date Posted],

'SD02' AS [RMAR J Category],

IIF(Sources.RegulatedFirm = -1, 'Yes’ , 'No’) AS [Regulated],

IIF (Schemes.SchemeType = 1, 'Investment Bond',

IIF (Schemes.SchemeType = 2, 'Savings / Protection plans',

IIF (Schemes.SchemeType = 3, 'Individual Pension plans',

IIF (Schemes.SchemeType = 4, 'Group Pension plans',

IIF (Schemes.SchemeType IN (5, 15), 'PEPs / ISAs',

IIF (Schemes.SchemeType = 6, 'Guaranteed Contracts',

IIF (Schemes.SchemeType = 7, 'Group Protection plans',

IIF (Schemes.SchemeType = 8, 'Regular Investment plans',

IIF (Schemes.SchemeType = 9, 'Managed Portfolios',

IIF (Schemes.SchemeType = 10, 'Investments',

IIF (Schemes.SchemeType = 11, 'Pensions in payment',

IIF (Schemes.SchemeType = 12, 'Cash investments',

IIF (Schemes.SchemeType = 13, 'Long term care',

IIF (Schemes.SchemeType = 14, 'General Insurance', 'Unknown')))))))))))))) AS [SchemeTypeName]

FROM (((((((((Debtors LEFT JOIN CommissionPayaways ON (Debtors.FKeyRef = CommissionPayaways.CommPayRef AND CommissionPayaways.FKeyType = 3 AND CommissionPayaways.Type = 3))

LEFT JOIN Sources ON CommissionPayaways.SourceRef = Sources.SourceRef)

LEFT JOIN PortfolioCommissions ON CommissionPayaways.FKeyRef = PortfolioCommissions.PortfolioCommnRef)

LEFT JOIN Transactions ON PortfolioCommissions.TransactionRef = Transactions.TransactionRef)

LEFT JOIN FundHoldings ON Transactions.FundHoldRef = FundHoldings.FundHoldRef)

LEFT JOIN UnitPrices ON FundHoldings.UnitPriceRef = UnitPrices.UnitPriceRef)

LEFT JOIN Policies ON FundHoldings.FKeyRef = Policies.PolicyRef)

LEFT JOIN Clients ON Policies.PolicyRef = Clients.ClientRef)

LEFT JOIN Schemes ON Policies.SchemeRef = Schemes.SchemeRef)

WHERE Debtors.FKeyType = 2

AND Debtors.AccountingPeriod BETWEEN 1701 AND 1712

AND FundHoldings.FKeyType IN (2, 3)

AND CommissionPayaways.Type = 3

UNION

SELECT 'Fee Linked Remuneration' AS [Income Type], Sources.FullName AS [Intro Firm],

IIF(IIF(ISNULL(Fees.Owner), 0, Fees.Owner) = 0, IIF(ISNULL(Clients.PartnerSurname), Clients.Surname + ' - partner', Clients.PartnerSurname), Clients.Surname) AS [Surname],

IIF(IIF(ISNULL(Fees.Owner), 0, Fees.Owner) = 0, IIF(ISNULL(Clients.PartnerForenames), Clients.Forenames + ' - partner', Clients.PartnerForenames), Clients.Forenames) AS [Forenames],

IIF(IIF(ISNULL(Fees.Owner), 0, Fees.Owner) = 0, IIF(ISNULL(Clients.PartnerPostcode), Clients.HomePostcode, Clients.PartnerPostcode), Clients.HomePostcode) AS [Postcode],

CStr(Fees.FeeRef) AS [Policy Number], Fees.Title,

ROUND((FeeLinks.Perc * Debtors.Amount) * 0.01, 2) AS [Posted], Debtors.DatePosted AS [Date Posted],

IIF(Schemes.RMARJ_SectionC = 0, 'Blank',

IIF(Schemes.RMARJ_SectionC = 1, 'Excluded',

IIF(Schemes.RMARJ_SectionC = 2, 'SE02',

IIF(Schemes.RMARJ_SectionC = 3, 'SB02',

IIF(Schemes.RMARJ_SectionC = 4, 'SC02',

IIF(Schemes.RMARJ_SectionC = 5, 'SD02', '')))))) AS [RMAR J Category],

IIF(Sources.RegulatedFirm = -1, 'Yes’ , 'No’) AS [Regulated],

IIF (Schemes.SchemeType = 1, 'Investment Bond',

IIF (Schemes.SchemeType = 2, 'Savings / Protection plans',

IIF (Schemes.SchemeType = 3, 'Individual Pension plans',

IIF (Schemes.SchemeType = 4, 'Group Pension plans',

IIF (Schemes.SchemeType IN (5, 15), 'PEPs / ISAs',

IIF (Schemes.SchemeType = 6, 'Guaranteed Contracts',

IIF (Schemes.SchemeType = 7, 'Group Protection plans',

IIF (Schemes.SchemeType = 8, 'Regular Investment plans',

IIF (Schemes.SchemeType = 9, 'Managed Portfolios',

IIF (Schemes.SchemeType = 10, 'Investments',

IIF (Schemes.SchemeType = 11, 'Pensions in payment',

IIF (Schemes.SchemeType = 12, 'Cash investments',

IIF (Schemes.SchemeType = 13, 'Long term care',

IIF (Schemes.SchemeType = 14, 'General Insurance', 'Unknown')))))))))))))) AS [SchemeTypeName]

FROM (((((((Debtors LEFT JOIN CommissionPayaways ON (Debtors.FKeyRef = CommissionPayaways.CommPayRef AND CommissionPayaways.FKeyType = 2 AND CommissionPayaways.Type = 3))

LEFT JOIN Sources ON CommissionPayaways.SourceRef = Sources.SourceRef)

LEFT JOIN Fees ON CommissionPayaways.FKeyRef = Fees.FeeRef)

LEFT JOIN Clients ON Fees.ClientRef = Clients.ClientRef)

LEFT JOIN FeeLinks ON (Fees.FeeRef = FeeLinks.FeeRef AND FeeLinks.FKeyType IN (1,3)))

LEFT JOIN Policies ON FeeLinks.FKeyRef = Policies.PolicyRef)

LEFT JOIN Schemes ON Policies.SchemeRef = Schemes.SchemeRef)

WHERE Debtors.FKeyType = 2

AND Debtors.AccountingPeriod BETWEEN 1701 AND 1712

AND CommissionPayaways.Type = 3

AND FeeLinks.Perc > 0