SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Server Devlopment Studio
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

molden
Starting Member

4 Posts

Posted - 07/26/2013 :  09:18:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/26/2013 :  10:38:12  Show Profile  Reply with Quote
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 - 07/26/2013 :  10:42:38  Show Profile  Reply with Quote
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;

Edited by - molden on 07/26/2013 10:43:27
Go to Top of Page

molden
Starting Member

4 Posts

Posted - 07/26/2013 :  10:53:05  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000