GETTING MULTIPLE VALUES FROM THIS SP

Question

1.00/5 (1 vote)

See more:

Hi, I am getting multiple from this. Can someone please help me in resolving this ?

SQL

CREATE PROCEDURE [dbo].[GetBMCOverages]
	 @ContractID nvarchar(MAX)  -- =	6145412166	
AS

DECLARE @Environment INT = 0
DECLARE @ContractIds TABLE (SContractID NVARCHAR(MAX))

CREATE TABLE #AggrTransaction ( AccountID INT, AccountName nvarchar(255), CompanyName nvarchar(400), OwnerTypeName nvarchar(400), OwnerType nvarchar(255),ContractID nvarchar(max),UnitsPurchased NVARCHAR(MAX)
,ContractStartDate NVARCHAR(MAX),ContractEndDate NVARCHAR(MAX), Email nvarchar(255), BillableUsage INT, Percentage NVARCHAR(MAX), Average INT,OverageYN NVARCHAR(100),ExpectedToGoOveragebefore NVARCHAR(100), ExpectedDate NVARCHAR(MAX) )

BEGIN
	
	SET NOCOUNT ON;
	
	DECLARE @SelectedContractIds TABLE (SContractID NVARCHAR(MAX))
	
	-- If @ContractIds has space in between, then exit
	if ( CHARINDEX(' ',@ContractID) > 0 )
		RETURN;
		
		
	-- 	Store all the User Selected Contract IDS in a table
	INSERT INTO @SelectedContractIds( SContractID )
		SELECT ITEMS FROM [BingMapsPlatform_Staging].[dbo].[Split](@ContractID, ',');
	
	IF ((SELECT  COUNT(SContractID) FROM @SelectedContractIds) = 0)
		SET @ContractID = NULL
	
			
INSERT INTO #AggrTransaction 		

--Get data from the veapi		
SELECT
FA.new_accountid AS AccountID,
FA.name AS AccountName,
FA.new_companyname AS CompanyName,
FA.new_ownertypename as OwnerTypeName,
FA.new_ownertype as OwnerType,
CASE FA.new_ownertype
WHEN 8 THEN FA.new_vlenrollmentnumber
ELSE FA.new_contractid
END AS ContractID,
CASE FA.new_unitspurchased
WHEN '0' THEN 'Unlimited Usage'
WHEN '-1' THEN 'No Prepaid Pool'
ELSE FA.new_unitspurchased
END AS UnitsPurchased,
CASE FA.new_ownertype
WHEN 8 THEN FA.new_vlenrollmentenddate
ELSE FA.new_contractstartdate
END AS ContractStartDate,
CASE FA.new_ownertype
WHEN 8 THEN FA.new_vlenrollmentenddate
ELSE FA.new_contractenddate
END AS ContractEndDate,
FAU.new_email AS Email,
SUM(VEWS.TransactionCount) AS BillableUsage,
(ltrim(str(100.0 * sum(VEWS.TransactionCount) / fa.new_unitspurchased, 6, 2))) + ' %' as Percenatge,
(SUM(VEWS.TransactionCount) / 12) AS Average,
CASE fa.new_unitspurchased
WHEN Fa.new_unitspurchased THEN 'Y'
ELSE 'N'
END AS OverageYN,
CASE fa.new_contractenddate
WHEN fa.new_contractenddate THEN 'Y'
ELSE 'N'
END AS ExpectedToGoOveragebefore,
CASE FA.new_contractenddate
WHEN FA.new_unitspurchased THEN 'IN-OVERAGE'
ELSE FA.new_contractenddate
END AS ExpectedDate
FROM BingMapsPlatform_MSCRM.dbo.FilteredNew_accountuser FAU WITH (NOLOCK)
INNER JOIN BingMapsPlatform_MSCRM.dbo.Filterednew_new_accountuser_account FAUA  WITH (NOLOCK)
ON FAU.new_accountuserid = FAUA.new_accountuserid
INNER JOIN BingMapsPlatform_MSCRM.dbo.FilteredAccount FA WITH (NOLOCK)
ON FAUA.accountid = FA.accountid
INNER JOIN BingMapsPlatform_Staging.dbo.VEWSDailyAggTrans VEWS WITH(NOLOCK)
ON VEWS.PrimaryId=FA.new_accountid
INNER JOIN @SelectedContractIds CID
ON CID.SContractID = FA.new_contractid
WHERE FA.new_ownertype IN (2,7,8,10,11)
AND VEWS.ServerRole=@Environment
GROUP BY
FA.new_accountid,
FA.name,
FA.new_companyname,
FA.new_ownertypename,
FA.new_ownertype,
FA.new_unitspurchased,
FA.new_contractenddate,
FA.new_vlenrollmentnumber,
FA.new_contractid,
FA.new_contractstartdate,
FA.new_vlenrollmentenddate,
FAU.new_email

