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