Over the next few days we are going to start the migration to a new forum application. I'm going to start with high post count and active users. You may receive an invitation to the new forums. It's not spam. It's just me trying to seed the user base. My goal is to open it up over the weekend.

Once we open the site we'll block registrations here. We should be open shortly on the new site.

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

3873 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.06 seconds. Powered By: Snitz Forums 2000