UNION

--Get data from tfe
SELECT
FA.new_accountid AS AccountID,
FA.name AS AccountName,
FA.new_companyname AS CompanyName,
FA.new_ownertypename as OwnerTypeName,
FA.new_ownertype as OwnerType,
CASE FA.new_ownertype
WHEN 8 THEN FA.new_vlenrollmentnumber
ELSE FA.new_contractid
END AS ContractID,
CASE FA.new_unitspurchased
WHEN '0' THEN 'Unlimited Usage'
WHEN '-1' THEN 'No Prepaid Pool'
ELSE FA.new_unitspurchased
END AS UnitsPurchased,
CASE FA.new_ownertype
WHEN 8 THEN FA.new_vlenrollmentenddate
ELSE FA.new_contractstartdate
END AS ContractStartDate,
CASE FA.new_ownertype
WHEN 8 THEN FA.new_vlenrollmentenddate
ELSE FA.new_contractenddate
END AS ContractEndDate,
FAU.new_email AS Email,
SUM(TFE.TransactionCount) AS BillableUsage,
(ltrim(str(100.0 * sum(TFE.TransactionCount) / fa.new_unitspurchased, 6, 2))) + ' %' as Percenatge,
(SUM(TFE.TransactionCount)/12) AS Average,
CASE fa.new_unitspurchased
WHEN fa.new_unitspurchased THEN 'Y'
ELSE 'N'
END AS OverageYN,
CASE fa.new_contractenddate
WHEN fa.new_contractenddate THEN 'Y'
ELSE 'N'
END AS ExpectedToGoOveragebefore,
CASE FA.new_contractenddate
WHEN FA.new_unitspurchased THEN 'IN-OVERAGE'
ELSE FA.new_contractenddate
END AS ExpectedDate
FROM BingMapsPlatform_MSCRM.dbo.FilteredNew_accountuser FAU WITH (NOLOCK)
INNER JOIN BingMapsPlatform_MSCRM.dbo.Filterednew_new_accountuser_account FAUA  WITH (NOLOCK)
ON FAU.new_accountuserid = FAUA.new_accountuserid
INNER JOIN BingMapsPlatform_MSCRM.dbo.FilteredAccount FA WITH (NOLOCK)
ON FAUA.accountid = FA.accountid
INNER JOIN BingMapsPlatform_Staging.dbo.TFEDailyAggTrans TFE WITH(NOLOCK)
ON TFE.AccountId=FA.new_accountid
INNER JOIN @SelectedContractIds CID
ON CID.SContractID = FA.new_contractid
WHERE FA.new_ownertype IN (2,7,8,10,11)
AND TFE.EnvironmentId=@Environment
GROUP BY
FA.new_accountid,
FA.name,
FA.new_companyname,
FA.new_ownertypename,
FA.new_ownertype,
FA.new_unitspurchased,
FA.new_contractenddate,
FA.new_vlenrollmentnumber,
FA.new_contractid,
FA.new_contractstartdate,
FA.new_vlenrollmentenddate,
FAU.new_email

UNION

