WoodleySaint Posted March 18, 2014 Report Share Posted March 18, 2014 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' Link to comment Share on other sites More sharing options...

## Recommended Posts

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account## Sign in

Already have an account? Sign in here.

Sign In Now