/* Microsoft SQL Server - Scripting			*/
/* Server: ACME65					*/
/* Database: ReportDev					*/
/* Creation Date 4/25/00 11:52:01 AM 			*/

/****** Object:  Stored Procedure dbo.sp_slowChangeDim    Script Date: 4/25/00 11:52:01 AM ******/
if exists (select * from sysobjects where id = object_id('dbo.sp_slowChangeDim') and sysstat & 0xf = 4)
	drop procedure dbo.sp_slowChangeDim
GO

/****** Object:  Stored Procedure dbo.sp_slowChangeDim    Script Date: 4/25/00 11:52:01 AM ******/
/* 

Author: Les Smith
Email:	smithles@pacbell.net
Date:	April 25, 2000

This example is for versioning of data. Most of the time adding data to 
a table uses an Update Else Insert methodolgy. However, in some circumstances
you may need to keep a history of changes/versions. In my example I am creating 
a credit record history for vendors. Other examples include tracking addresses and
name changes (ie women get married and change their last name). In data warehouse terminology
we are working with "Slowly Changing Dimensions".

This is skeleton you can use for building more complex version processing.

*/


CREATE PROCEDURE sp_slowChangeDim
@CompID INT,
@CreditStatus INT =1
AS

--variable to indicate if Company already exists in the database
DECLARE @Exists INT
--the version number
DECLARE @dwCompanyID FLOAT
--variable to hold first version
DECLARE @FirstVersion FLOAT
--valid change or not 0 is valid, 1 not valid
DECLARE @ValidChange INT


--Find out if Company already exists
SELECT @Exists = COUNT(CompanyID)  FROM CompanyCredit
WHERE CompanyID= @CompID

--get the latest version number for that company
SELECT @dwCompanyID = MAX(dwCompanyID) FROM CompanyCredit WHERE CompanyID = @CompID

--need to know if this is a valid version change
--if this is not a valid version change then no changes need to be made
SELECT @ValidChange =  COUNT(*) FROM CompanyCredit WHERE dwCompanyID = @dwCompanyID
			      AND CreditStatus = @CreditStatus

--go through version process if the Company is already
--in database and this is a valid version change.
IF  ( @Exists > 0 AND @ValidChange = 0)
BEGIN
        --get the current ID version of Company's credit status
	
	
	--determine if credit information has really changed, if this is a valid version change
	--if not a valid change then do not do anything
	
		
	--if the max number of versions(9) is matched  drop the first version and renumber
	IF (@dwCompanyID  >= @CompID +.9)
	BEGIN

		SELECT @FirstVersion = MIN(dwCompanyID) FROM CompanyCredit WHERE CompanyID = @CompID

		--Delete First Version
		DELETE CompanyCredit
		WHERE CompanyID = @CompID
		AND dwCompanyID = @FirstVersion

		--Now Decrement all Versions
		UPDATE CompanyCredit
		SET dwCompanyID = dwCompanyID -.1
		WHERE CompanyID = @CompID

	--get the new Max after deletions
	--the new version should be .8
	SELECT @dwCompanyID = MAX(dwCompanyID) FROM CompanyCredit WHERE CompanyID = @CompID

	END
		--increment the ID version
	SELECT @dwCompanyID = @dwCompanyID + .1
	INSERT CompanyCredit(CompanyID, dwCompanyID, CreditStatus)
	VALUES (@CompID, @dwCompanyID, @CreditStatus)


END


--if this Company is being added to the database for the first time
--insert it
IF  ( @Exists < 1)
BEGIN
	INSERT CompanyCredit(CompanyID, dwCompanyID, CreditStatus)
	VALUES (@CompID, @CompID + .1,@CreditStatus)
END
GO