SQLTeam.com | Weblogs | Forums

Ssrs 2012 report gets same results when parameter values change


#1

In the t-sql 2012 that is that is listed below, is a query that always comes up with the same results in an ssrs 2012 report. It does not make any difference what start and end dates are given to this t-sql. The results are always the same. Note: I placed the important parts of the t-sql in thus area since it is too long to post to this forum). Thus can you tell me what is wrong with the t-sql and/or tell me how I can run only 'parts' of the sql so that I can see what is wrong?
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2013-07-01'
SET @EndDate = '2015-08-01'

; WITH Com_House_1 AS (

SELECT 
		CW.ItemMonth
	,	CW.ItemID
	,	CW.Active
	,	CW.RFSCode		
	,	CW.OldUSDA							AS OldUSDA_CW
	,	CW.NewUSDA							AS NewUSDA_CW
	,	CW.ProductName
	,	ISNULL(CW.BeginningInventory,0)		AS BeginningInventory
	,	ISNULL(CW.TotalInventory,0)			AS TotalInventory
	,	ISNULL(CW.PhysicalInventory,0)		AS PhysicalInventory
	,	ISNULL(CW.SpreadsheetInventory,0)	AS EndingInventory
	,	ISNULL(CW.Variance,0)				AS Variance
	,	ISNULL(I.Processed,0)				AS Processed
	,	AN.Name								AS AccountName
	,	AN.AccountNumber

	--	Beginning Inventory Adj
	
	, ISNULL(cw.SpreadsheetInventory,0) - 
		CASE 
			WHEN MONTH(cw.ItemMonth) = 1 THEN ISNULL(cw.ReceivedJanuary,0)
			WHEN MONTH(cw.ItemMonth) = 2 THEN ISNULL(cw.ReceivedFebruary,0)
			WHEN MONTH(cw.ItemMonth) = 3 THEN ISNULL(cw.ReceivedMarch,0)
			WHEN MONTH(cw.ItemMonth) = 4 THEN ISNULL(cw.ReceivedApril,0)
			WHEN MONTH(cw.ItemMonth) = 5 THEN ISNULL(cw.ReceivedMay,0)
			WHEN MONTH(cw.ItemMonth) = 6 THEN ISNULL(cw.ReceivedJune,0)
			WHEN MONTH(cw.ItemMonth) = 7 THEN ISNULL(cw.ReceivedJuly,0)
			WHEN MONTH(cw.ItemMonth) = 8 THEN ISNULL(cw.ReceivedAugust,0)
			WHEN MONTH(cw.ItemMonth) = 9 THEN ISNULL(cw.ReceivedSeptember,0)
			WHEN MONTH(cw.ItemMonth) = 10 THEN ISNULL(cw.ReceivedOctober,0)
			WHEN MONTH(cw.ItemMonth) = 11 THEN ISNULL(cw.ReceivedNovember,0)
			WHEN MONTH(cw.ItemMonth) = 12 THEN ISNULL(cw.ReceivedDecember,0)
			END 
			END AS BeginningInventoryAdj		
				
	--	MontlyUsage
	
	,	CASE 
			WHEN MONTH(ItemMonth) = 1 THEN ISNULL(cw.ReceivedJanuary,0)
			WHEN MONTH(ItemMonth) = 2 THEN ISNULL(cw.ReceivedFebruary,0)
			WHEN MONTH(ItemMonth) = 3 THEN ISNULL(cw.ReceivedMarch,0)
			WHEN MONTH(ItemMonth) = 4 THEN ISNULL(cw.ReceivedApril,0)
			WHEN MONTH(ItemMonth) = 5 THEN ISNULL(cw.ReceivedMay,0)
			WHEN MONTH(ItemMonth) = 6 THEN ISNULL(cw.ReceivedJune,0)
			WHEN MONTH(ItemMonth) = 7 THEN ISNULL(cw.ReceivedJuly,0)
			WHEN MONTH(ItemMonth) = 8 THEN ISNULL(cw.ReceivedAugust,0)
			WHEN MONTH(ItemMonth) = 9 THEN ISNULL(cw.ReceivedSeptember,0)
			WHEN MONTH(ItemMonth) = 10 THEN ISNULL(cw.ReceivedOctober,0)
			WHEN MONTH(ItemMonth) = 11 THEN ISNULL(cw.ReceivedNovember,0)
			WHEN MONTH(ItemMonth) = 12 THEN ISNULL(cw.ReceivedDecember,0)
			ELSE 0 END AS MonthlyReceipts
	,	CASE WHEN MONTH(ItemMonth) = 1 THEN ISNULL(ShippedJanuary,0)
			WHEN MONTH(ItemMonth) = 2 THEN ISNULL(ShippedFebruary,0)
			WHEN MONTH(ItemMonth) = 3 THEN ISNULL(ShippedMarch,0)
			WHEN MONTH(ItemMonth) = 4 THEN ISNULL(ShippedApril,0)
			WHEN MONTH(ItemMonth) = 5 THEN ISNULL(ShippedMay,0)
			WHEN MONTH(ItemMonth) = 6 THEN ISNULL(ShippedJune,0)
			WHEN MONTH(ItemMonth) = 7 THEN ISNULL(ShippedJuly,0)
			WHEN MONTH(ItemMonth) = 8 THEN ISNULL(ShippedAugust,0)
			WHEN MONTH(ItemMonth) = 9 THEN ISNULL(ShippedSeptember,0)
			WHEN MONTH(ItemMonth) = 10 THEN ISNULL(ShippedOctober,0)
			WHEN MONTH(ItemMonth) = 11 THEN ISNULL(ShippedNovember,0)
			WHEN MONTH(ItemMonth) = 12 THEN ISNULL(ShippedDecember,0)
			ELSE 0 END AS MonthlyUsage		
	
	
	
			
	
			
	-- Usage Processing
	
	, CASE WHEN (SELECT SUM(a.CasesDelivered) FROM (
					SELECT TOP 1 bola.CasesDelivered FROM [NC].[Lad] bola
					WHERE bola.Active = 1
					AND bola.RFSCode = cw.RFSCode
					AND bola.ItemMonth <= CW.ItemMonth
					ORDER BY bola.BillOfLadingDate DESC) a) > cw.Inven
			THEN (SELECT ROUND(SUM(b.ProcessingCharge)/NULLIF(SUM(b.CasesDelivered),0), 4) AS WeightedAvereage FROM (
				SELECT TOP 1 bolb.CasesDelivered,  bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge FROM [NC].[Lad] bolb
				WHERE bolb.Active = 1
				AND bolb.RFSCode = cw.RFSCode
				AND bolb.ItemMonth <= CW.ItemMonth
				ORDER BY bolb.BillOfLadingDate DESC) b) * CASE WHEN MONTH(CW.ItemMonth) = 1 THEN ISNULL(ShippedJanuary,0)
														WHEN MONTH(CW.ItemMonth) = 2 THEN ISNULL(ShippedFebruary,0)
														WHEN MONTH(CW.ItemMonth) = 3 THEN ISNULL(ShippedMarch,0)
														WHEN MONTH(CW.ItemMonth) = 4 THEN ISNULL(ShippedApril,0)
														WHEN MONTH(CW.ItemMonth) = 5 THEN ISNULL(ShippedMay,0)
														WHEN MONTH(CW.ItemMonth) = 6 THEN ISNULL(ShippedJune,0)
														WHEN MONTH(CW.ItemMonth) = 7 THEN ISNULL(ShippedJuly,0)
														WHEN MONTH(CW.ItemMonth) = 8 THEN ISNULL(ShippedAugust,0)
														WHEN MONTH(CW.ItemMonth) = 9 THEN ISNULL(ShippedSeptember,0)
														WHEN MONTH(CW.ItemMonth) = 10 THEN ISNULL(ShippedOctober,0)
														WHEN MONTH(CW.ItemMonth) = 11 THEN ISNULL(ShippedNovember,0)
														WHEN MONTH(CW.ItemMonth) = 12 THEN ISNULL(ShippedDecember,0)
														END
			WHEN (SELECT SUM(a.CasesDelivered) FROM (
					SELECT TOP 2 bola.CasesDelivered FROM [NC].[Lad] bola
					WHERE bola.Active = 1
					AND bola.RFSCode = cw.RFSCode
					AND bola.ItemMonth <= CW.ItemMonth
					ORDER BY bola.BillOfLadingDate DESC) a) > cw.Inven
			THEN (SELECT ROUND(SUM(b.ProcessingCharge)/NULLIF(SUM(b.CasesDelivered),0), 4) AS WeightedAvereage FROM (
				SELECT TOP 2 bolb.CasesDelivered,  bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge FROM [NC].[Lad] bolb
				WHERE bolb.Active = 1
				AND bolb.RFSCode = cw.RFSCode
				AND bolb.ItemMonth <= CW.ItemMonth
				ORDER BY bolb.BillOfLadingDate DESC) b) * CASE WHEN MONTH(CW.ItemMonth) = 1 THEN ISNULL(ShippedJanuary,0)
														WHEN MONTH(CW.ItemMonth) = 2 THEN ISNULL(ShippedFebruary,0)
														WHEN MONTH(CW.ItemMonth) = 3 THEN ISNULL(ShippedMarch,0)
														WHEN MONTH(CW.ItemMonth) = 4 THEN ISNULL(ShippedApril,0)
														WHEN MONTH(CW.ItemMonth) = 5 THEN ISNULL(ShippedMay,0)
														WHEN MONTH(CW.ItemMonth) = 6 THEN ISNULL(ShippedJune,0)
														WHEN MONTH(CW.ItemMonth) = 7 THEN ISNULL(ShippedJuly,0)
														WHEN MONTH(CW.ItemMonth) = 8 THEN ISNULL(ShippedAugust,0)
														WHEN MONTH(CW.ItemMonth) = 9 THEN ISNULL(ShippedSeptember,0)
														WHEN MONTH(CW.ItemMonth) = 10 THEN ISNULL(ShippedOctober,0)
														WHEN MONTH(CW.ItemMonth) = 11 THEN ISNULL(ShippedNovember,0)
														WHEN MONTH(CW.ItemMonth) = 12 THEN ISNULL(ShippedDecember,0)
														END
			WHEN (SELECT SUM(a.CasesDelivered) FROM (
					SELECT TOP 3 bola.CasesDelivered FROM [NC].[Lad] bola
					WHERE bola.Active = 1
					AND bola.RFSCode = cw.RFSCode
					AND bola.ItemMonth <= CW.ItemMonth
					ORDER BY bola.BillOfLadingDate DESC) a) > cw.Inven
			THEN (SELECT ROUND(SUM(b.ProcessingCharge)/NULLIF(SUM(b.CasesDelivered),0), 4) AS WeightedAvereage FROM (
				SELECT TOP 3 bolb.CasesDelivered,  bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge FROM [NC].[Lad] bolb
				WHERE bolb.Active = 1
				AND bolb.RFSCode = cw.RFSCode
				AND bolb.ItemMonth <= CW.ItemMonth
				ORDER BY bolb.BillOfLadingDate DESC) b) * CASE WHEN MONTH(CW.ItemMonth) = 1 THEN ISNULL(ShippedJanuary,0)
														WHEN MONTH(CW.ItemMonth) = 2 THEN ISNULL(ShippedFebruary,0)
														WHEN MONTH(CW.ItemMonth) = 3 THEN ISNULL(ShippedMarch,0)
														WHEN MONTH(CW.ItemMonth) = 4 THEN ISNULL(ShippedApril,0)
														WHEN MONTH(CW.ItemMonth) = 5 THEN ISNULL(ShippedMay,0)
														WHEN MONTH(CW.ItemMonth) = 6 THEN ISNULL(ShippedJune,0)
														WHEN MONTH(CW.ItemMonth) = 7 THEN ISNULL(ShippedJuly,0)
														WHEN MONTH(CW.ItemMonth) = 8 THEN ISNULL(ShippedAugust,0)
														WHEN MONTH(CW.ItemMonth) = 9 THEN ISNULL(ShippedSeptember,0)
														WHEN MONTH(CW.ItemMonth) = 10 THEN ISNULL(ShippedOctober,0)
														WHEN MONTH(CW.ItemMonth) = 11 THEN ISNULL(ShippedNovember,0)
														WHEN MONTH(CW.ItemMonth) = 12 THEN ISNULL(ShippedDecember,0)
														END
			WHEN (SELECT SUM(a.CasesDelivered) FROM (
					SELECT TOP 4 bola.CasesDelivered FROM [NC].[Lad] bola
					WHERE bola.Active = 1
					AND bola.RFSCode = cw.RFSCode
					AND bola.ItemMonth <= CW.ItemMonth
					ORDER BY bola.BillOfLadingDate DESC) a) > cw.Inven
			THEN (SELECT ROUND(SUM(b.ProcessingCharge)/NULLIF(SUM(b.CasesDelivered),0), 4) AS WeightedAvereage FROM (
				SELECT TOP 4 bolb.CasesDelivered,  bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge FROM [NC].[Lad] bolb
				WHERE bolb.Active = 1
				AND bolb.RFSCode = cw.RFSCode
				AND bolb.ItemMonth <= CW.ItemMonth
				ORDER BY bolb.BillOfLadingDate DESC) b) * CASE WHEN MONTH(CW.ItemMonth) = 1 THEN ISNULL(ShippedJanuary,0)
														WHEN MONTH(CW.ItemMonth) = 2 THEN ISNULL(ShippedFebruary,0)
														WHEN MONTH(CW.ItemMonth) = 3 THEN ISNULL(ShippedMarch,0)
														WHEN MONTH(CW.ItemMonth) = 4 THEN ISNULL(ShippedApril,0)
														WHEN MONTH(CW.ItemMonth) = 5 THEN ISNULL(ShippedMay,0)
														WHEN MONTH(CW.ItemMonth) = 6 THEN ISNULL(ShippedJune,0)
														WHEN MONTH(CW.ItemMonth) = 7 THEN ISNULL(ShippedJuly,0)
														WHEN MONTH(CW.ItemMonth) = 8 THEN ISNULL(ShippedAugust,0)
														WHEN MONTH(CW.ItemMonth) = 9 THEN ISNULL(ShippedSeptember,0)
														WHEN MONTH(CW.ItemMonth) = 10 THEN ISNULL(ShippedOctober,0)
														WHEN MONTH(CW.ItemMonth) = 11 THEN ISNULL(ShippedNovember,0)
														WHEN MONTH(CW.ItemMonth) = 12 THEN ISNULL(ShippedDecember,0)
														END
			WHEN (SELECT SUM(a.CasesDelivered) FROM (
					SELECT TOP 5 bola.CasesDelivered FROM [NC].[Lad] bola
					WHERE bola.Active = 1
					AND bola.RFSCode = cw.RFSCode
					AND bola.ItemMonth <= CW.ItemMonth
					ORDER BY bola.BillOfLadingDate DESC) a) > cw.Inven
			THEN (SELECT ROUND(SUM(b.ProcessingCharge)/NULLIF(SUM(b.CasesDelivered),0), 4) AS WeightedAvereage FROM (
				SELECT TOP 5 bolb.CasesDelivered,  bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge FROM [NC].[Lad] bolb
				WHERE bolb.Active = 1
				AND bolb.RFSCode = cw.RFSCode
				AND bolb.ItemMonth <= CW.ItemMonth
				ORDER BY bolb.BillOfLadingDate DESC) b) * CASE WHEN MONTH(CW.ItemMonth) = 1 THEN ISNULL(ShippedJanuary,0)
														WHEN MONTH(CW.ItemMonth) = 2 THEN ISNULL(ShippedFebruary,0)
														WHEN MONTH(CW.ItemMonth) = 3 THEN ISNULL(ShippedMarch,0)
														WHEN MONTH(CW.ItemMonth) = 4 THEN ISNULL(ShippedApril,0)
														WHEN MONTH(CW.ItemMonth) = 5 THEN ISNULL(ShippedMay,0)
														WHEN MONTH(CW.ItemMonth) = 6 THEN ISNULL(ShippedJune,0)
														WHEN MONTH(CW.ItemMonth) = 7 THEN ISNULL(ShippedJuly,0)
														WHEN MONTH(CW.ItemMonth) = 8 THEN ISNULL(ShippedAugust,0)
														WHEN MONTH(CW.ItemMonth) = 9 THEN ISNULL(ShippedSeptember,0)
														WHEN MONTH(CW.ItemMonth) = 10 THEN ISNULL(ShippedOctober,0)
														WHEN MONTH(CW.ItemMonth) = 11 THEN ISNULL(ShippedNovember,0)
														WHEN MONTH(CW.ItemMonth) = 12 THEN ISNULL(ShippedDecember,0)
														END
			WHEN (SELECT SUM(a.CasesDelivered) FROM (
					SELECT TOP 1 bola.CasesDelivered FROM [NC].[Lad] bola
					WHERE bola.Active = 1
					AND bola.RFSCode = cw.RFSCode
					AND bola.ItemMonth <= CW.ItemMonth
					ORDER BY bola.BillOfLadingDate DESC) a) <= cw.Inven
			THEN (SELECT ROUND(SUM(b.ProcessingCharge)/NULLIF(SUM(b.CasesDelivered),0), 4) AS WeightedAvereage FROM (
				SELECT TOP 1 bolb.CasesDelivered,  bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge FROM [NC].[Lad] bolb
				WHERE bolb.Active = 1
				AND bolb.RFSCode = cw.RFSCode
				AND bolb.ItemMonth <= CW.ItemMonth
				ORDER BY bolb.BillOfLadingDate DESC) b) * CASE WHEN MONTH(CW.ItemMonth) = 1 THEN ISNULL(ShippedJanuary,0)
														WHEN MONTH(CW.ItemMonth) = 2 THEN ISNULL(ShippedFebruary,0)
														WHEN MONTH(CW.ItemMonth) = 3 THEN ISNULL(ShippedMarch,0)
														WHEN MONTH(CW.ItemMonth) = 4 THEN ISNULL(ShippedApril,0)
														WHEN MONTH(CW.ItemMonth) = 5 THEN ISNULL(ShippedMay,0)
														WHEN MONTH(CW.ItemMonth) = 6 THEN ISNULL(ShippedJune,0)
														WHEN MONTH(CW.ItemMonth) = 7 THEN ISNULL(ShippedJuly,0)
														WHEN MONTH(CW.ItemMonth) = 8 THEN ISNULL(ShippedAugust,0)
														WHEN MONTH(CW.ItemMonth) = 9 THEN ISNULL(ShippedSeptember,0)
														WHEN MONTH(CW.ItemMonth) = 10 THEN ISNULL(ShippedOctober,0)
														WHEN MONTH(CW.ItemMonth) = 11 THEN ISNULL(ShippedNovember,0)
														WHEN MONTH(CW.ItemMonth) = 12 THEN ISNULL(ShippedDecember,0)
														END
	

FROM	NC.ComsWarehouse CW
		LEFT OUTER JOIN
		NC.Items I
			ON CW.RFSCode = I.RFSCode
		LEFT OUTER JOIN
		NC.AccountNumbers AN
			ON AN.AccountNumberID = I.AccountNumberID
			
WHERE
		(	ISNULL(CW.BeginningInventory,0) <> 0 OR ISNULL(CW.SpreadsheetInventory,0) <> 0 )
		AND ISNULL(CW.Active,0) = 1 
		AND	ISNULL(I.Active, 0) = 1

), Delivered_Items AS (

SELECT DISTINCT 
		RFSCode
	,	MAX(OldUSDA) AS OldUSDA_DI
	,	MAX(NewUSDA) AS NewUSDA_DI
	
FROM		NC.DeliveredItems 
WHERE		Active = 1 
GROUP BY	RFSCode

), Com_House_2 AS (

SELECT		
			CW.ItemMonth
		,	CW.ItemID
		,	CW.ProductName
		,	CW.RFSCode
		,	CASE 
				WHEN CW.OldUSDA_CW IS NULL AND DI.OldUSDA_DI IS NOT NULL THEN DI.OldUSDA_DI
				WHEN CW.OldUSDA_CW IS NOT NULL AND DI.OldUSDA_DI IS NULL THEN CW.OldUSDA_CW
					ELSE CW.OldUSDA_CW END AS OldUSDA
		,	CASE 
				WHEN CW.NewUSDA_CW IS NULL AND DI.NewUSDA_DI IS NOT NULL THEN DI.NewUSDA_DI 
				WHEN CW.NewUSDA_CW IS NOT NULL AND DI.NewUSDA_DI IS NULL THEN CW.NewUSDA_CW
					ELSE CW.NewUSDA_CW END AS NewUSDA
		,	CW.Processed
		,	CW.BeginningInventory
		,	CW.BeginningInventoryAdj
		,	CW.TotalInventory
		,	CW.PhysicalInventory
		,	CW.EndingInventory		
		,	CW.Variance
		,	ISNULL(CW.AccountNumber,0)			AS AccountNumber
		,	ISNULL(CW.AccountName,'Unknown')	AS AccountName
		,	CW.MonthlyReceipts
		,	CW.MonthlyUsage
		,	CW.CaseRate
		,	CW.ProcessingCharge
		,	CW.USDAStorageCharge
		,	CW.PerUnitCharge
		,	CW.EndingInventoryExtension
		,	CW.ReceiptValue
		,	CW.UsageProcessing
		,	CW.UsageStorage
		,	CW.UsageInventoryExtension
		

FROM		
			Com_House_1 CW
			LEFT OUTER JOIN 
			Delivered_Items DI
				ON CW.RFSCode = DI.RFSCode

), Com_House_Final AS (

SELECT		
			ItemMonth
		,	AccountNumber
		,	AccountName
		,	ItemID
		,	ProductName
		,	RFSCode
		,	OldUSDA
		,	NewUSDA
		,	ISNULL(NewUSDA,OldUSDA) AS USDA
		,	Processed
		--,	BeginningInventory
		,	BeginningInventoryAdj AS BeginningInventory
		,	EndingInventory					
		--,	PhysicalInventory
		,	MonthlyReceipts
		,	MonthlyUsage
		,	CaseRate
		,	ProcessingCharge
		,	USDAStorageCharge
		,	PerUnitCharge
		,	EndingInventoryExtension
		,	ReceiptValue
		,	UsageProcessing
		,	UsageStorage
		,	UsageInventoryExtension
		
FROM		
			Com_House_2 CWF

), First_Month AS (

	SELECT		
			FirstMonth.ItemMonth
		,	FirstMonth.AccountNumber
		,	FirstMonth.AccountName
		,	FirstMonth.ProductName
		,	FirstMonth.Processed
		,	FirstMonth.RFSCode
		,	FirstMonth.USDA
		,	FirstMonth.BeginningInventory


	FROM
			(SELECT		
				CWF.ItemMonth
			,	CWF.AccountNumber
			,	CWF.AccountName
			,	CWF.ProductName
			,	CWF.Processed
			,	CWF.RFSCode
			,	CWF.USDA
			,	CWF.BeginningInventory
			,	row_number() OVER (PARTITION BY CWF.RFSCode order by CWF.ItemMonth asc) as row


			FROM		Com_House_Final CWF
		
			WHERE

				CWF.ItemMonth		>= @StartDate
			AND CWF.ItemMonth		<= @EndDate)	AS FirstMonth
			
	WHERE ROW = 1	

), Last_Month AS (

	SELECT		
			LastMonth.ItemMonth
		,	LastMonth.RFSCode
		,	LastMonth.EndingInventory
		,	LastMonth.MonthlyReceipts
		,	LastMonth.MonthlyUsage
		,	LastMonth.CaseRate
		,	LastMonth.ProcessingCharge
		,	LastMonth.USDAStorageCharge
		,	LastMonth.PerUnitCharge
		,	LastMonth.EndingInventoryExtension
		,	LastMonth.ReceiptValue
		,	LastMonth.UsageProcessing
		,	LastMonth.UsageStorage
		,	LastMonth.UsageInventoryExtension

	FROM
			(SELECT		
				CWF.ItemMonth
			,	CWF.RFSCode
			,	CWF.MonthlyReceipts
			,	CWF.MonthlyUsage
			,	CWF.EndingInventory
			,	CWF.CaseRate
			,	CWF.ProcessingCharge
			,	CWF.USDAStorageCharge
			,	CWF.PerUnitCharge
			,	CWF.EndingInventoryExtension
			,	CWF.ReceiptValue
			,	CWF.UsageProcessing
			,	CWF.UsageStorage
			,	CWF.UsageInventoryExtension
			,	row_number() OVER (PARTITION BY CWF.RFSCode order by CWF.ItemMonth desc) as row

			FROM		Com_House_Final CWF
		
			WHERE
				CWF.ItemMonth >= @StartDate
			AND CWF.ItemMonth <= @EndDate)		AS LastMonth
			
	WHERE ROW = 1	

), Monthly_Processing AS (

SELECT		
		
			CWF.AccountNumber
		,	CWF.AccountName
		,	CWF.ProductName
		,	CWF.RFSCode
		,	CWF.USDA
		,	SUM (CWF.MonthlyReceipts)			AS Receipts
		,	SUM (CWF.MonthlyUsage)				AS Usage
		,	SUM (CWF.UsageProcessing)			AS UsageProcessingSum		
	
		
FROM		Com_House_Final CWF
	
WHERE	
			CWF.ItemMonth >= @StartDate
		AND CWF.ItemMonth <= @EndDate
		
GROUP BY 

			CWF.AccountNumber
		,	CWF.AccountName
		,	CWF.ProductName
		,	CWF.RFSCode
		,	CWF.USDA
		,	CWF.RFSCode

)

