SQLTeam.com | Weblogs | Forums

Simplify Approach to the Logic To make it easier to understand

Could someone help me simplify this query? I received a request for it to be simplified and added in comments to update the query but I'm having trouble compiling this. I am pretty green in this area.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [stage].[vp_BIA_InitializeCostingAnalysis]
AS

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [stage].[vp_BIA_InitializeCostingAnalysis]
AS

/*---------------------------------------------------------------------------
  Creation: 
  Change Log:


*/---------------------------------------------------------------------------


DECLARE @LastVersion INT
SELECT @LastVersion = MAX(VersionID)
FROM stage.ct_ProductCostingVersion


--Temp table to get latest active version
IF OBJECT_ID('tempdb..#TempVersion','U') IS NOT NULL DROP TABLE #TempVersion
SELECT ((ROW_NUMBER() OVER(PARTITION BY VersionDate ORDER BY VersionDate)) + VersionID - 1) as RowNum, *
INTO #TempVersion
FROM stage.ct_ProductCostingVersion
WHERE VersionID = @LastVersion


	--QA NOTES | REMOVE NOTES ONCE COMPLETE-- 
	/*
		I think we can simplify the below a bit.
		We need to do the check to see if there is an active version before we do anything else.

		Something like
		
		-- check to see if an active version exists
		-- should return 1/0
		DECLARE @ActiveVersion INT 
		SELECT @ActiveVersion = COUNT(1)
		FROM stage.ct_ProductCostingVersion 
		WHERE ActiveVersion = 1

		IF @ActiveVersion > 0
		BEGIN
			-- if an active version exists (>0) we just need to vall the Load routine
			
			...

		END
		ELSE 
		BEGIN 
			-- otherwise (if an active version doesn't exist (=0) we need to create a version marked as active, then call the load routine

			...

		END 


	*/
	--QA NOTES | REMOVE NOTES ONCE COMPLETE-- 

BEGIN
	INSERT INTO stage.ct_ProductCostingVersion
	(	 [VersionCode]
		,[VersionName]
		,[VersionDate]
		,[VersionNotes]
		,[ActiveVersion]
		  )
	SELECT  
		 CONVERT(varchar, GETDATE(), 112) + '|' + CAST((RowNum + 1) as varchar) as [VersionCode]
		,CAST(DATENAME(MONTH, GETDATE()) as varchar) + ' ' + CAST(RIGHT(DATEPART(Year, GETDATE()), 2) as varchar) + ' Costing' as [VersionName]
		,GETDATE() as [VersionDate]
		,'Version #' + CAST((VersionID + 1) as varchar) as [VersionNotes]
		,1 as [ActiveVersion]
	FROM #TempVersion

--QA NOTES | REMOVE NOTES ONCE COMPLETE-- 
/*
	From here down can be eliminated / simplified, i think, to match the psuedocode / sample in the above notes.
*/
--QA NOTES | REMOVE NOTES ONCE COMPLETE-- 

	WHERE NOT EXISTS (
		SELECT VersionID
		FROM stage.ct_ProductCostingVersion 
		WHERE ActiveVersion = 1
	)
	BEGIN
		IF EXISTS
		(
			SELECT VersionID
			FROM stage.ct_ProductCostingVersion 
			WHERE ActiveVersion = 1
		)
			BEGIN
				exec [stage].[vp_BIA_LoadCostingAnalysis]
			END
	END
END



this looks pretty straightforward and easy to understand for me

Another approach you might use is

https://www.sqlshack.com/understanding-the-sql-merge-statement/