Jump to content

WoodleySaint

Members
  • Content count

    3
  • Joined

  • Last visited

Community Reputation

0 Neutral

About WoodleySaint

  • Rank
    Newbie
  1. WoodleySaint

    Sum of iterative rows

    Hi, Here is a subset of some rows from a SQL table: I would like to construct a select statement which will have a row for each record it finds where the CreditType is 0 and then sum records where the ParentFeeFee is the same as the FeeRef. So the orange ones would form one summation and the green another and the results would be: The easy part is: Select FeeRef from tableName where CreditType = 0 How would I perform the summation part? Many thanks for any guidance.
  2. WoodleySaint

    Primary Key next value

    I would like to add some rows to an existing table in MS SQL server 2005 which has 3 columns: ProviderCodeRef (PK, int, not null) ManagerRef (int, not null) UnitPriceRef (int, not null) The current last row is as follows: ProviderCodeRef ManagerRef UnitPriceRef 164000790 164000598 164000891 If I run the following the record will be added. Insert into ProviderCodes (ProviderCodeRef,ManagerRef,UnitPriceRef) values ('164000791','164000110','164005000'); so the last 2 rows are now ProviderCodeRef ManagerRef UnitPriceRef 164000790 164000598 164000891 164000791 164000110 164005000 If I try an add a record to this table using the application GUI it throws up an error. If I add the record with the following, thereby leaving a gap in the ProviderCodeRefs. Insert into ProviderCodes (ProviderCodeRef,ManagerRef,UnitPriceRef) values ('164000800','164000110','164005000'); I can add a record with the GUI but it is assigned the ProviderCodeRef 164000791 so the table now looks like this: ProviderCodeRef ManagerRef UnitPriceRef 164000790 164000598 164000891 164000791 164000300 164000251 164000800 164000100 164005000 I would be able to add a further 8 records using the GUI but it would then throw an error. How can I make the table use a specific ProviderCodeRef for the next record added with the GUI? Thanks for any advice.
  3. WoodleySaint

    Group by help

    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'
×