USE QESTLab

DECLARE @counter INT
DECLARE @TESTDATECOUNTER INT
DECLARE @AGEDAYSCOUNTER INT
DECLARE @DiffTDCount INT
DECLARE @DiffADCount INT
DECLARE @maxCount INT
DECLARE @ID INT
DECLARE @TestDate DATETIME
DECLARE @Until DATETIME
DECLARE @AgeDays SMALLINT
DECLARE @StandardCuringDays SMALLINT
DECLARE @parentID INT

DECLARE test_cursor CURSOR
FOR 
SELECT qestuniqueid, qestuniqueparentid, testdate, agedays, StandardCuringDays FROM QESTLab_Backup.dbo.DocumentConcreteDestructiveSpecimen 

SET @counter = 1
SET @TESTDATECOUNTER = 0
SET @AGEDAYSCOUNTER = 0
SET @DiffTDCount = 0
SET @DiffADCount = 0

---------------------------------------------------------
-- MODIFY THE FOLLOWING LINE TO SET THE TEST DATE
-- TO WHICH THE SCRIPT WILL UPDATE SAMPLES.
-- NOTE THAT ONLY SAMPLES WITH A TEST DATE LESS THAN
-- THAT SPECIFIED BELOW WILL BE MODIFIED.
-- FORMAT: CONVERT(DATETIME, 'YYYY-MM-DD 00:00:00', 102)
---------------------------------------------------------

SET @Until = CONVERT(DATETIME, '2006-03-16 00:00:00', 102)

---------------------------------------------------------
-- DO NOT MODIFY ANYTHING BELOW THIS LINE
---------------------------------------------------------

--Count different testdates
SET @DiffTDCount = 	(SELECT COUNT(*) FROM 
			QESTLab.dbo.DocumentConcreteDestructiveSpecimen,
			QESTLab_Backup.dbo.DocumentConcreteDestructiveSpecimen
			WHERE 
			QESTLab.dbo.DocumentConcreteDestructiveSpecimen.QESTUniqueID = 
			QESTLab_Backup.dbo.DocumentConcreteDestructiveSpecimen.QESTUniqueID
			AND
			QESTLab.dbo.DocumentConcreteDestructiveSpecimen.TestDate <>
			QESTLab_Backup.dbo.DocumentConcreteDestructiveSpecimen.TestDate)

SET @DiffADCount = 	(SELECT COUNT(*) FROM 
			QESTLab.dbo.DocumentConcreteDestructiveSpecimen,
			QESTLab_Backup.dbo.DocumentConcreteDestructiveSpecimen
			WHERE 
			QESTLab.dbo.DocumentConcreteDestructiveSpecimen.QESTUniqueID = 
			QESTLab_Backup.dbo.DocumentConcreteDestructiveSpecimen.QESTUniqueID
			AND
			QESTLab.dbo.DocumentConcreteDestructiveSpecimen.AgeDays <>
			QESTLab_Backup.dbo.DocumentConcreteDestructiveSpecimen.AgeDays)

SET @maxCount = 	(select count(*) from QESTLab_Backup.dbo.DocumentConcreteDestructiveSpecimen)

print 'Records with differing TestDate: '
print @DiffTDCount
print 'Records with differing AgeDays: '
print @DiffADCount

