[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 111
  • Last Modified:

how to order by using parameter value

I have a stored procedure that I would like to order by the order of the parameters it takes in starting for current item number 1, prior item number 1, current item number 2, prior item number 2, and so on.

currently I am ordering it by:

            c.CurrentItemNumber
      ,      p.PriorItemNumber

among other fields, but I would like to replace the part above with the parameters 1 through 5 (current and prior).

 Is this possible?

Please see the attached stored procedure for reference.
Ingredient-Cost-Comparison.sql
0
metropia
Asked:
metropia
  • 3
  • 3
1 Solution
 
PortletPaulCommented:
Are you referring to lines 262 to 268? Does this not work? Perhaps you need to give us a sample of what you get now and what you would like to get.
USE [DW]
GO
/****** Object:  StoredProcedure [cost].[Ingredient_Cost_Comparison]    Script Date: 7/11/2014 8:38:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*=================================================================================================================================================================
Notes:
Create/2014-07-01-ANG
===================================================================================================================================================================
Dev
===================================================================================================================================================================
[cost].[Ingredient_Cost_Comparison] 'QUO-10051-5', 'QUO-10051-4', '11039', '11039', '', '', '', '', '', '', '', ''
[cost].[Ingredient_Cost_Comparison] 'QUO-10051-5', 'QUO-10051-4', '11039', '11039', '10610', '10610', '', '', '', '', '', ''
[cost].[Ingredient_Cost_Comparison] 'QUO-10051-5', 'QUO-10051-4', '11039', '11039', '10610', '10610', '11745', '11745', '', '', '', ''
[cost].[Ingredient_Cost_Comparison] 'QUO-10051-5', 'QUO-10051-4', '11039', '11039', '10610', '10610', '11745', '11745', '11008', '11008', '', ''
[cost].[Ingredient_Cost_Comparison] 'QUO-10051-5', 'QUO-10051-4', '11039', '11039', '10610', '10610', '11745', '11745', '11008', '11008', '11498', '11498'
===================================================================================================================================================================
Prod
[cost].[Ingredient_Cost_Comparison] 'QUO-10051-6', 'QUO-10051-5', '11039', '11039', '', '', '', '', '', '', '', ''
[cost].[Ingredient_Cost_Comparison] 'QUO-10051-6', 'QUO-10051-5', '11039', '11039', '10610', '10610', '', '', '', '', '', ''
[cost].[Ingredient_Cost_Comparison] 'QUO-10051-6', 'QUO-10051-5', '11039', '11039', '10610', '10610', '11745', '11745', '', '', '', ''
[cost].[Ingredient_Cost_Comparison] 'QUO-10051-6', 'QUO-10051-5', '11039', '11039', '10610', '10610', '11745', '11745', '11008', '11008', '', ''
[cost].[Ingredient_Cost_Comparison] 'QUO-10051-6', 'QUO-10051-5', '11039', '11039', '10610', '10610', '11745', '11745', '11008', '11008', '11498', '11498'
=================================================================================================================================================================*/
ALTER PROCEDURE [cost].[Ingredient_Cost_Comparison]
(
	@CurrentSalesQuoteNumber	NVARCHAR(20)
,	@PriorSalesQuoteNumber		NVARCHAR(20)
,	@CurrentItemNumber1			NVARCHAR(20)
,	@PriorItemNumber1			NVARCHAR(20)
,	@CurrentItemNumber2			NVARCHAR(20)
,	@PriorItemNumber2			NVARCHAR(20)
,	@CurrentItemNumber3			NVARCHAR(20)
,	@PriorItemNumber3			NVARCHAR(20)
,	@CurrentItemNumber4			NVARCHAR(20)
,	@PriorItemNumber4			NVARCHAR(20)
,	@CurrentItemNumber5			NVARCHAR(20)
,	@PriorItemNumber5			NVARCHAR(20)
)
AS
BEGIN

	--DECLARE @CurrentQuote_CurrentItem TABLE 
	--(
	--	CurrentCustomerNumber_BillTo		VARCHAR(20)
	--,	CurrentSalesQuoteNumber				VARCHAR(20)
	--,	CurrentQuoteLineNumber				VARCHAR(20)
	--,	CurrentItemNumber					VARCHAR(20)
	--,	CurrentItemDescription				VARCHAR(50)
	--,	CurrentComponentItemNumber			VARCHAR(20)
	--,	CurrentComponentItemDescription		VARCHAR(50)
	--,	CurrentComponentItemLevel			INTEGER
	--,	CurrentBOMQuantity					DECIMAL(38,20)
	--,	CurrentBOMUnitOfMeasure				VARCHAR(10)
	--,	CurrentCostTypeUsed					VARCHAR(10)
	--,	CurrentCostMaterialWorksheet		DECIMAL(38,20)
	--,	CurrentCostMaterialMarketExtended	DECIMAL(38,20)
	--,	CurrentRequestedDeliveryDate		DATETIME
	--)
	--INSERT INTO @CurrentQuote_CurrentItem
	--(	
	--	CurrentCustomerNumber_BillTo
	--,	CurrentSalesQuoteNumber
	--,	CurrentQuoteLineNumber
	--,	CurrentItemNumber
	--,	CurrentItemDescription
	--,	CurrentComponentItemNumber
	--,	CurrentComponentItemDescription
	--,	CurrentComponentItemLevel
	--,	CurrentBOMQuantity
	--,	CurrentBOMUnitOfMeasure
	--,	CurrentCostTypeUsed
	--,	CurrentCostMaterialWorksheet
	--,	CurrentCostMaterialMarketExtended
	--,	CurrentRequestedDeliveryDate
	--)
	SELECT 
		s.CustomerNumber				AS 'CurrentCustomerNumber_BillTo'
	,	s.SalesQuoteNumber				AS 'CurrentSalesQuoteNumber'
	,	s.SalesQuoteLineNumber			AS 'CurrentQuoteLineNumber'
	,	s.ItemNumber					AS 'CurrentItemNumber'
	,	s.ItemDescription				AS 'CurrentItemDescription'
	,	s.ComponentItemNumber			AS 'CurrentComponentItemNumber'
	,	s.ComponentItemDescription		AS 'CurrentComponentItemDescription'
	,	s.ComponentItemLevel			AS 'CurrentComponentItemLevel'
	,	s.BOMQuantity					AS 'CurrentBOMQuantity'
	,	s.BOMUnitOfMeasure				AS 'CurrentBOMUnitOfMeasure'
	,	s.CostTypeUsed					AS 'CurrentCostTypeUsed'
	,	s.CostMaterialWorksheet			AS 'CurrentCostMaterialWorksheet'
	,	s.CostMaterialMarketExtended	AS 'CurrentCostMaterialMarketExtended'
	,	s.RequestedDeliveryDate			AS 'CurrentRequestedDeliveryDate'
	INTO #Current
	FROM dw.cost.vStd_SalesQuoteCostLineBOM s (NOLOCK)
	WHERE s.SalesQuoteNumber = @CurrentSalesQuoteNumber
	AND s.ItemNumber IN (
								ISNULL(@CurrentItemNumber1, '')
							,	ISNULL(@CurrentItemNumber2, '')
							,	ISNULL(@CurrentItemNumber3, '')
							,	ISNULL(@CurrentItemNumber4, '')
							,	ISNULL(@CurrentItemNumber5, '')
						)
	GROUP BY 
		s.CustomerNumber
	,	s.SalesQuoteNumber
	,	s.SalesQuoteLineNumber
	,	s.ItemNumber
	,	s.ItemDescription
	,	s.ComponentItemNumber
	,	s.ComponentItemDescription
	,	s.ComponentItemLevel
	,	s.BOMQuantity
	,	s.BOMUnitOfMeasure
	,	s.CostTypeUsed
	,	s.CostMaterialWorksheet
	,	s.CostMaterialMarketExtended
	,	s.RequestedDeliveryDate
	ORDER BY 
		s.ItemNumber
	,	s.ComponentItemLevel
	,	s.ComponentItemNumber

	--DECLARE @PriorQuote_PriorItem TABLE 
	--(
	--	PriorCustomerNumber_BillTo			VARCHAR(20)
	--,	PriorSalesQuoteNumber				VARCHAR(20)
	--,	PriorQuoteLineNumber				VARCHAR(20)
	--,	PriorItemNumber						VARCHAR(20)
	--,	PriorItemDescription				VARCHAR(50)
	--,	PriorComponentItemNumber			VARCHAR(20)
	--,	PriorComponentItemDescription		VARCHAR(50)
	--,	PriorComponentItemLevel				INTEGER
	--,	PriorBOMQuantity					DECIMAL(38,20)
	--,	PriorBOMUnitOfMeasure				VARCHAR(10)
	--,	PriorCostTypeUsed					VARCHAR(10)
	--,	PriorCostMaterialWorksheet			DECIMAL(38,20)
	--,	PriorCostMaterialMarketExtended		DECIMAL(38,20)
	--,	PriorRequestedDeliveryDate			DATETIME
	--)
	--INSERT INTO @PriorQuote_PriorItem
	--(	
	--	PriorCustomerNumber_BillTo
	--,	PriorSalesQuoteNumber
	--,	PriorQuoteLineNumber
	--,	PriorItemNumber
	--,	PriorItemDescription
	--,	PriorComponentItemNumber
	--,	PriorComponentItemDescription
	--,	PriorComponentItemLevel
	--,	PriorBOMQuantity
	--,	PriorBOMUnitOfMeasure
	--,	PriorCostTypeUsed
	--,	PriorCostMaterialWorksheet
	--,	PriorCostMaterialMarketExtended
	--,	PriorRequestedDeliveryDate
	--)
	SELECT 
		s.CustomerNumber				AS 'PriorCustomerNumber_BillTo'
	,	s.SalesQuoteNumber				AS 'PriorSalesQuoteNumber'
	,	s.SalesQuoteLineNumber			AS 'PriorQuoteLineNumber'
	,	s.ItemNumber					AS 'PriorItemNumber'
	,	s.ItemDescription				AS 'PriorItemDescription'
	,	s.ComponentItemNumber			AS 'PriorComponentItemNumber'
	,	s.ComponentItemDescription		AS 'PriorComponentItemDescription'
	,	s.ComponentItemLevel			AS 'PriorComponentItemLevel'
	,	s.BOMQuantity					AS 'PriorBOMQuantity'
	,	s.BOMUnitOfMeasure				AS 'PriorBOMUnitOfMeasure'
	,	s.CostTypeUsed					AS 'PriorCostTypeUsed'
	,	s.CostMaterialWorksheet			AS 'PriorCostMaterialWorksheet'
	,	s.CostMaterialMarketExtended	AS 'PriorCostMaterialMarketExtended'
	,	s.RequestedDeliveryDate			AS 'PriorRequestedDeliveryDate'
	INTO #Prior
	FROM dw.cost.vStd_SalesQuoteCostLineBOM s (NOLOCK)
	WHERE s.SalesQuoteNumber = @PriorSalesQuoteNumber
	AND s.ItemNumber IN (
								ISNULL(@PriorItemNumber1, '')
							,	ISNULL(@PriorItemNumber2, '')
							,	ISNULL(@PriorItemNumber3, '')
							,	ISNULL(@PriorItemNumber4, '')
							,	ISNULL(@PriorItemNumber5, '')
						)
	GROUP BY 
		s.CustomerNumber
	,	s.SalesQuoteNumber
	,	s.SalesQuoteLineNumber
	,	s.ItemNumber
	,	s.ItemDescription
	,	s.ComponentItemNumber
	,	s.ComponentItemDescription
	,	s.ComponentItemLevel
	,	s.BOMQuantity
	,	s.BOMUnitOfMeasure
	,	s.CostTypeUsed
	,	s.CostMaterialWorksheet
	,	s.CostMaterialMarketExtended
	,	s.RequestedDeliveryDate
	ORDER BY 
		s.ItemNumber
	,	s.ComponentItemLevel
	,	s.ComponentItemNumber

	-- Output
	SELECT
		c.CurrentCustomerNumber_BillTo
	,	c.CurrentSalesQuoteNumber
	,	c.CurrentItemNumber
	,	MAX(c.CurrentItemDescription)				AS 'CurrentItemDescription'
	,	c.CurrentComponentItemNumber
	,	MAX(c.CurrentComponentItemDescription)		AS 'CurrentComponentItemDescription'
	,	c.CurrentComponentItemLevel
	,	SUM(c.CurrentBOMQuantity)					AS 'CurrentBOMQuantity'
	,	c.CurrentBOMUnitOfMeasure
	,	c.CurrentCostTypeUsed
	,	SUM(c.CurrentCostMaterialWorksheet)			AS 'CurrentCostMaterialWorksheet'
	,	SUM(c.CurrentCostMaterialMarketExtended)	AS 'CurrentCostMaterialMarketExtended'
	,	MAX(c.CurrentRequestedDeliveryDate)			AS 'CurrentRequestedDeliveryDate'
	,	p.PriorSalesQuoteNumber
	,	p.PriorItemNumber
	,	MAX(p.PriorItemDescription)					AS 'PriorItemDescription'
	,	p.PriorComponentItemNumber
	,	MAX(p.PriorComponentItemDescription)		AS 'PriorComponentItemDescription'
	,	p.PriorComponentItemLevel
	,	sum(p.PriorBOMQuantity)						AS 'PriorBOMQuantity'
	,	p.PriorBOMUnitOfMeasure
	,	p.PriorCostTypeUsed
	,	SUM(p.PriorCostMaterialWorksheet)			AS 'PriorCostMaterialWorksheet'
	,	SUM(p.PriorCostMaterialMarketExtended)		AS 'PriorCostMaterialMarketExtended'
	,	MAX(p.PriorRequestedDeliveryDate)			AS 'PriorRequestedDeliveryDate'
	,	CASE 	
			WHEN (LEN(SUM(c.CurrentCostMaterialMarketExtended)) > 0 OR LEN(SUM(p.PriorCostMaterialMarketExtended)) > 0)
				THEN ISNULL(SUM(c.CurrentCostMaterialMarketExtended), 0) - ISNULL(SUM(p.PriorCostMaterialMarketExtended), 0)
			ELSE 			
				NULL
		END											AS 'FavUnfavCostChange'
	,	ISNULL(SUM(c.CurrentBOMQuantity), 0) 
		- 
		ISNULL(SUM(p.PriorBOMQuantity), 0)				AS 'FormulaChange'


	FROM #Current C
	LEFT OUTER JOIN #Prior P 
		ON p.PriorCustomerNumber_BillTo = c.CurrentCustomerNumber_BillTo 
		AND p.PriorItemNumber = c.CurrentItemNumber 
		AND p.PriorComponentItemNumber = c.CurrentComponentItemNumber 
		AND p.PriorComponentItemLevel = c.CurrentComponentItemLevel
	GROUP BY 
		c.CurrentCustomerNumber_BillTo
	,	c.CurrentSalesQuoteNumber
	,	p.PriorSalesQuoteNumber
	,	c.CurrentItemNumber
	,	p.PriorItemNumber
	,	c.CurrentComponentItemNumber
	,	p.PriorComponentItemNumber
	,	c.CurrentBOMUnitOfMeasure
	,	p.PriorBOMUnitOfMeasure
	,	c.CurrentCostTypeUsed
	,	p.PriorCostTypeUsed
	,	c.CurrentComponentItemLevel
	,	p.PriorComponentItemLevel
	ORDER BY 
		c.CurrentItemNumber
	,	p.PriorItemNumber
	,	c.CurrentComponentItemLevel
	,	p.PriorComponentItemLevel
	,	c.CurrentComponentItemNumber
	,	p.PriorComponentItemNumber
	,	c.CurrentCostTypeUsed	

	DROP TABLE #Current, #Prior;
	

END

Open in new window

0
 
metropiaAuthor Commented:
Hi Paul,

Yes, I am referring to the lines 262, 268

I am ordering by c.CurrentItemNumber,  p.PriorItemNumber

and some other columns.

But my problem is this; parameters @CurrentItemNumber1, @PriorItemNumber1, @CurrentItemNumber2, @PriorItemNumber2, @CurrentItemNumber3, @PriorItemNumber3, @CurrentItemNumber4, @PriorItemNumber4, @CurrentItemNumber5, @PriorItemNumber5

may have values like:

'11039', '11039', '10610', '10610', '11745', '11745', '11008', '11008', '11498', '11498'

so when the ORDER BY I currently have is applied, the order in which the item numbers are displayed, is not the same as the order in which the parameters came in.

10610 would go before 11039, just to mention one example.

What I would like to do, is to be able to ORDER BY at least as primary, in the same way the parameters come in.

Does this make sense?

Thank you.
0
 
PortletPaulCommented:
order by
case
   when [field ] = @param1 then 1
   when [field ] = @param2 then 2
   when [field ] = @param3 then 3
   when [field ] = @param4 then 4
    ...
   when [field ] = @param10 then 10
   else 20
end
, next_field_to_sort_by
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
metropiaAuthor Commented:
interesting. let me give it a try. will post results.

thank you!
0
 
metropiaAuthor Commented:
thank you.

final version:

	-- Output
	SELECT
		c.CurrentCustomerNumber_BillTo
	,	c.CurrentSalesQuoteNumber
	,	c.CurrentItemNumber
	,	MAX(c.CurrentItemDescription)				AS 'CurrentItemDescription'
	,	c.CurrentComponentItemNumber
	,	MAX(c.CurrentComponentItemDescription)		AS 'CurrentComponentItemDescription'
	,	SUM(c.CurrentBOMQuantity)					AS 'CurrentBOMQuantity'
	,	c.CurrentBOMUnitOfMeasure
	,	c.CurrentCostTypeUsed
	,	SUM(c.CurrentCostMaterialWorksheet)			AS 'CurrentCostMaterialWorksheet'
	,	SUM(c.CurrentCostMaterialMarketExtended)	AS 'CurrentCostMaterialMarketExtended'
	,	MAX(c.CurrentRequestedDeliveryDate)			AS 'CurrentRequestedDeliveryDate'
	,	p.PriorSalesQuoteNumber
	,	p.PriorItemNumber
	,	MAX(p.PriorItemDescription)					AS 'PriorItemDescription'
	,	p.PriorComponentItemNumber
	,	MAX(p.PriorComponentItemDescription)		AS 'PriorComponentItemDescription'
	,	SUM(p.PriorBOMQuantity)						AS 'PriorBOMQuantity'
	,	p.PriorBOMUnitOfMeasure
	,	p.PriorCostTypeUsed
	,	SUM(p.PriorCostMaterialWorksheet)			AS 'PriorCostMaterialWorksheet'
	,	SUM(p.PriorCostMaterialMarketExtended)		AS 'PriorCostMaterialMarketExtended'
	,	MAX(p.PriorRequestedDeliveryDate)			AS 'PriorRequestedDeliveryDate'
	,	CASE 	
			WHEN (LEN(SUM(c.CurrentCostMaterialMarketExtended)) > 0 OR LEN(SUM(p.PriorCostMaterialMarketExtended)) > 0)
				THEN ISNULL(SUM(c.CurrentCostMaterialMarketExtended), 0) - ISNULL(SUM(p.PriorCostMaterialMarketExtended), 0)
			ELSE 			
				NULL
		END											AS 'FavUnfavCostChange'
	,	ISNULL(SUM(c.CurrentBOMQuantity), 0)		AS 'CurrentBOMQuantity'
	,	ISNULL(SUM(p.PriorBOMQuantity), 0)			AS 'FormulaChange'
	FROM #Current C
	LEFT OUTER JOIN #Prior P 
		ON p.PriorCustomerNumber_BillTo = c.CurrentCustomerNumber_BillTo 
		AND p.PriorComponentItemNumber = c.CurrentComponentItemNumber 
		AND p.PriorComponentItemDescription = c.CurrentComponentItemDescription
		AND p.PriorQuoteLineNumber = c.CurrentQuoteLineNumber		
	GROUP BY 
		c.CurrentCustomerNumber_BillTo
	,	c.CurrentSalesQuoteNumber	
	,	c.CurrentItemNumber
	,	c.CurrentComponentItemNumber
	,	c.CurrentBOMUnitOfMeasure
	,	c.CurrentCostTypeUsed
	,	p.PriorSalesQuoteNumber
	,	p.PriorItemNumber
	,	p.PriorComponentItemNumber
	,	p.PriorBOMUnitOfMeasure
	,	p.PriorCostTypeUsed
	ORDER BY 
		CASE 
			WHEN c.CurrentItemNumber = @CurrentItemNumber1 THEN 1
			WHEN c.CurrentItemNumber = @CurrentItemNumber2 THEN 2
			WHEN c.CurrentItemNumber = @CurrentItemNumber3 THEN 3
			WHEN c.CurrentItemNumber = @CurrentItemNumber4 THEN 4
			WHEN c.CurrentItemNumber = @CurrentItemNumber5 THEN 5
		END
	,	CASE 
			WHEN p.PriorItemNumber = @PriorItemNumber1 THEN 1
			WHEN p.PriorItemNumber = @PriorItemNumber2 THEN 2
			WHEN p.PriorItemNumber = @PriorItemNumber3 THEN 3
			WHEN p.PriorItemNumber = @PriorItemNumber4 THEN 4
			WHEN p.PriorItemNumber = @PriorItemNumber5 THEN 5
		END
	, 	c.CurrentComponentItemNumber
	,	p.PriorComponentItemNumber

Open in new window

0
 
PortletPaulCommented:
very nice

btw: I'm a big fan of "comma first" formatting
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now