Hi,
I'm quite new to SQL so still learning efficient ways to do things. I've constructed the following SQL which returns the results I want but I can't help think there has to be a more efficient way of achieving the desired results. I had to add the 'Sum(cpay.AmountToPay) as 'Payaways'' line which caused multiple errors until I added the Group by for all the fields I wanted to select. Up to that point I didn't need to reference the CommisionPayaways table, which may have multiple records for a give CommnEntries row.
Could anyone educatee me as to how I could add the sum() witout all the group by entries.
Many thanks
PS Using SQL Server Management Studio 2005
SQL:
selectcp.commnpremref,cons.Fullname as 'Consultant',case when cp.ExecutionOnly = 0 then 'Unknown' when cp.ExecutionOnly = 1 then 'Advice Given' when cp.ExecutionOnly = 2 then 'Execution Only' when cp.ExecutionOnly = 3 then 'Direct Offer' when cp.ExecutionOnly = 4 then 'Limited Advice' when cp.ExecutionOnly = 5 then 'Contrary to Advice' when cp.ExecutionOnly = 6 then 'Non Transacted by you' when cp.ExecutionOnly = 7 then 'Discretionary' when cp.ExecutionOnly = 8 then 'Other' when cp.ExecutionOnly = 9 then 'Pre-selected Option' when cp.ExecutionOnly = 10 then 'Less than 25% increase' when cp.ExecutionOnly = 11 then 'non Advised Sale'end as 'Advice Given',ce.TotalDue as 'Remuneration to you',case when p.owner = 100 then c.surnameelse c.partnerSurnameend as 'ClientSurname',Case when cp.replaceexisting = 1 then 'Y' when cp.replaceexisting = 0 then 'N'end as 'Replace Existing',case when p.owner = 100 then c.Initialselse c.partnerInitialsend as 'ClientInitials',p.policynumber as 'Policy Num',cp.amount as 'Premium',Case when cp.submitted = 1 then 'Y' when cp.submitted = 0 then 'N'end as 'Submitted',convert(varchar(10),cp.DateSubmitted,103) as 'Date Submitted',s.schemename as 'Scheme Name',case when s.schemetype = 1 then 'Bonds' when s.schemetype = 2 then 'Savings/Protection Plan' when s.schemetype = 3 then 'Individual Pension Plan' when s.schemetype = 4 then 'Group Pension Plan' when s.schemetype = 5 then 'Personal Equity Plan' when s.schemetype = 6 then 'Guaranateed Contract' when s.schemetype = 7 then 'Group Protection Plan' when s.schemetype = 8 then 'Regular Investment Plan' when s.schemetype = 9 then 'Managed Portfolio' when s.schemetype = 10 then 'Investments' when s.schemetype = 11 then 'Pensions in Payment' when s.schemetype = 12 then 'Cash Investment' when s.schemetype = 13 then 'Long Term Care' when s.schemetype = 14 then 'General Insurance' when s.schemetype = 15 then 'Individual Savings Account'end as 'Scheme Type',Case when cp.onrisk = 1 then 'Y' when cp.onrisk = 0 then 'N'end as 'On Risk',case when (cp.onrisk = 1 and cp.status = -1) then 'Current' when (cp.onrisk = 1 and cp.status = 0) then 'Paid Up' when (cp.onrisk = 1 and cp.status = 4) then 'Retired' when (cp.onrisk = 1 and cp.status = 5) then 'Refunded' when (cp.onrisk = 1 and cp.status = 6) then 'Transferred' when (cp.onrisk = 1 and cp.status = 9) then 'Lapsed' when (cp.onrisk = 1 and cp.status = 11) then 'Left Service' when (cp.onrisk = 1 and cp.status = 12) then 'In Claim' when (cp.onrisk = 1 and cp.status = 14) then 'Death Claim' when (cp.onrisk = 0 and cp.status = -1) then 'Proposed' when (cp.onrisk = 0 and cp.status = 8) then 'Not Taken Up' when (cp.onrisk = 0 and cp.status = 10) then 'Rejected' else 'Check why No Status'end as 'Status',m.fullname as 'Provider',convert(varchar(10),cp.proposaldate,103) as 'Proposal Date',case when cp.type = 1 then 'Initial Premium' when cp.type = 2 then 'Increment' when cp.type = 3 then 'Single Premium' when cp.type = 4 then 'Annual Renewal' when cp.type = 5 then 'Value Based' when cp.type = 6 then 'Protected Rights' when cp.type = 7 then 'Transfer' when cp.type = 8 then 'Switch' when cp.type = 9 then 'Adviser Charge'end as 'Comm Type',case when cp.SourceOfBusiness = 1 then 'Telephone Sale' when cp.SourceOfBusiness = 2 then 'Sales Visit' when cp.SourceOfBusiness = 3 then 'Postal Sale' when cp.SourceOfBusiness = 4 then 'Direct Offer' when cp.SourceOfBusiness = 5 then 'Internet Sale' when cp.SourceOfBusiness = 6 then 'Marketing Lists' when cp.SourceOfBusiness = 7 then 'Referrals from Non-Authorised Introducers' when cp.SourceOfBusiness = 8 then 'Referrals from Intermediaries' when cp.SourceOfBusiness = 9 then 'Cold Calling' when cp.SourceOfBusiness = 10 then 'Repeat Customers' when cp.SourceOfBusiness = 11 then 'Other' when cp.SourceOfBusiness is NULL then 'Unknown' else 'Check data'end as 'Source of Business',case when ce.type = 1 then 'Non-Indemnity' when ce.type = 2 then 'Indemnity' when ce.type = 3 then 'Initial Premium' when ce.type = 4 then 'Level' when ce.type = 5 then 'Value Based (Trail)' when ce.type = 6 then 'Level (Indemnity)' when ce.type = 7 then 'Adviser Charge' when ce.type is NULL then 'Adviser Charge'end as 'Entry Type',case when cp.TransactionMethod = 0 then 'Unknown' when cp.TransactionMethod = 1 then 'Face to face Meeting' when cp.TransactionMethod = 2 then 'Correspondence Only' when cp.TransactionMethod = 3 then 'Telephone Sale' when cp.TransactionMethod = 4 then 'Internet Sale' when cp.TransactionMethod = 5 then 'Email Purchase Request' when cp.TransactionMethod = 6 then 'Printed Media Campaign' when cp.TransactionMethod = 7 then 'Other'end as 'Conducted By',u.Fullname as 'Created By',ce.TotalReceived as 'Amount Received',Case when ce.settled = 1 then 'Y' when ce.settled = 0 then 'N'end as 'Settled',convert(varchar(10),cp.DateCreated,103) as 'Date Created',Sum(cpay.AmountToPay) as 'Payaways'from commnPremiums as cpleft join commnEntries as ce on cp.commnPremRef = ce.commnPremRefleft join users as u on cp.usercreated = u.userrefleft join policies as p on cp.policyRef = p.policyRefleft join clients as c on p.clientRef = c.clientRefleft join consultants as cons on c.consultantRef = cons.consultantRefleft join schemes as s on p.schemeRef = s.schemeRefleft join managers as m on s.managerref = m.managerrefleft join commissionPayaways as cpay on (cpay.fKeyRef=ce.commnEntryRef and cpay.fKeyType=1)where/* ce.type <> 3 and Renewal */cp.DateSubmitted >= convert(datetime,'2013-04-01') andcp.DateSubmitted <= convert(datetime,'2013-12-31') and/* (cp.type = 5 or cp.type = 4) and */ /* Value Based and Annual Renewal */cp.ChargeIncludeNBR = 1 /* KPI box checked *//* and c.clientref = 164003772 */
group by cons.Fullname,cp.ExecutionOnly,cp.commnpremref,ce.TotalDue,p.owner,c.surname,c.partnersurname,cp.replaceexisting, c.initials,c.partnerinitials,p.policynumber,cp.amount,cp.submitted,cp.datesubmitted,s.schemename,s.schemetype,cp.onrisk,cp.status,m.fullname,cp.proposaldate,cp.type,cp.sourceofbusiness,ce.type,cp.transactionmethod,u.fullname,ce.totalreceived,ce.settled,cp.datecreated
order by 'Provider'