Hi,

The whole code is below but I've only pasted in from "WITH Linesummary As" because the program doesnt support Declare's and Set's

USE CallData;
DECLARE @BundleCharge MONEY
DECLARE @LLMin int
DECLARE @MobMin Int
DECLARE @LLUnlimited int
DECLARE @MOBUnlimited int
set @BundleCharge=0;
set @LLMin= 0;
set @MobMin=0;
-- When @LLUnlimited Or @MOBUnlimited set to 1 then Equals Unlimited Calls
Set @LLUnlimited =1;
Set @MOBUnlimited =1;
WITH Linesummary As
( SELECT LineFK,
Calls = COUNT(Call_ID),
RentalCost = FullMonthJuneJuly.Cost,
CallCost = isnull(SUM(Call_PerMinute.Cost),0),
RentalCharge = FullMonthJuneJuly.Charge,
Per30SecondCharge = isnull(SUM(Per30Second2),0),
CustomCharge = Case When @LLUnlimited = 0 then
case When @MobUnlimited = 0 then sum(case when DestinationType = 'Uk Landline' and CumulativeDuration <@LLMin or DestinationType = 'Uk Mobile' and CumulativeDuration <@MobMin then 0 else Per30Second2 End)
When @MOBUnlimited = 1 then sum(case when DestinationType = 'Uk Landline' and CumulativeDuration <@LLMin or DestinationType = 'Uk Mobile' then 0 else Per30Second2 End)
end
else
case When @MobUnlimited = 0 then sum(case when DestinationType = 'Uk Landline' or DestinationType = 'Uk Mobile' and CumulativeDuration <@MobMin then 0 else Per30Second2 End)
When @MOBUnlimited = 1 then sum(case when DestinationType = 'Uk Landline' or DestinationType = 'Uk Mobile' then 0 else Per30Second2 End)
end
end
FROM FullMonthJuneJuly
LEFT JOIN Call_PerMinute
ON Call_PerMinute.Line_FK = LineFK
GROUP BY LineFK, FullMonthJuneJuly.Cost, FullMonthJuneJuly.Charge
), AccountSummary AS
( SELECT LineTranslation.AccountFK,
Contracts = COUNT(DISTINCT LineTranslation.ContractFK),
Lines = COUNT(LineTranslation.LineID),
Channels = SUM(LineTranslation.Lines),
Calls = SUM(LineSummary.Calls),
RentalCost = sum(LineSummary.RentalCost),
CallCost = SUM(LineSummary.CallCost),
RentalCharge = sum(LineSummary.RentalCharge),
Per30SecondCharge = SUM(linesummary.Per30SecondCharge),
BundleCharge = sum(@BundleCharge*LineTranslation.Lines),
Quintile = NTILE(5) OVER(ORDER BY SUM(linesummary.Per30SecondCharge)),
CustomCharge = SUM(linesummary.CustomCharge),
BenefitedLines = Sum(Case when CustomCharge + @BundleCharge*LineTranslation.Lines < linesummary.Per30SecondCharge then 1 else 0 end)
FROM LineTranslation
inner JOIN LineSummary
ON LineTranslation.DDILineID = LineSummary.LineFK
AND LineType='WLR'
LEFT JOIN CallModelling.dbo.CRMBroadbandCharge
ON CRMBroadbandCharge.AccountID = AccountFK
and CRMBroadbandCharge.Linenumber = LineTranslation.Linenumber
and ChargeTypeName='Broadband Rental'
and FromDate = '20130601'
and ToDate = '20130630'
GROUP BY LineTranslation.AccountFK
)
SELECT Quintile,
Accounts = COUNT(*),
Contracts = SUM(Contracts),
Lines = SUM(lines),
Channels = SUM(Channels),
Calls = Sum(Calls),
RentalCost = sum(RentalCost),
CallCost=Sum(CallCost),
TotalCost = sum(RentalCost)+Sum(CallCost),
RentalCharge = SUM(RentalCharge),
Per30SecondCharge = SUM(Per30SecondCharge),
CustomCharge = SUM(CustomCharge),
BundleCharge = SUM(BundleCharge),
TotalCharge=SUM(CustomCharge)+SUM(BundleCharge),
BenefitedLines = Sum(BenefitedLines),
TotalGP = SUM(CustomCharge)+SUM(BundleCharge)-Sum(CallCost)
FROM AccountSummary
GROUP BY Quintile;