-- **************************************************************
-- Script Name  	: PM_5_0_FormatDateOfBirth.sql
-- Tracker job    	: 50451
-- Release Date	:  

-- ***************************************************************
-- Dependancies :
 
--  Viztopia 4.6 + 


--	22964_CommonScriptLoggingSystem.sql

-- ***************************************************************
-- Description of issue and symptoms:
  -- When integration is chosen - date of birth can sometimes be invalid - and can also have inconsistent layout
 
-- ***************************************************************
-- Brief description of what script does:
 -- This script removes the invalid Date of Birth entries.
 -- This script also formats the date of birth consistently as DD MMM YYYY

-- ***************************************************************
-- Script execution instructions:

-- 1. Backup data
-- 2. Pre-run tests:
-- 	* 
-- 3. Run script(s)
-- 4. Backup data
-- 5. Check the script log for details
-- 6. Post-run tests
--	*

-- ***************************************************************/
-- Switch to default viz database 
--USE VPMSER
--GO

SET NOCOUNT ON
GO

-- Script name
DECLARE @ScriptName		AS VARCHAR(255)
SET @ScriptName			= 'G_50451_FormatDateOfBirthTaxField.sql'

-- Core variables - for version checks 
DECLARE @Result			AS INT	
DECLARE @LogID			AS INT

DECLARE @SectionName	AS VARCHAR(255)
SET @SectionName		=''

DECLARE @TextToWrite	AS VARCHAR(4000)
SET @TextToWrite		= ''

DECLARE @VIZ31sp1		AS DECIMAL(6,4)
SET @VIZ31sp1			=3.102

DECLARE @VIZ32100		AS DECIMAL(6,4)
SET @VIZ32100			=3.21

DECLARE @VIZ32500		AS DECIMAL(6,4)
SET @VIZ32500			=3.25

DECLARE @VIZ40145		AS DECIMAL(6,4)
SET @VIZ40145			=4.0145

DECLARE @VIZ40147		AS DECIMAL(6,4)
SET @VIZ40147			=4.0147

DECLARE @VIZPRI41		AS DECIMAL(6,4)
SET @VIZPRI41			=4.1920

DECLARE @VIZPRI43		AS DECIMAL(6,4)
SET @VIZPRI43			=4.19999

DECLARE @VIZ41930		AS DECIMAL(6,4)
SET @VIZ41930			=4.1930

DECLARE @VIZ419325		AS DECIMAL(6,4)
SET @VIZ419325			=4.19325

DECLARE @VIZ43000		AS DECIMAL(6,4)
SET @VIZ43000			=4.3190

DECLARE @VIZ43218		AS DECIMAL(6,4)
SET @VIZ43218			=4.3218

DECLARE @VIZ44000		AS DECIMAL(6,4)
SET @VIZ44000			=4.4000

DECLARE @VIZPRI45		AS DECIMAL(6,4)
SET @VIZPRI45			=4.4999

DECLARE @VIZPRI46		AS DECIMAL(6,4)
SET @VIZPRI46			=4.5999

DECLARE @VIZPRI47		AS DECIMAL(6,4)
SET @VIZPRI47			=4.6999

DECLARE @VIZ__MAX		AS DECIMAL(6,4)
SET @VIZ__MAX			= @VIZPRI47

DECLARE @SQL_TRUE		AS INT
SET @SQL_TRUE			= 1

DECLARE @CrLf			AS VARCHAR(2)
SET @CrLf				= char(13) + char(10)

-- General constants used within script 

DECLARE @CountEntries	AS INT
SET @CountEntries		= 0

DECLARE @ErrorCode 		AS INT
SET @ErrorCode			= 0


-- General variables used within script 



-- User configurable variables in script










-- Start of script execution 
EXEC @LogID = LogScriptHeader @ScriptName
IF @LogID>0
BEGIN
--	EXEC @Result = IsDatabaseVersionWithin @VIZ40147, @VIZ__MAX	
--	IF @Result = @SQL_TRUE
--	BEGIN

		SET DATEFORMAT DMY ;

		DECLARE @dob VARCHAR(3);
		SET @dob = 'DOB';

		-- Remove invalid entries
		DELETE dbo.ExtraValue
		FROM	dbo.ExtraValue 
			INNER JOIN dbo.ExtraField 
				ON ExtraField.ExtraFieldID = ExtraValue.ExtraFieldID 
			INNER JOIN dbo.ExtraFieldSystem 
				ON ExtraField.ExtraFieldID = ExtraFieldSystem.ExtraFieldID 
				AND ExtraFieldSystem.ExtraFieldSystemTypeCode = @dob
		WHERE isDate(Value)=0 ;
			
		-- Format remaining entries to DD MMM YYYY format

		UPDATE	
			ExtraValue 
		SET		
			[Value] = CONVERT(VARCHAR(11), CONVERT(DATETIME,Value, 103),106)
		FROM	dbo.ExtraValue 
				INNER JOIN dbo.ExtraField 
					ON ExtraField.ExtraFieldID = ExtraValue.ExtraFieldID 
				INNER JOIN dbo.ExtraFieldSystem 
					ON ExtraField.ExtraFieldID = ExtraFieldSystem.ExtraFieldID 
					AND ExtraFieldSystem.ExtraFieldSystemTypeCode = @dob ;

--	END
--	ELSE
--		EXEC LogScriptError @LogID, 'Database version is outside of acceptable range for this script.'
		
	EXEC LogScriptFooter @LogID
END


GO

GO