--Get data from MWS
SELECT
FA.new_accountid AS AccountID,
FA.name AS AccountName,
FA.new_companyname AS CompanyName,
FA.new_ownertypename as OwnerTypeName,
FA.new_ownertype as OwnerType,
CASE FA.new_ownertype
WHEN 8 THEN FA.new_vlenrollmentnumber
ELSE FA.new_contractid
END AS ContractID,
CASE FA.new_unitspurchased
WHEN '0' THEN 'Unlimited Usage'
WHEN '-1' THEN 'No Prepaid Pool'
ELSE FA.new_unitspurchased
END AS UnitsPurchased,
CASE FA.new_ownertype
WHEN 8 THEN FA.new_vlenrollmentenddate
ELSE FA.new_contractstartdate
END AS ContractStartDate,
CASE FA.new_ownertype
WHEN 8 THEN FA.new_vlenrollmentenddate
ELSE FA.new_contractenddate
END AS ContractEndDate,
FAU.new_email AS Email,
SUM(MWS.TransactionCount) AS BillableUsage,
(ltrim(str(100.0 * sum(MWS.TransactionCount) / fa.new_unitspurchased, 6, 2))) + ' %' as Percenatge,
(SUM(MWS.TransactionCount)/12) AS Average,
CASE fa.new_unitspurchased
WHEN fa.new_unitspurchased THEN 'Y'
ELSE 'N'
END AS OverageYN,
CASE fa.new_contractenddate
WHEN fa.new_contractenddate THEN 'Y'
ELSE 'N'
END AS ExpectedToGoOveragebefore,
CASE FA.new_contractenddate
WHEN FA.new_unitspurchased THEN 'IN-OVERAGE'
ELSE FA.new_contractenddate
END AS ExpectedDate
FROM BingMapsPlatform_MSCRM.dbo.FilteredNew_accountuser FAU WITH (NOLOCK)
INNER JOIN BingMapsPlatform_MSCRM.dbo.Filterednew_new_accountuser_account FAUA  WITH (NOLOCK)
ON FAU.new_accountuserid = FAUA.new_accountuserid
INNER JOIN BingMapsPlatform_MSCRM.dbo.FilteredAccount FA WITH (NOLOCK)
ON FAUA.accountid = FA.accountid
INNER JOIN BingMapsPlatform_Staging.dbo.MWSDailyAggTrans MWS WITH(NOLOCK)
ON MWS.AccountId=FA.new_accountid
INNER JOIN @SelectedContractIds CID
ON CID.SContractID = FA.new_contractid
WHERE FA.new_ownertype IN (2,7,8,10,11)
AND MWS.EnvironmentId=@Environment
GROUP BY
FA.new_accountid,
FA.name,
FA.new_companyname,
FA.new_ownertypename,
FA.new_ownertype,
FA.new_unitspurchased,
FA.new_contractenddate,
FA.new_vlenrollmentnumber,
FA.new_contractid,
FA.new_contractstartdate,
FA.new_vlenrollmentenddate,
FAU.new_email

SELECT 
			AccountID,AccountName,CompanyName,OwnerTypeName,OwnerType,ContractID,UnitsPurchased,ContractStartDate,ContractEndDate
			,Email,SUM(AGG.BillableUsage) AS BillableUsage,
			(ltrim(str(100.0 * sum(AGG.BillableUsage) / agg.UnitsPurchased, 6, 2))) + ' %' as Percenatge,
			(SUM(AGG.BillableUsage)/12) AS Average,
			CASE Agg.UnitsPurchased
			WHEN  '100001' THEN 'Y'  --((SUM(AGG.BillableUsage))> AGG.UnitsPurchased)
			ELSE 'N'
			END AS OverageYN,
			CASE agg.ContractEndDate
			WHEN agg.ContractEndDate THEN 'Y'
			ELSE 'N'
			END AS ExpectedToGoOveragebefore,
			CASE AGG.ContractEndDate
			WHEN AGG.UnitsPurchased THEN 'IN-OVERAGE'
			ELSE agg.ContractEndDate
			END AS ExpectedDate

	FROM   
			#AggrTransaction  AGG
	
	GROUP BY AGG.AccountID,AGG.AccountName,AGG.CompanyName,AGG.OwnerTypeName,AGG.OwnerType,AGG.ContractID,AGG.UnitsPurchased,AGG.ContractStartDate,AGG.ContractEndDate
			,AGG.Email,AGG.Percentage,AGG.Average,AGG.OverageYN,AGG.ExpectedToGoOveragebefore,AGG.ExpectedDate 			
		
DROP TABLE #AggrTransaction			

END

GO

Please tell me how to remove the multiple values ?

Posted 12-Dec-12 19:29pm

PRASHANT KUMAR LAL

Updated 12-Dec-12 21:06pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

Comments

Kuthuparakkal 13-Dec-12 2:18am

Whats multiple, what's the output etc.. this is a lot of code pasted, not a good framed question. Improve Question

[no name] 13-Dec-12 7:26am

To avoid multiple values from the stored procedure you need to improve the query itself.