SELECT		
			CONVERT(INT,FM.AccountNumber)	AS AccountNumber
		,	FM.AccountName
		,	FM.ProductName
		,	FM.Processed
		,	FM.RFSCode
		,	FM.USDA
		,	FM.ItemMonth					AS Month_Start
		,	LM.ItemMonth					AS Month_End
		,	FM.BeginningInventory
		,	MP.Usage
		,	MP.Receipts
		,	FM.BeginningInventory + MP.Receipts - MP.Usage AS EndingInventory
		--,	LM.EndingInventory
		,	LM.CaseRate
		,	LM.ProcessingCharge
		,	LM.USDAStorageCharge
		,	LM.PerUnitCharge
		,	(LM.CaseRate + LM.ProcessingCharge) * (FM.BeginningInventory + MP.Receipts - MP.Usage) AS EndingInventoryExtension
		--,	LM.EndingInventoryExtension
		,	LM.ReceiptValue
		,	LM.UsageProcessing
		,	LM.UsageStorage
		,	LM.UsageInventoryExtension

	
		
FROM		
			First_Month FM
			LEFT JOIN 
			Last_Month LM ON LM.RFSCode = FM.RFSCode
			LEFT JOIN
			Monthly_Processing MP
			ON MP.RFSCode = FM.RFSCode

#2

found answer. parameter values were hardcoded