IF (@DiffTDCount > 0 OR @DiffADCount > 0)
BEGIN
	OPEN test_cursor
	WHILE @counter <= @maxCount
	BEGIN
	FETCH NEXT FROM test_cursor INTO @ID, @parentID, @TestDate, @AgeDays, @StandardCuringDays

	--make sure test date is within range
	IF (@TestDate < @Until)
		BEGIN
		IF (@TestDate <> (SELECT QESTLab.dbo.DocumentConcreteDestructiveSpecimen.TestDate 
				FROM QESTLab.dbo.DocumentConcreteDestructiveSpecimen
				WHERE QESTLab.dbo.DocumentConcreteDestructiveSpecimen.QESTUniqueID = @ID))
			BEGIN
				--Show incorrect specimen
				SELECT QESTLab.dbo.DocumentConcreteDestructive.QESTOwnerLabNo,
				QESTLab.dbo.DocumentConcreteDestructive.SampleID,
				QESTLab.dbo.DocumentConcreteDestructiveSpecimen.FieldSheetAndID,
				QESTLab.dbo.DocumentConcreteDestructiveSpecimen.TestDate,
				@TestDate AS CorrectTestDate,
				QESTLab.dbo.DocumentConcreteDestructiveSpecimen.StandardCuringDays,
				@StandardCuringDays AS CorrectStandardCuringDays
				FROM QESTLab.dbo.DocumentConcreteDestructiveSpecimen 
				LEFT JOIN QESTLab.dbo.DocumentConcreteDestructive
				ON QESTLab.dbo.DocumentConcreteDestructiveSpecimen.QESTUniqueParentID = QESTLab.dbo.DocumentConcreteDestructive.QESTUniqueID
				WHERE  QESTLab.dbo.DocumentConcreteDestructiveSpecimen.QESTUniqueID = @ID
				
				--Update incorrect specimen with correct values
				UPDATE QESTLab.dbo.DocumentConcreteDestructiveSpecimen 
				SET QESTLab.dbo.DocumentConcreteDestructiveSpecimen.TestDate = @TestDate,
				QESTLab.dbo.DocumentConcreteDestructiveSpecimen.StandardCuringDays = @StandardCuringDays
				WHERE QESTLab.dbo.DocumentConcreteDestructiveSpecimen.QESTUniqueID = @ID

				SET @TESTDATECOUNTER = @TESTDATECOUNTER + 1
			END
		
		
		IF (@AgeDays <> (SELECT QESTLab.dbo.DocumentConcreteDestructiveSpecimen.AgeDays 
				FROM QESTLab.dbo.DocumentConcreteDestructiveSpecimen
				WHERE QESTLab.dbo.DocumentConcreteDestructiveSpecimen.QESTUniqueID = @ID))
			BEGIN
				--Show incorrect specimen
				SELECT QESTLab.dbo.DocumentConcreteDestructive.QESTOwnerLabNo,
				QESTLab.dbo.DocumentConcreteDestructive.SampleID,
				QESTLab.dbo.DocumentConcreteDestructiveSpecimen.FieldSheetAndID,
				QESTLab.dbo.DocumentConcreteDestructiveSpecimen.AgeDays,
				@AgeDays AS CorrectAgeDays,
				QESTLab.dbo.DocumentConcreteDestructiveSpecimen.StandardCuringDays,
				@StandardCuringDays AS CorrectStandardCuringDays
				FROM QESTLab.dbo.DocumentConcreteDestructiveSpecimen 
				LEFT JOIN QESTLab.dbo.DocumentConcreteDestructive
				ON QESTLab.dbo.DocumentConcreteDestructiveSpecimen.QESTUniqueParentID = QESTLab.dbo.DocumentConcreteDestructive.QESTUniqueID
				WHERE  QESTLab.dbo.DocumentConcreteDestructiveSpecimen.QESTUniqueID = @ID

				UPDATE QESTLab.dbo.DocumentConcreteDestructiveSpecimen 
				SET QESTLab.dbo.DocumentConcreteDestructiveSpecimen.AgeDays = @AgeDays,
				QESTLab.dbo.DocumentConcreteDestructiveSpecimen.StandardCuringDays = @StandardCuringDays
				WHERE QESTLab.dbo.DocumentConcreteDestructiveSpecimen.QESTUniqueID = @ID

				SET @AGEDAYSCOUNTER = @AGEDAYSCOUNTER + 1
			END
		END
		SET @counter = @counter + 1		
	END
	print 'Total Records Searched: ' 
	print @counter	
	print 'Records With updated TestDate: ' 
	print @TESTDATECOUNTER
	print 'Records With updated AgeDays: ' 
	print @AGEDAYSCOUNTER
END
ELSE
	print 'No records to update'

DEALLOCATE test_cursor