3 solutions

Solution 1

Any proc can give you as many values as you want, you can do as many selects as you want and a proc can return more than one table. You gave us a code dump and a vague ( and easy to fulfil ) requirement. I don't see what you're hoping for here.

Posted 12-Dec-12 21:07pm

Christian Graus

Comments

ZurdoDev 13-Dec-12 11:22am

How is this a solution?

Christian Graus 13-Dec-12 13:57pm

It's the ONLY possible solution. You get multiple values from a proc, by asking for them. 'Please tell me how to remove the multiple values ?' was not there when I answered this question. It was merely a code dump. So the question as it was framed, I gave the only possible answer.

Solution 2

Hi,

I think you need to add a where clause based upon your requirement in your select statement i.e add the where clause in

SELECT 
			AccountID,AccountName,CompanyName,OwnerTypeName,OwnerType,ContractID,UnitsPurchased,ContractStartDate,ContractEndDate
			,Email,SUM(AGG.BillableUsage) AS BillableUsage,
			(ltrim(str(100.0 * sum(AGG.BillableUsage) / agg.UnitsPurchased, 6, 2))) + ' %' as Percenatge,
			(SUM(AGG.BillableUsage)/12) AS Average,
			CASE Agg.UnitsPurchased
			WHEN  '100001' THEN 'Y'  --((SUM(AGG.BillableUsage))> AGG.UnitsPurchased)
			ELSE 'N'
			END AS OverageYN,
			CASE agg.ContractEndDate
			WHEN agg.ContractEndDate THEN 'Y'
			ELSE 'N'
			END AS ExpectedToGoOveragebefore,
			CASE AGG.ContractEndDate
			WHEN AGG.UnitsPurchased THEN 'IN-OVERAGE'
			ELSE agg.ContractEndDate
			END AS ExpectedDate
 
	FROM   
			#AggrTransaction  AGG
	
	GROUP BY AGG.AccountID,AGG.AccountName,AGG.CompanyName,AGG.OwnerTypeName,AGG.OwnerType,AGG.ContractID,AGG.UnitsPurchased,AGG.ContractStartDate,AGG.ContractEndDate
			,AGG.Email,AGG.Percentage,AGG.Average,AGG.OverageYN,AGG.ExpectedToGoOveragebefore,AGG.ExpectedDate

This will resolve your problem.

Or Try this.

SELECT TOP 1 
			AccountID,AccountName,CompanyName,OwnerTypeName,OwnerType,ContractID,UnitsPurchased,ContractStartDate,ContractEndDate
			,Email,SUM(AGG.BillableUsage) AS BillableUsage,
			(ltrim(str(100.0 * sum(AGG.BillableUsage) / agg.UnitsPurchased, 6, 2))) + ' %' as Percenatge,
			(SUM(AGG.BillableUsage)/12) AS Average,
			CASE Agg.UnitsPurchased
			WHEN  '100001' THEN 'Y'  --((SUM(AGG.BillableUsage))> AGG.UnitsPurchased)
			ELSE 'N'
			END AS OverageYN,
			CASE agg.ContractEndDate
			WHEN agg.ContractEndDate THEN 'Y'
			ELSE 'N'
			END AS ExpectedToGoOveragebefore,
			CASE AGG.ContractEndDate
			WHEN AGG.UnitsPurchased THEN 'IN-OVERAGE'
			ELSE agg.ContractEndDate
			END AS ExpectedDate
 
	FROM   
			#AggrTransaction  AGG
	
	GROUP BY AGG.AccountID,AGG.AccountName,AGG.CompanyName,AGG.OwnerTypeName,AGG.OwnerType,AGG.ContractID,AGG.UnitsPurchased,AGG.ContractStartDate,AGG.ContractEndDate
			,AGG.Email,AGG.Percentage,AGG.Average,AGG.OverageYN,AGG.ExpectedToGoOveragebefore,AGG.ExpectedDate

And use order by clause too.

Thanks

Posted 13-Dec-12 1:30am

sisir patro

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PRASHANT KUMAR LAL · Accepted Answer · 2012-12-15T17:33:00

Solution 3

i made a separate table which i used to filter my data. And then i created a sp with my table and joins which has improved my query execution speed and performance.

Posted 15-Dec-12 17:33pm

PRASHANT KUMAR LAL