USE [janinetestdata]GO/*Purpose of the sql query is to make corrections to all the errors detectedwhich in essences pertains to all of the tables concerning the LFS Survey .This query will run insertions, updates and deletion commands*/--Variables declaration sectionDECLARE @ErrorMessage varchar(500), @BatchNo varchar(15), @zBarcode varchar(15), @IncorrectValue varchar(100), @ColumnsToFix varchar(25), @Person_Number varchar(7), @CorrectedValue varchar(100), @Description varchar(500), @Command varchar(10), @ED_Three varchar(10), @ED_Four varchar(10), @ED_Five varchar(10), @ED_Six varchar(10), @CORRECTION_STATEMENT nvarchar(900) DECLARE ERRORCursor CURSOR FORSELECT ERRORMESSAGE, BATCH_NO, zBarcode, Incorrect_Value , Columns_To_Fix ,Person_No , Corrected_Value , Err_Description ,Command , ED_THREE , ED_FOUR,ED_FIVE, ED_SIX FROM [janinetestdata].[dbo].[TEST66]OPEN ERRORCursorBEGIN--Fetch first householdFETCH NEXT FROM ERRORCursor into @ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue,@ColumnsToFix ,@Person_Number, @CorrectedValue , @Description, @Command, @ED_Three , @ED_Four , @ED_Five , @ED_Six WHILE @@FETCH_STATUS = 0 BEGIN /*Beginning of Education Commands*/ if @ErrorMessage LIKE 'ED%' BEGIN/*Beginning of Update Command for Education Table*/ if @Command = 'UPDATE' and @Person_Number is not null and @ColumnsToFix is not null and @zBarcode is not null BEGIN set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[EDTE] set ' + CAST(@ColumnsToFix AS nVarchar(50))+ ' = ' + CAST(@CorrectedValue AS nVarchar(15)) + ' WHERE zBarcode = ' + CAST(@zBarcode AS nVarchar(15))+ ' and ' + 'PERSON_NUMBER = ' + CAST(@Person_Number AS nVarchar(7)) print @CORRECTION_STATEMENT EXECUTE (@CORRECTION_STATEMENT) END/*End of Update Command for Education Table*/ END/*end of Fourteen and Over Commands*/ --execute sp_executesql @CORRECTION_STATEMENT FETCH NEXT FROM ERRORCursor into @ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue , @ColumnsToFix , @Person_Number,@CorrectedValue , @Description, @Command, @ED_Three , @ED_Four , @ED_Five , @ED_Six END/*End of begin*/ENDCLOSE ERRORCursorDEALLOCATE ERRORCursorGO
USE [janinetestdata]GO/*Purpose of the sql query is to make corrections to all the errors detectedwhich in essences pertains to all of the tables concerning the LFS Survey .This query will run insertions, updates and deletion commands*/--Variables declaration sectionDECLARE @ErrorMessage varchar(500), @BatchNo varchar(15), @zBarcode varchar(15), @IncorrectValue varchar(100), @ColumnsToFix varchar(25), @Person_Number varchar(7), @CorrectedValue varchar(100), @Description varchar(500), @Command varchar(10), @ED_Three varchar(10), @ED_Four varchar(10), @ED_Five varchar(10), @ED_Six varchar(10), @CORRECTION_STATEMENT nvarchar(900) DECLARE ERRORCursor CURSOR FORSELECT ERRORMESSAGE, BATCH_NO, zBarcode, Incorrect_Value , Columns_To_Fix ,Person_No , Corrected_Value , Err_Description ,Command , ED_THREE , ED_FOUR,ED_FIVE, ED_SIX FROM [janinetestdata].[dbo].[TEST66]OPEN ERRORCursorBEGIN--Fetch first householdFETCH NEXT FROM ERRORCursor into @ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue,@ColumnsToFix ,@Person_Number, @CorrectedValue , @Description, @Command, @ED_Three , @ED_Four , @ED_Five , @ED_Six WHILE @@FETCH_STATUS = 0 BEGIN /*Beginning of Education Commands*/ if @ErrorMessage LIKE 'ED%' BEGIN/*Beginning of Update Command for Education Table*/ if @Command = 'UPDATE' and @Person_Number is not null and @ColumnsToFix is not null and @zBarcode is not null BEGIN set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[EDTE] set ' + CAST(@ColumnsToFix AS nVarchar(50))+ ' = ''' + CAST(@CorrectedValue AS nVarchar(15)) + ''' WHERE zBarcode = ' + CAST(@zBarcode AS nVarchar(15))+ ' and ' + 'PERSON_NUMBER = ' + CAST(@Person_Number AS nVarchar(7)) print @CORRECTION_STATEMENT EXECUTE (@CORRECTION_STATEMENT) END/*End of Update Command for Education Table*/ END/*end of Fourteen and Over Commands*/ --execute sp_executesql @CORRECTION_STATEMENT FETCH NEXT FROM ERRORCursor into @ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue , @ColumnsToFix , @Person_Number,@CorrectedValue , @Description, @Command, @ED_Three , @ED_Four , @ED_Five , @ED_Six END/*End of begin*/ENDCLOSE ERRORCursorDEALLOCATE ERRORCursorGO
DECLARE @CORRECTION_STATEMENT nvarchar(max) select @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[EDTE] set ' + Columns_To_Fix + ' = ''' + Corrected_Value + ''' WHERE zBarcode = ' + zBarcode + ' and ' + 'PERSON_NUMBER = ' + Person_No + ';'from [janinetestdata].[dbo].[TEST66]where Command = 'UPDATE' and Person_No is not null and Columns_To_Fix is not null and zBarcode is not null exec sp_executesql @CORRECTION_STATEMENT
print @CORRECTION_STATEMENT
USE [janinetestdata]GO/*Purpose of the sql query is to make corrections to all the errors detectedwhich in essences pertains to all of the tables concerning the LFS Survey .This query will run insertions, updates and deletion commands*/--Variables declaration sectionDECLARE @ErrorMessage varchar(500), @BatchNo varchar(15), @zBarcode varchar(15), @IncorrectValue varchar(100), @ColumnsToFix varchar(25), @Person_Number varchar(5), @CorrectedValue varchar(100), @Description varchar(500), @Command varchar(10), @ED_Three varchar(10), @ED_Four varchar(10), @ED_Five varchar(10), @ED_Six varchar(10), @CORRECTION_STATEMENT Nvarchar(900), @Cluster varchar(15), @HNUM varchar(15), @District varchar(25), @URBAN_RURAL varchar(100), @Final_Result_Code varchar(25), @HL_Three varchar(10), @HL_Four varchar(10), @HL_Five varchar(10), @HL_Six varchar(10), @HL_Seven varchar(10), @HL_SevenB varchar(10),@HHBarcode varchar (10), @Q1_5 varchar(10), @Q1_5_KNS varchar(10), @Q1_7 varchar(10), @Q1_8 varchar(10), @Q1_9 varchar(10), @Q1_9_2 varchar(10), @Q1_10a varchar(10), @Q1_10b varchar(10), @Q1_10c varchar(10), @Q1_10_Total varchar(10), @Q1_11 varchar(10), @Q1_12 varchar(10), @Q1_13 varchar(10), @Q1_14 varchar(10), @Q1_15 varchar(10), @Q1_15a varchar(10), @Q1_16 varchar(10), @Q1_17 varchar(10), @Q1_18 varchar(10), @Q1_19 varchar(10), @Q1_20 varchar(10), @Q1_21 varchar(10), @Q1_22 varchar(50), @Q1_23 varchar(50), @Q1_24 varchar(10), @Q1_25 varchar(10), @Q1_26 varchar(10), @Q1_27 varchar(10), @Q1_28_MAIN varchar(10), @Q1_28_OTHER varchar(10), @Q1_28_PREVIOUS varchar(10), @Q1_29_SEASONAL varchar(10), @Q1_29_YEARROUND varchar(10), @Q1_30_MAIN varchar(10), @Q1_30_OTHER varchar(10), @Q1_30_PREVIOUS varchar(10), @Q1_31_MAIN varchar(10), @Q1_31_OTHER varchar(10), @Q1_31_PREVIOUS varchar(10), @Q1_32_MAIN varchar(10), @Q1_32_OTHER varchar(10), @Q1_32_TOTAL varchar(10), @Q1_33_MAIN varchar(10), @Q1_33_OTHER varchar(10), @Q1_33_TOTAL varchar(10), @Q1_35 varchar(10), @Q1_37 varchar(10), @Q1_38 varchar(10), @Q1_39 varchar(10), @Q1_40 varchar(10), @Q1_41 varchar(10), @Q1_41_DKNS varchar(10), @Q1_41a varchar(10), @Q1_41b varchar(10), @Q1_41b_DKNS varchar(10), @Q1_42 varchar(10), @Q1_42_DKNS varchar(10), @Q1_43 varchar(10), @Q1_44 varchar(10) DECLARE ERRORCursor CURSOR FORSELECT ERRORMESSAGE, BATCH_NO, zBarcode, Incorrect_Value , Columns_To_Fix ,Person_No , Corrected_Value , Err_Description ,Command , ED_THREE , ED_FOUR,ED_FIVE, ED_SIX , Cluster, HHNUM , District , URBAN_RURAL, Final_Result_Code,HL_THREE, HL_FOUR, HL_FIVE, HL_SIX, HL_SEVEN , HL_SEVENB , HHBarcode, Q1_5,Q1_5_KNS , Q1_7, Q1_8, Q1_9, Q1_9_2, Q1_10a, Q1_10b, Q1_10c, Q1_10_Total, Q1_11,Q1_12, Q1_13 , Q1_14 , Q1_15, Q1_15a, Q1_16, Q1_17, Q1_18, Q1_19, Q1_20, Q1_21 , Q1_22, Q1_23 , Q1_24 , Q1_25, Q1_26, Q1_27, Q1_28_MAIN, Q1_28_OTHER,Q1_28_PREVIOUS , Q1_29_SEASONAL, Q1_29_YEARROUND, Q1_30_MAIN ,Q1_30_OTHER,Q1_30_PREVIOUS, Q1_31_MAIN, Q1_31_OTHER, Q1_31_PREVIOUS, Q1_32_MAIN , Q1_32_OTHER,Q1_32_TOTAL, Q1_33_MAIN, Q1_33_OTHER, Q1_33_TOTAL, Q1_35, Q1_37 , Q1_38 , Q1_39,Q1_40, Q1_41, Q1_41_DKNS, Q1_41a, Q1_41b, Q1_41b_DKNS, Q1_42, Q1_42_DKNS, Q1_43 ,Q1_44 FROM [janinetestdata].[dbo].[TEST]OPEN ERRORCursorBEGIN--Fetch first householdFETCH NEXT FROM ERRORCursor into @ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue,@ColumnsToFix ,@Person_Number, @CorrectedValue , @Description, @Command, @ED_Three , @ED_Four , @ED_Five , @ED_Six ,@Cluster , @HNUM ,@District , @URBAN_RURAL , @Final_Result_Code ,@HL_Three, @HL_Four, @HL_Five , @HL_Six ,@HL_Seven , @HL_SevenB ,@HHBarcode , @Q1_5 , @Q1_5_KNS ,@Q1_7, @Q1_8 , @Q1_9 , @Q1_9_2 , @Q1_10a,@Q1_10b , @Q1_10c , @Q1_10_Total, @Q1_11 , @Q1_12 ,@Q1_13 , @Q1_14 , @Q1_15 , @Q1_15a,@Q1_16 , @Q1_17 , @Q1_18, @Q1_19,@Q1_20 , @Q1_21 , @Q1_22, @Q1_23,@Q1_24 , @Q1_25 , @Q1_26 , @Q1_27,@Q1_28_MAIN , @Q1_28_OTHER , @Q1_28_PREVIOUS,@Q1_29_SEASONAL , @Q1_29_YEARROUND , @Q1_30_MAIN,@Q1_30_OTHER , @Q1_30_PREVIOUS , @Q1_31_MAIN,@Q1_31_OTHER , @Q1_31_PREVIOUS, @Q1_32_MAIN ,@Q1_32_OTHER , @Q1_32_TOTAL , @Q1_33_MAIN,@Q1_33_OTHER , @Q1_33_TOTAL , @Q1_35 , @Q1_37,@Q1_38 , @Q1_39 , @Q1_40, @Q1_41 , @Q1_41_DKNS ,@Q1_41a, @Q1_41b , @Q1_41b_DKNS , @Q1_42,@Q1_42_DKNS , @Q1_43 , @Q1_44 WHILE @@FETCH_STATUS = 0 BEGIN /*Beginning of Education Commands*/ if @ErrorMessage LIKE 'ED%' BEGIN/*Beginning of Update Command for Education Table*/ if @Command = 'UPDATE' and @Person_Number is not null and @ColumnsToFix is not null and @zBarcode is not null BEGIN set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[EDUCATION] set ' + coalesce(CAST(@ColumnsToFix AS nVarchar(50)),'99999')+ ' = ' + coalesce(CAST(@CorrectedValue AS nVarchar(15)),'99999')+ ' WHERE zBarcode = ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999')+ ' and ' + 'PERSON_NUMBER = ' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') print @CORRECTION_STATEMENT EXECUTE (@CORRECTION_STATEMENT) END/*End of Update Command for Education Table*/ /*Beginning of Insert Command for Education Table*/ else if @Command = 'INSERT' and @Person_Number is not null and @zBarcode is not null BEGIN set @CORRECTION_STATEMENT = 'INSERT INTO [janinetestdata].[dbo].[EDUCATION] VALUES('+ coalesce(CAST(@ED_THREE AS nVarchar(10)),'99999') + ',' + coalesce(CAST(@ED_Four AS nVarchar(10)),'99999') + ',' + coalesce(CAST(@ED_Five AS nVarchar(10)),'99999') + ',' + coalesce(CAST(@ED_Six AS nVarchar(10)),'99999')+ ',' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ',' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999')+ ')' print @CORRECTION_STATEMENT EXECUTE (@CORRECTION_STATEMENT) END/*End of INSERT Command for Education Table*/ /*Beginning of Delete Command for Education Table*/ else BEGIN if @Command = 'DELETE' BEGIN if @Person_Number is not null and @zBarcode is not null BEGIN set @CORRECTION_STATEMENT = 'DELETE FROM [janinetestdata].[dbo].[EDUCATION] WHERE zBarcode = '+ coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and PERSON_NUMBER = ' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') print @CORRECTION_STATEMENT EXECUTE (@CORRECTION_STATEMENT) END END END/*End of Delete Command for Education Table*/ END /*End of Education Commands*/ /*Beginning of HH_ALL Commands*/ else if @ErrorMessage LIKE 'HH%' BEGIN/*Beginning of Update Command for HH_ALL Table*/ if @Command = 'UPDATE'and @Person_Number is not null and @ColumnsToFix is not null and @zBarcode is not null BEGIN set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[HH_ALL] set ' + coalesce(CAST(@ColumnsToFix AS nVarchar(50)),'99999')+ ' = ' + coalesce(CAST(@CorrectedValue AS nVarchar(50)),'99999')+ ' WHERE zBarcode = ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and ' + 'Person_No = ' +coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') print @CORRECTION_STATEMENT EXECUTE (@CORRECTION_STATEMENT) END/*End of Update Command for HH_ALL Table*/ /*Beginning of Insert Command for HH_ALL Table*/ else if @Command = 'INSERT'and @Person_Number is not null and @zBarcode is not null BEGIN set @CORRECTION_STATEMENT = 'INSERT INTO [janinetestdata].[dbo].[HH_ALL] VALUES('+ coalesce(CAST(@BatchNo AS nVarchar(8)),'99999') + ' , ' + coalesce(CAST(@Cluster AS nVarchar (10)),'99999') + ' , ' + coalesce(CAST(@HNUM AS nVarchar(10)),'99999')+ ' , ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' , ' + coalesce(CAST(@District AS nVarchar(10)),'99999')+ ' , ' + coalesce(CAST(@URBAN_RURAL AS nVarchar(10)),'99999') + ' , ' + coalesce(CAST(@Final_Result_Code AS nVarchar(10)),'99999') + ' , ' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ' )' print @CORRECTION_STATEMENT EXECUTE (@CORRECTION_STATEMENT) END /*End of INSERT Command for HH_ALL Table*/ /*Beginning of Delete Command for HH_ALL Table*/ else BEGIN if @Command = 'DELETE' BEGIN if @Person_Number is not null and @zBarcode is not null BEGIN set @CORRECTION_STATEMENT = 'DELETE FROM [janinetestdata].[dbo].[HH_ALL] WHERE zBarcode = ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and Person_No = ' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') print @CORRECTION_STATEMENT EXECUTE (@CORRECTION_STATEMENT) END /*End of Delete Command for HH_ALL Table*/ END END /*End Else*/ END/*end of HH_ALL Commands*/ /*Beginning of Household Listing Commands*/ else if @ErrorMessage LIKE 'HL%' BEGIN/*Beginning of Update Command for Household Listing Table*/ if @Command = 'UPDATE'and @Person_Number is not null and @ColumnsToFix is not null and @zBarcode is not null BEGIN set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[HH_LISTING] set ' + coalesce(CAST(@ColumnsToFix AS nVarchar(50)),'99999')+ ' = ' + coalesce(CAST(@CorrectedValue AS nVarchar(50)),'99999')+ ' WHERE zBarcode = ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and ' + 'PERSON_NUMBER = ''' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + '''' print @CORRECTION_STATEMENT EXECUTE (@CORRECTION_STATEMENT) END/*End of Update Command for Household Listing Table*/ /*Beginning of Insert Command for Household Listing Table*/ else if @Command = 'INSERT'and @Person_Number is not null BEGIN set @CORRECTION_STATEMENT = 'INSERT INTO [janinetestdata].[dbo].[HH_LISTING] VALUES('''+ coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ''' , ' + coalesce(CAST(@HL_Three AS nVarchar (10)),'99999') + ' , ' + coalesce(CAST(@HL_Four AS nVarchar (10)),'99999') + ' , ' + coalesce(CAST(@HL_Five AS nVarchar(10)),'99999') + ' , '+ coalesce(CAST(@HL_Six AS nVarchar(10)),'99999')+ ' , ' + coalesce(CAST(@HL_Seven AS nVarchar (10)),'99999') + ' , ' + coalesce(CAST(@HL_SevenB AS nVarchar (10)),'99999') + ' , ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' )' print @CORRECTION_STATEMENT EXECUTE (@CORRECTION_STATEMENT) END/*End of INSERT Command for Household Listing Table*/ /*Beginning of Delete Command for Household Listing Table*/ else BEGIN if @Command = 'DELETE' BEGIN if @Person_Number is not null and @zBarcode is not null BEGIN set @CORRECTION_STATEMENT = 'DELETE FROM [janinetestdata].[dbo].[HH_LISTING] WHERE zBarcode = '+ coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and PERSON_NUMBER = ''' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + '''' print @CORRECTION_STATEMENT EXECUTE (@CORRECTION_STATEMENT) END/*End of Delete Command for Household Listing Table*/ END END/*end else*/ END /*end of Household Listing Commands*/ /*Beginning of Fourteen and Over Commands*/ else if @ErrorMessage LIKE 'O14%' BEGIN/*Beginning of Update Command for Fourteen and Over Table*/ if @Command = 'UPDATE'and @Person_Number is not null and @ColumnsToFix is not null and @zBarcode is not null BEGIN set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[FOURTEEN_ALL] set ' + coalesce(CAST(@ColumnsToFix AS nVarchar(50)),'99999')+ ' = ' + coalesce(CAST(@CorrectedValue AS nVarchar(50)),'99999')+ ' WHERE zBarcode = ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and ' + 'Person_No = ''' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + '''' print @CORRECTION_STATEMENT EXECUTE (@CORRECTION_STATEMENT) END/*End of Update Command for Fourteen and Over Table*/ /*Beginning of Insert Command for Fourteen and Over Table*/ else if @Command = 'INSERT'and @Person_Number is not null and @Q1_8 is not null and @Q1_23 is not null and @Q1_22 is not null and @Q1_30_MAIN is not null and @Q1_30_OTHER is not null and @Q1_30_PREVIOUS is not null and @Q1_31_MAIN is not null and @Q1_31_OTHER is not null and @Q1_31_PREVIOUS is not null and @Q1_39 is not null and @Q1_40 is not null and @zBarcode is not null BEGIN set @CORRECTION_STATEMENT = 'INSERT INTO [janinetestdata].[dbo].[FOURTEEN_ALL] VALUES('+ coalesce(CAST(@BatchNo AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' , ' + coalesce(CAST(@HHBarcode AS nVarchar(7)),'99999') + ' , ''' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ''' , ' + coalesce(CAST(@District AS nVarchar(7)),'99999') + ' , '+ coalesce(CAST(@URBAN_RURAL AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Cluster AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@HNUM AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Final_Result_Code AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_5 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_5_KNS AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_7 AS nVarchar(7)),'99999') + ' , '''+ coalesce(CAST(@Q1_8 AS nVarchar(7)),'99999') + ''' , '+ coalesce(CAST(@Q1_9 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_9_2 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_10a AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_10b AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_10c AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_10_Total AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_11 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_12 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_13 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_14 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_15 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_15a AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_16 AS nVarchar(7)),'99999') + ' , ''' + coalesce(CAST(@Q1_17 AS nVarchar(7)),'99999') + ''' , ' + coalesce(CAST(@Q1_18 AS nVarchar(7)),'99999') + ' , '+ coalesce(CAST(@Q1_19 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_20 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_21 AS nVarchar(7)),'99999') + ' , ''' + coalesce(CAST(@Q1_22 AS nVarchar(7)),'99999')+ ''' , ''' + coalesce(CAST(@Q1_23 AS nVarchar(7)),'99999') + ''' , ' + coalesce(CAST(@Q1_24 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_25 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_26 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_27 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_28_MAIN AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_28_OTHER AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_28_PREVIOUS AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_29_SEASONAL AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_29_YEARROUND AS nVarchar(7)),'99999') + ' , '''+ coalesce(CAST(@Q1_30_MAIN AS nVarchar(7)),'99999') +''' , ''' + coalesce(CAST(@Q1_30_OTHER AS nVarchar(7)),'99999') + ''' , ''' + coalesce(CAST(@Q1_30_PREVIOUS AS nVarchar(7)),'99999')+ ''' , ''' + coalesce(CAST(@Q1_31_MAIN AS nVarchar(7)),'99999') + ''' , ''' + coalesce(CAST(@Q1_31_OTHER AS nVarchar(7)),'99999') + ''' , '''+ coalesce(CAST(@Q1_31_PREVIOUS AS nVarchar(7)),'99999')+ ''' , ' + coalesce(CAST(@Q1_32_MAIN AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_32_OTHER AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_32_TOTAL AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_33_MAIN AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_33_OTHER AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_33_TOTAL AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_35 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_37 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_38 AS nVarchar(7)),'99999') + ' , ''' + coalesce(CAST(@Q1_39 AS nVarchar(7)),'99999') + ''' , ''' + coalesce(CAST(@Q1_40 AS nVarchar(7)),'99999') + ''' , ' + coalesce(CAST(@Q1_41 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_41_DKNS AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_41a AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_41b AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_41b_DKNS AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_42 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_42_DKNS AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_43 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_44 AS nVarchar(7)),'99999') + ')' print @CORRECTION_STATEMENT EXECUTE (@CORRECTION_STATEMENT) END/*End of INSERT Command for Fourteen and Over Table*/ /*Beginning of Delete Command for Fourteen and Over Table*/ else BEGIN if @Command = 'DELETE' BEGIN if @Person_Number is not null and @zBarcode is not null BEGIN set @CORRECTION_STATEMENT = 'DELETE FROM [janinetestdata].[dbo].[FOURTEEN_ALL] WHERE zBarcode = '+ coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and Person_No = ''' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + '''' print @CORRECTION_STATEMENT EXECUTE (@CORRECTION_STATEMENT) END/*End of Delete Command for Fourteen and Over Table*/ END/*end else*/ END END/*end of Fourteen and Over Commands*/ FETCH NEXT FROM ERRORCursor into @ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue , @ColumnsToFix , @Person_Number,@CorrectedValue , @Description, @Command, @ED_Three , @ED_Four , @ED_Five , @ED_Six , @Cluster , @HNUM , @District , @URBAN_RURAL , @Final_Result_Code , @HL_Three, @HL_Four, @HL_Five , @HL_Six , @HL_Seven , @HL_SevenB ,@HHBarcode , @Q1_5 , @Q1_5_KNS , @Q1_7, @Q1_8 , @Q1_9 , @Q1_9_2 , @Q1_10a, @Q1_10b , @Q1_10c , @Q1_10_Total, @Q1_11 , @Q1_12 , @Q1_13 , @Q1_14 , @Q1_15 , @Q1_15a, @Q1_16 , @Q1_17 , @Q1_18, @Q1_19, @Q1_20 , @Q1_21 , @Q1_22, @Q1_23, @Q1_24 , @Q1_25 , @Q1_26 , @Q1_27, @Q1_28_MAIN , @Q1_28_OTHER , @Q1_28_PREVIOUS, @Q1_29_SEASONAL , @Q1_29_YEARROUND , @Q1_30_MAIN, @Q1_30_OTHER , @Q1_30_PREVIOUS , @Q1_31_MAIN, @Q1_31_OTHER , @Q1_31_PREVIOUS, @Q1_32_MAIN , @Q1_32_OTHER , @Q1_32_TOTAL , @Q1_33_MAIN, @Q1_33_OTHER , @Q1_33_TOTAL , @Q1_35 , @Q1_37, @Q1_38 , @Q1_39 , @Q1_40, @Q1_41 , @Q1_41_DKNS , @Q1_41a, @Q1_41b , @Q1_41b_DKNS , @Q1_42, @Q1_42_DKNS , @Q1_43 , @Q1_44 END/*End of begin*/ENDCLOSE ERRORCursorDEALLOCATE ERRORCursorGO
UPDATE [janinetestdata].[dbo].[EDUCATION] set ED_THREE = 1 WHERE zBarcode = 15394 and PERSON_NUMBER = 3(1 row(s) affected)UPDATE [janinetestdata].[dbo].[EDUCATION] set ED_THREE = 2 WHERE zBarcode = 567 and PERSON_NUMBER = 1(1 row(s) affected)UPDATE [janinetestdata].[dbo].[EDUCATION] set ED_THREE = 3 WHERE zBarcode = 567 and PERSON_NUMBER = 2(0 row(s) affected)INSERT INTO [janinetestdata].[dbo].[EDUCATION] VALUES(2,3,4,6,2,4398)(1 row(s) affected)INSERT INTO [janinetestdata].[dbo].[EDUCATION] VALUES(2,3,7,5,3,4237)(1 row(s) affected)DELETE FROM [janinetestdata].[dbo].[EDUCATION] WHERE zBarcode = 20725 and PERSON_NUMBER = 4(0 row(s) affected)DELETE FROM [janinetestdata].[dbo].[EDUCATION] WHERE zBarcode = 20725 and PERSON_NUMBER = 5(0 row(s) affected)DELETE FROM [janinetestdata].[dbo].[EDUCATION] WHERE zBarcode = 20725 and PERSON_NUMBER = 6(0 row(s) affected)UPDATE [janinetestdata].[dbo].[HH_ALL] set Final_Result_Code = 0 WHERE zBarcode = 15110 and Person_No = 1(1 row(s) affected)UPDATE [janinetestdata].[dbo].[HH_ALL] set Final_Result_Code = 0 WHERE zBarcode = 15141 and Person_No = 2(1 row(s) affected)DELETE FROM [janinetestdata].[dbo].[HH_ALL] WHERE zBarcode = 20657 and Person_No = 1(0 row(s) affected)DELETE FROM [janinetestdata].[dbo].[HH_ALL] WHERE zBarcode = 21265 and Person_No = 1(0 row(s) affected)INSERT INTO [janinetestdata].[dbo].[HH_ALL] VALUES(36144 , 2 , 3 , 21227 , 5 , 6 , 8 , 1 )(1 row(s) affected)INSERT INTO [janinetestdata].[dbo].[HH_ALL] VALUES(36157 , 2 , 3 , 5678 , 5 , 6 , 8 , 1 )(1 row(s) affected)UPDATE [janinetestdata].[dbo].[HH_LISTING] set HL_FOUR = 3 WHERE zBarcode = 21555 and PERSON_NUMBER = '3'(3 row(s) affected)UPDATE [janinetestdata].[dbo].[HH_LISTING] set HL_FOUR = 3 WHERE zBarcode = 21555 and PERSON_NUMBER = '3'(3 row(s) affected)INSERT INTO [janinetestdata].[dbo].[HH_LISTING] VALUES('1' , 3 , 4 , 6 , 8 , 6 , 8 , 15011 )(1 row(s) affected)INSERT INTO [janinetestdata].[dbo].[HH_LISTING] VALUES('1' , 3 , 4 , 6 , 8 , 6 , 8 , 15011 )(1 row(s) affected)DELETE FROM [janinetestdata].[dbo].[HH_LISTING] WHERE zBarcode = 21098 and PERSON_NUMBER = '3'(0 row(s) affected)DELETE FROM [janinetestdata].[dbo].[HH_LISTING] WHERE zBarcode = 24723 and PERSON_NUMBER = '1'(0 row(s) affected)UPDATE [janinetestdata].[dbo].[FOURTEEN_ALL] set Q1_42_DKNS = 63 WHERE zBarcode = 51415 and Person_No = '2'(1 row(s) affected)UPDATE [janinetestdata].[dbo].[FOURTEEN_ALL] set Q1_42_DKNS = 67 WHERE zBarcode = 50913 and Person_No = '2'(1 row(s) affected)INSERT INTO [janinetestdata].[dbo].[FOURTEEN_ALL] VALUES(36122 , 21593 , 3 , '1' , 4 , 4 , 7 , 9 , 3 , 4 , 5 , 5 , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , '5' , '5' , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5)http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif(1 row(s) affected)INSERT INTO [janinetestdata].[dbo].[FOURTEEN_ALL] VALUES(36124 , 22019 , 2 , '1' , 2 , 4 , 7 , 9 , 4 , 6 , 4 , 6 , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , '5' , '5' , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5)(1 row(s) affected)DELETE FROM [janinetestdata].[dbo].[FOURTEEN_ALL] WHERE zBarcode = 3667 and Person_No = '1'(0 row(s) affected)DELETE FROM [janinetestdata].[dbo].[FOURTEEN_ALL] WHERE zBarcode = 3674 and Person_No = '1'(0 row(s) affected)
/*Purpose of the sql query is to make corrections to all the errors detectedwhich in essences pertains to all of the tables concerning the LFS Survey .This query will run insertions, updates and deletion commands*/--Variables declaration sectionDECLARE @CORRECTION_STATEMENT nvarchar(max) /*Beginning of Update Command for Education Table*/select @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[EDUCATION] set ' + coalesce(CAST(Columns_To_Fix AS Varchar(50)),'99999')+ ' = ' + coalesce(CAST(Corrected_Value AS Varchar(15)),'99999')+ ' WHERE zBarcode = ' + coalesce(CAST(zBarcode AS Varchar(15)),'99999')+ ' and ' + 'PERSON_NUMBER = ' + coalesce(CAST(Person_No AS Varchar(7)),'99999')from [janinetestdata].[dbo].[TEST] where Command = 'UPDATE' and Person_No is not null and Columns_To_Fix is not null and zBarcode is not null and ERRORMESSAGE LIKE 'ED%' print @CORRECTION_STATEMENTEXECUTE (@CORRECTION_STATEMENT)
UPDATE [janinetestdata].[dbo].[EDUCATION] set ED_THREE = 3 WHERE zBarcode = 567 and PERSON_NUMBER = 2(0 row(s) affected)INSERT INTO [janinetestdata].[dbo].[EDUCATION] VALUES(2,3,7,5,3,4237)(1 row(s) affected)DELETE FROM [janinetestdata].[dbo].[EDUCATION] WHERE zBarcode = 20725 and PERSON_NUMBER = 6(0 row(s) affected)UPDATE [janinetestdata].[dbo].[HH_ALL] set Final_Result_Code = 0 WHERE zBarcode = 15141 and Person_No = 2(1 row(s) affected)INSERT INTO [janinetestdata].[dbo].[HH_ALL] VALUES(36157 , 2 , 3 , 5678 , 5 , 6 , 8 , 1 )(1 row(s) affected)DELETE FROM [janinetestdata].[dbo].[HH_ALL] WHERE zBarcode = 21265 and Person_No = 1(0 row(s) affected)UPDATE [janinetestdata].[dbo].[HH_LISTING] set HL_FOUR = 3 WHERE zBarcode = 21555 and PERSON_NUMBER = 3(3 row(s) affected)INSERT INTO [janinetestdata].[dbo].[HH_LISTING] VALUES('1' , 3 , 4 , 6 , 8 , 6 , 8 , 15011 )(1 row(s) affected)DELETE FROM [janinetestdata].[dbo].[HH_LISTING] WHERE zBarcode = 24723 and PERSON_NUMBER = '1'(0 row(s) affected)UPDATE [janinetestdata].[dbo].[FOURTEEN_ALL] set Q1_42_DKNS = 67 WHERE zBarcode = 50913 and Person_No = '2'(1 row(s) affected)INSERT INTO [janinetestdata].[dbo].[FOURTEEN_ALL] VALUES(36124 , 22019 , 2 , '1' , 2 , 4 , 7 , 9 , 4 , 6 , 4 , 6 , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , '5' , '5' , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5)(1 row(s) affected)DELETE FROM [janinetestdata].[dbo].[FOURTEEN_ALL] WHERE zBarcode = 3674 and Person_No = '1'(0 row(s) affected)
