Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Server Devlopment Studio

Author  Topic 

molden
Starting Member

4 Posts

Posted - 2013-07-26 : 09:18:15
Hi All,

I am trying to put the below code in a report in SQL Server Devlopment Studio but Keep getting the error:



My code is below and any help would be greatly appriciated

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-26 : 10:38:12
Is that the complete code? Have you declared the variable/parameter @MobUnlimited twice somewhere? Also, you need to define the variable/parameter @BundleChange.
Go to Top of Page

molden
Starting Member

4 Posts

Posted - 2013-07-26 : 10:42:38
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;
Go to Top of Page

molden
Starting Member

4 Posts

Posted - 2013-07-26 : 10:53:05
Hi, If any one is scratching there heads like I was its because @MOBUnlimited had caps off and on i.e. @MOBUnlimited and @MobUnlimited and now it works :D
Go to Top of Page
   

- Advertisement -