Multiple sumations - at present we perform them sequentially
-
This query is used to find the sales margin of one of our insurance products sold in October. For each product we edit this query in the last line by replacing 'ci%' with, say 'rr%'.
We would like to do all the products at once. We only sell seven products but one query is seven times faster than seven.
I assume the answer is probably simple so apoligies in advance for my lack of knowledge.
--This line is to sum the sales "value" less the costs "IPT", "Supplier1" and "Supplier2"
Selectsum (CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge))
--This line determines the period start date
WhereCPP.PhaseStartDate >= '10/01/2008'
--This line determines the period end date--
AndCPP.PhaseStartDate < '11/01/2008'
--This line selects the insurance that we would like totalled.
AndCPP.Service like 'ci%'
-
Something like this
Select sum (case when CPP.Service like 'ci%' then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [ci_sum],sum (case when CPP.Service like 'rr%' then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [rr_sum]
--This line determines the period start date
Where CPP.PhaseStartDate >= '10/01/2008'
--This line determines the period end date--
And CPP.PhaseStartDate < '11/01/2008'
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thank you so much. That will make such a difference.
-
This is a simple group by query:
Select cpp.Service
,Sum(cpp.PhaseValue -(cpp.PhaseIPT + cpp.Supplier1Charge + cpp.Supplier2Charge)) As Total
From yourTable cpp
Where CPP.PhaseStartDate >= '10/01/2008'
And CPP.PhaseStartDate < '11/01/2008'
--And CPP.Service like 'ci%'
Group By
cpp.Service;
This will give you the results for all Services.
Now, I am assuming that you modify this query for each month when you run it. If so, you can also modify it as:
Select cpp.Service
,Sum(cpp.PhaseValue -(cpp.PhaseIPT + cpp.Supplier1Charge + cpp.Supplier2Charge)) As Total
From yourTable cpp
Where CPP.PhaseStartDate >= dateadd(month, datediff(month, 0, current_timestamp) - 1, 0)
And CPP.PhaseStartDate < dateadd(month, datediff(month, 0, current_timestamp), 0)
--And CPP.Service like 'ci%'
Group By
cpp.Service;
This calculates the first of last month and the first of this month so you will always get the results for last month.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thank you Mark. I have added your solution to my query. How can count the number of products at the same time?
Select sum (case when (CPP.Service like 'rrp%' or CPP.Service like 'rrv%' or CPP.Service like 'rrsc' or CPP.Service like 'rrsc') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [RR-Retail Margin],
sum (case when (CPP.Service like 'ci%'and CPP.Service not like 'cictc') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [CI Margin],
sum (case when (CPP.Service like 'rrgwi2' or CPP.Service like 'rrmeg%' or CPP.Service like 'rrctcm') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [RR-Bundled Margin],
sum (case when (CPP.Service like 'rrc%' and CPP.Service not like 'rrctc%') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [RR-Fleet Margin],
sum (case when (CPP.Service like 'ti%' or CPP.Service like 'at') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [Travel Margin],
sum (case when (CPP.Service like 'rrctc' or CPP.Service like 'rrgwiz') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [RR Vendor1 Margin],
sum (case when (CPP.Service like 'rr%' and (CPP.Scheme like '19354000' or CPP.Scheme like '19367015' or CPP.Scheme like '19574000' or CPP.Scheme like '19457015' or CPP.Scheme like '19614000' or CPP.Scheme like '19604000' or CPP.Scheme like '19564000')) then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [RR Vendor2 Margin]
fromCustomerPolicy CP, CustomerPolicyPhase CPP
WhereCP.PolicyNumber = CPP.PolicyNumber
AndCP.PolicyStartDate = CP.PolicyInceptionDate
AndCP.CustomerNumber <> 'ZZ999999'
AndCPP.PhaseStartDate >= dateadd(month, datediff(month, 0, current_timestamp) - 1, 0)
AndCPP.PhaseStartDate < dateadd(month, datediff(month, 0, current_timestamp), 0)
AndCP.PolicyStatus = 'A'
And(CPP.PhaseType <> 'CD' and CPP.PhaseType <> 'UP' and CPP.PhaseType <> 'CN' and CPP.PhaseType <> 'TM')
-
Thank you Jeffrey. I have used your solution in a number of my queries. How can I change the folowing codes to relate to the current month? Is there an easy discriptor for all the parameters contained within each line?
Where CPP.PhaseStartDate >= dateadd(month, datediff(month, 0, current_timestamp) - 1, 0)
And CPP.PhaseStartDate < dateadd(month, datediff(month, 0, current_timestamp), 0)
-
Select sum (case when (CPP.Service like 'rrp%' or CPP.Service like 'rrv%' or CPP.Service like 'rrsc' or CPP.Service like 'rrsc') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [RR-Retail Margin],
sum (case when (CPP.Service like 'ci%'and CPP.Service not like 'cictc') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [CI Margin],
sum (case when (CPP.Service like 'rrgwi2' or CPP.Service like 'rrmeg%' or CPP.Service like 'rrctcm') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [RR-Bundled Margin],
sum (case when (CPP.Service like 'rrc%' and CPP.Service not like 'rrctc%') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [RR-Fleet Margin],
sum (case when (CPP.Service like 'ti%' or CPP.Service like 'at') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [Travel Margin],
sum (case when (CPP.Service like 'rrctc' or CPP.Service like 'rrgwiz') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [RR Vendor1 Margin],
sum (case when (CPP.Service like 'rr%' and (CPP.Scheme like '19354000' or CPP.Scheme like '19367015' or CPP.Scheme like '19574000' or CPP.Scheme like '19457015' or CPP.Scheme like '19614000' or CPP.Scheme like '19604000' or CPP.Scheme like '19564000')) then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [RR Vendor2 Margin],
sum (case when (CPP.Service like 'rrp%' or CPP.Service like 'rrv%' or CPP.Service like 'rrsc' or CPP.Service like 'rrsc') then 1 else 0 end) as [RR-Retail Margin Count],
sum (case when (CPP.Service like 'ci%'and CPP.Service not like 'cictc') then 1 else 0 end) as [CI Margin Count],
sum (case when (CPP.Service like 'rrgwi2' or CPP.Service like 'rrmeg%' or CPP.Service like 'rrctcm') then 1 else 0 end) as [RR-Bundled Margin Count],
sum (case when (CPP.Service like 'rrc%' and CPP.Service not like 'rrctc%') then 1 else 0 end) as [RR-Fleet Margin Count],
sum (case when (CPP.Service like 'ti%' or CPP.Service like 'at') then 1 else 0 end) as [Travel Margin Count],
sum (case when (CPP.Service like 'rrctc' or CPP.Service like 'rrgwiz') then 1 else 0 end) as [RR Vendor1 Margin Count],
sum (case when (CPP.Service like 'rr%' and (CPP.Scheme like '19354000' or CPP.Scheme like '19367015' or CPP.Scheme like '19574000' or CPP.Scheme like '19457015' or CPP.Scheme like '19614000' or CPP.Scheme like '19604000' or CPP.Scheme like '19564000')) then 1 else 0 end) as [RR Vendor2 Margin Count]
from CustomerPolicy CP, CustomerPolicyPhase CPP
Where CP.PolicyNumber = CPP.PolicyNumber
And CP.PolicyStartDate = CP.PolicyInceptionDate
And CP.CustomerNumber <> 'ZZ999999'
And CPP.PhaseStartDate >= dateadd(month, datediff(month, 0, current_timestamp) - 1, 0)
And CPP.PhaseStartDate < dateadd(month, datediff(month, 0, current_timestamp), 0)
And CP.PolicyStatus = 'A'
And (CPP.PhaseType <> 'CD' and CPP.PhaseType <> 'UP' and CPP.PhaseType <> 'CN' and CPP.PhaseType <> 'TM')
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Thank you this is great.
-
Davroche (10/21/2008)
Thank you Jeffrey. I have used your solution in a number of my queries. How can I change the folowing codes to relate to the current month? Is there an easy discriptor for all the parameters contained within each line?Where CPP.PhaseStartDate >= dateadd(month, datediff(month, 0, current_timestamp) - 1, 0)
And CPP.PhaseStartDate < dateadd(month, datediff(month, 0, current_timestamp), 0)
If you want the current month, you can easily change this to:
Where cpp.PhaseStartDate >= dateadd(month, datediff(month, 0, current_timestamp), 0) --start of current month
And cpp.PhaseStartDate < dateadd(month, datediff(month, 0, current_timestamp) + 1, 0) -- start of next month
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thank you Jeffrey. I now have what was fourteen queries into one and no editing of the query required before use. Brilliant.
-
I have been checking the query and I have resluts that do not tally.
My current query sums a series of conditions and it totals the same conditions.
I have tried to produce a list with the same condtions but I am missing some sintax. Could you help?
I apologise for the bad formating in advance.
Select sum (case when (cpp.service like '%' and not (cpp.service like 'mb%'and not cpp.service like 'mbs')) then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [Total £],
sum (case when (CPP.Service like '%' and (cpp.service like 'rrp%' or CPP.Service like 'rrv%' or CPP.Service like 'rrsc' or CPP.Service like 'rrsc')and not (CPP.Scheme like '19354000' or CPP.Scheme like '19367015' or CPP.Scheme like '19574000' or CPP.Scheme like '19457015' or CPP.Scheme like '19614000' or CPP.Scheme like '19604000' or CPP.Scheme like '19564000')) then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [RR-R £],
sum (case when (CPP.Service like 'ci%') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [CI £],
sum (case when (CPP.Service like 'rrgwi2' or CPP.Service like 'rrmeg%' or CPP.Service like 'rrctcm') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [RR-B £],
sum (case when (CPP.Service like 'rrc%' and CPP.Service not like 'rrctc%') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [RR-F £],
sum (case when (CPP.Service like 'ti%' or CPP.Service like 'at%') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [ATI £],
sum (case when (CPP.Service like 'rrctc' or CPP.Service like 'rrgwiz' or cpp.service not like 'mb%' and (CPP.Scheme like '19354000' or CPP.Scheme like '19367015' or CPP.Scheme like '19574000' or CPP.Scheme like '19457015' or CPP.Scheme like '19614000' or CPP.Scheme like '19604000' or CPP.Scheme like '19564000')) then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [RR-V £],
sum (case when (CPP.Service like 'co%') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [CO £],
sum (case when (CPP.Service like 'mbs%') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as ,
sum (case when (cpp.service like 'mb%'and not cpp.service like 'mbs') then CPP.PhaseValue - (CPP.PhaseIPT + CPP.Supplier1Charge + CPP.Supplier2Charge) end) as [MB £],
sum (case when (cpp.service like '%' and not (cpp.service like 'mb%'and not cpp.service like 'mbs')) then 1 else 0 end) as [Total #],
sum (case when (CPP.Service like '%' and (cpp.service like 'rrp%' or CPP.Service like 'rrv%' or CPP.Service like 'rrsc' or CPP.Service like 'rrsc')and not (CPP.Scheme like '19354000' or CPP.Scheme like '19367015' or CPP.Scheme like '19574000' or CPP.Scheme like '19457015' or CPP.Scheme like '19614000' or CPP.Scheme like '19604000' or CPP.Scheme like '19564000')) then 1 else 0 end) as [RR-R #],
sum (case when (CPP.Service like 'ci%') then 1 else 0 end) as [CI #],
sum (case when (CPP.Service like 'rrgwi2' or CPP.Service like 'rrmeg%' or CPP.Service like 'rrctcm') then 1 else 0 end) as [RR-B #],
sum (case when (CPP.Service like 'rrc%' and CPP.Service not like 'rrctc%') then 1 else 0 end) as [RR-F #],
sum (case when (CPP.Service like 'ti%' or CPP.Service like 'at%') then 1 else 0 end) as [ATI #],
sum (case when (CPP.Service like 'rrctc' or CPP.Service like 'rrgwiz' or cpp.service not like 'mb%' and (CPP.Scheme like '19354000' or CPP.Scheme like '19367015' or CPP.Scheme like '19574000' or CPP.Scheme like '19457015' or CPP.Scheme like '19614000' or CPP.Scheme like '19604000' or CPP.Scheme like '19564000')) then 1 else 0 end) as [RR-V2 #],
sum (case when (CPP.Service like 'co%') then 1 else 0 end) as [CO #],
sum (case when (CPP.Service like 'mbs%') then 1 else 0 end) as ,
sum (case when (cpp.service like 'mb%'and not cpp.service like 'mbs')then 1 else 0 end) as [MB #]
from CustomerPolicy CP, CustomerPolicyPhase CPP
Where CP.PolicyNumber = CPP.PolicyNumber
And CP.PolicyStartDate = CP.PolicyInceptionDate
And CP.CustomerNumber <> 'ZZ999999'
And CPP.PhaseStartDate >= dateadd(month, datediff(month, 0, current_timestamp) -1, 0)
And CPP.PhaseStartDate < dateadd(month, datediff(month, 0, current_timestamp), 0)
And CP.PolicyStatus = 'A'
And (CPP.PhaseType <> 'CD' and CPP.PhaseType <> 'UP' and CPP.PhaseType <> 'CN' and CPP.PhaseType <> 'TM')
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply