从这个SP获得多个价值 [英] GETTING MULTIPLE VALUES FROM THIS SP

查看：78
本文介绍了从这个SP获得多个价值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我从中得到了多个。有人可以帮我解决这个问题吗？

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

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 ?

推荐答案

i做了一个单独的表，我用来过滤我的数据。然后我用我的表和连接创建了一个sp，这提高了我的查询执行速度和性能。
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.


任何proc都可以为你提供任意数量的值，你可以做多少选择如你所愿，proc可以返回多个表。您给了我们一个代码转储和一个模糊（易于实现）的要求。我不知道你在这里想要什么。
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.






我认为你需要添加根据您在select语句中的要求的where子句，即在
中添加where子句


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





并且也使用order by子句。



谢谢



And use order by clause too.

Thanks


这篇关于从这个SP获得多个价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