USE [Clancy_Exp_Database]
GO
/****** Object:  StoredProcedure [dbo].[usp_CalculateInterceptByHole]    Script Date: 03/09/2010 07:31:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






-- =============================================
-- Author:		Peter Wilson
-- Create date: 12/10/2007
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[usp_CalculateInterceptByHole] 
	-- Add the parameters for the stored procedure here
	@Owner varchar(50),
	@PackageID int,
	@Dataset varchar(30), 
	@Hole_ID varchar(30),
	@MinCutoff real,
	@MaxCutoff real,
	@MinIntercept real,
	@MaxInternalWaste real,
	@Element1 varchar(20) = '',
	@Element2 varchar(20) = '',
	@Element3 varchar(20) = '',
	@Element4 varchar(20) = '',
	@Element5 varchar(20) = '',
	@Element6 varchar(20) = '',
	@Element7 varchar(20) = ''
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Local variables
		DECLARE @SQL varchar(2000)
		DECLARE @SQL_A varchar(1000)
		DECLARE @SQL_B varchar(1000)
		DECLARE @mFrom real
		DECLARE @mTo real
		DECLARE @Value1 real
		DECLARE @Value2 real
		DECLARE @Value3 real
		DECLARE @Value4 real
		DECLARE @Value5 real
		DECLARE @Value6 real
		DECLARE @Value7 real
		DECLARE @ElementCount int
		DECLARE @PrevTo real
		DECLARE @IntStart real
		DECLARE @IntEnd real
		DECLARE @IntValue1 real
		DECLARE @IntValue2 real
		DECLARE @IntValue3 real
		DECLARE @IntValue4 real
		DECLARE @IntValue5 real
		DECLARE @IntValue6 real
		DECLARE @IntValue7 real
		DECLARE @WasteValue1 real
		DECLARE @WasteValue2 real
		DECLARE @WasteValue3 real
		DECLARE @WasteValue4 real
		DECLARE @WasteValue5 real
		DECLARE @WasteValue6 real
		DECLARE @WasteValue7 real
        DECLARE @Grade1 real
		DECLARE @Grade2 real
		DECLARE @Grade3 real
		DECLARE @Grade4 real
		DECLARE @Grade5 real
		DECLARE @Grade6 real
		DECLARE @Grade7 real

		SET @SQL = 'DECLARE DHAssays CURSOR FOR SELECT mFrom, mTo, ' + @Element1;
		SET @ElementCount = 1;

		IF @Element2 <> ''
		BEGIN
			SET @SQL = @SQL + ',' + @Element2;
			SET @ElementCount = 2;
        END
        IF @Element3 <> ''
		BEGIN
			SET @SQL = @SQL + ',' + @Element3;
			SET @ElementCount = 3;
        END
        IF @Element4 <> ''
		BEGIN
			SET @SQL = @SQL + ',' + @Element4;
			SET @ElementCount = 4;
        END
        IF @Element5 <> ''
		BEGIN
			SET @SQL = @SQL + ',' + @Element5;
			SET @ElementCount = 5;
        END
        IF @Element6 <> ''
		BEGIN
			SET @SQL = @SQL + ',' + @Element6;
			SET @ElementCount = 6;
        END
        IF @Element7 <> ''
		BEGIN
			SET @SQL = @SQL + ',' + @Element7;
			SET @ElementCount = 7;
        END
        SET @SQL = @SQL + ' FROM tblVWDHAssays WHERE Dataset = ''' + @Dataset + '''AND Hole_ID = ''' + @Hole_ID + ''' AND Superseded = 0 Order by Hole_ID, mFrom';
		
		EXEC(@SQL);
		OPEN DHAssays;

		/*Read record*/
		IF @ElementCount = 1
		BEGIN
			FETCH NEXT FROM DHAssays into @mFrom, @mTo, @Value1;
			SET @Value1 = isnull(@Value1,0)
		END
		IF @ElementCount = 2
		BEGIN
			FETCH NEXT FROM DHAssays into @mFrom, @mTo, @Value1, @Value2;
			SET @Value1 = isnull(@Value1,0)
			SET @Value2 = isnull(@Value2,0)

		END
		IF @ElementCount = 3
		BEGIN
			FETCH NEXT FROM DHAssays into @mFrom, @mTo, @Value1, @Value2, @Value3;
			SET @Value1 = isnull(@Value1,0)
			SET @Value2 = isnull(@Value2,0)
			SET @Value3 = isnull(@Value3,0)
		END
		IF @ElementCount = 4
		BEGIN
			FETCH NEXT FROM DHAssays into @mFrom, @mTo, @Value1, @Value2, @Value3, @Value4;
			SET @Value1 = isnull(@Value1,0)
			SET @Value2 = isnull(@Value2,0)
			SET @Value3 = isnull(@Value3,0)
			SET @Value4 = isnull(@Value4,0)
		END
		IF @ElementCount = 5
		BEGIN
			FETCH NEXT FROM DHAssays into @mFrom, @mTo, @Value1, @Value2, @Value3, @Value4, @Value5;
			SET @Value1 = isnull(@Value1,0)
			SET @Value2 = isnull(@Value2,0)
			SET @Value3 = isnull(@Value3,0)
			SET @Value4 = isnull(@Value4,0)
			SET @Value5 = isnull(@Value5,0)
		END
		IF @ElementCount = 6
		BEGIN
			FETCH NEXT FROM DHAssays into @mFrom, @mTo, @Value1, @Value2, @Value3, @Value4, @Value5, @Value6;
			SET @Value1 = isnull(@Value1,0)
			SET @Value2 = isnull(@Value2,0)
			SET @Value3 = isnull(@Value3,0)
			SET @Value4 = isnull(@Value4,0)
			SET @Value5 = isnull(@Value5,0)
			SET @Value6 = isnull(@Value6,0)
		END
		IF @ElementCount = 7
		BEGIN
			FETCH NEXT FROM DHAssays into @mFrom, @mTo, @Value1, @Value2, @Value3, @Value4, @Value5, @Value6, @Value7;
			SET @Value1 = isnull(@Value1,0)
			SET @Value2 = isnull(@Value2,0)
			SET @Value3 = isnull(@Value3,0)
			SET @Value4 = isnull(@Value4,0)
			SET @Value5 = isnull(@Value5,0)
			SET @Value6 = isnull(@Value6,0)
			SET @Value7 = isnull(@Value7,0)
		END

		SET @PrevTo = -1;
		SET @IntStart = -1;
		SET @IntEnd = -1;


		
/*Start of loop*/

WHILE @@FETCH_STATUS = 0
BEGIN

		/*Is there a gap in the results sequence?*/
		IF @mFrom <> @PrevTo
		BEGIN
			/*Write to intercept table if current intercept exists and is valid*/
			IF (@IntStart <> -1) AND (@IntEnd - @IntStart >= @MinIntercept)
			BEGIN
				/*Write Intercept*/
				SET @Grade1 = ROUND((@IntValue1/(@IntEnd - @IntStart)),2);
				SET @Grade2 = ROUND((@IntValue2/(@IntEnd - @IntStart)),2);
				SET @Grade3 = ROUND((@IntValue3/(@IntEnd - @IntStart)),2);
				SET @Grade4 = ROUND((@IntValue4/(@IntEnd - @IntStart)),2);
				SET @Grade5 = ROUND((@IntValue5/(@IntEnd - @IntStart)),2);
				SET @Grade6 = ROUND((@IntValue6/(@IntEnd - @IntStart)),2);
				SET @Grade7 = ROUND((@IntValue7/(@IntEnd - @IntStart)),2);
				SET @SQL_A = 'INSERT INTO tblDHInterceptsCustom (PackageID, Owner, Dataset, Hole_ID, mFrom, mTo, IntervalWidth, Element, Grade,MinCutOff,MaxCutOff,MinIntercept,MaxInternalWaste';
				SET @SQL_B = '''' + cast(@PackageID as varchar) + ''',''' + @Owner + ''','''  + @Dataset + ''',''' + @Hole_ID + ''',' +  cast(@IntStart as varchar);
				SET @SQL_B = @SQL_B + ',' +  cast(@IntEnd as varchar) + ',' + cast(ROUND((@IntEnd - @IntStart),2) as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element1 + ''',' + cast(@Grade1 as varchar);
				SET @SQL_B = @SQL_B + ',' + cast(@MinCutOff as varchar) + ',' +  cast(@MaxCutOff as varchar) + ',' +  cast(@MinIntercept as varchar) + ',' +  cast(@MaxInternalWaste as varchar);
				
				IF @ElementCount = 2
				BEGIN
					SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
					SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
				END
				IF @ElementCount = 3
				BEGIN
					SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
					SET @SQL_A = @SQL_A + ', CoElement2Name, CoElement2Grade';
					SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
					SET @SQL_B = @SQL_B + ',''' + @Element3 + ''',' + Cast(@Grade3 as varchar);
				END
				IF @ElementCount = 4
				BEGIN
					SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
					SET @SQL_A = @SQL_A + ', CoElement2Name, CoElement2Grade';
					SET @SQL_A = @SQL_A + ', CoElement3Name, CoElement3Grade';
					SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
					SET @SQL_B = @SQL_B + ',''' + @Element3 + ''',' + Cast(@Grade3 as varchar);
					SET @SQL_B = @SQL_B + ',''' + @Element4 + ''',' + Cast(@Grade4 as varchar);
				END
				IF @ElementCount = 5
				BEGIN
					SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
					SET @SQL_A = @SQL_A + ', CoElement2Name, CoElement2Grade';
					SET @SQL_A = @SQL_A + ', CoElement3Name, CoElement3Grade';
					SET @SQL_A = @SQL_A + ', CoElement4Name, CoElement4Grade';
					SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
					SET @SQL_B = @SQL_B + ',''' + @Element3 + ''',' + Cast(@Grade3 as varchar);
					SET @SQL_B = @SQL_B + ',''' + @Element4 + ''',' + Cast(@Grade4 as varchar);
					SET @SQL_B = @SQL_B + ',''' + @Element5 + ''',' + Cast(@Grade5 as varchar);
				END
				IF @ElementCount = 6
				BEGIN
					SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
					SET @SQL_A = @SQL_A + ', CoElement2Name, CoElement2Grade';
					SET @SQL_A = @SQL_A + ', CoElement3Name, CoElement3Grade';
					SET @SQL_A = @SQL_A + ', CoElement4Name, CoElement4Grade';
					SET @SQL_A = @SQL_A + ', CoElement5Name, CoElement5Grade';
					SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
					SET @SQL_B = @SQL_B + ',''' + @Element3 + ''',' + Cast(@Grade3 as varchar);
					SET @SQL_B = @SQL_B + ',''' + @Element4 + ''',' + Cast(@Grade4 as varchar);
					SET @SQL_B = @SQL_B + ',''' + @Element5 + ''',' + Cast(@Grade5 as varchar);
					SET @SQL_B = @SQL_B + ',''' + @Element6 + ''',' + Cast(@Grade6 as varchar);
				END
				IF @ElementCount = 7
				BEGIN
					SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
					SET @SQL_A = @SQL_A + ', CoElement2Name, CoElement2Grade';
					SET @SQL_A = @SQL_A + ', CoElement3Name, CoElement3Grade';
					SET @SQL_A = @SQL_A + ', CoElement4Name, CoElement4Grade';
					SET @SQL_A = @SQL_A + ', CoElement5Name, CoElement5Grade';
					SET @SQL_A = @SQL_A + ', CoElement6Name, CoElement6Grade';
					SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
					SET @SQL_B = @SQL_B + ',''' + @Element3 + ''',' + Cast(@Grade3 as varchar);
					SET @SQL_B = @SQL_B + ',''' + @Element4 + ''',' + Cast(@Grade4 as varchar);
					SET @SQL_B = @SQL_B + ',''' + @Element5 + ''',' + Cast(@Grade5 as varchar);
					SET @SQL_B = @SQL_B + ',''' + @Element6 + ''',' + Cast(@Grade6 as varchar);
					SET @SQL_B = @SQL_B + ',''' + @Element7 + ''',' + Cast(@Grade7 as varchar);
				END
				SET @SQL = @SQL_A + ') VALUES (' + @SQL_B + ');';

				EXEC (@SQL);
			END
			/*Reset intercept and waste variables*/
			SET @PrevTo = @mTo;
			SET @IntStart = -1;
			SET @IntEnd = -1;
			SET @IntValue1 = 0;
			SET @IntValue2 = 0;
			SET @IntValue3 = 0;
			SET @IntValue4 = 0;
			SET @IntValue5 = 0;
			SET @IntValue6 = 0;
			SET @IntValue7 = 0;
			SET @WasteValue1 = 0;
			SET @WasteValue2 = 0;
			SET @WasteValue3 = 0;
			SET @WasteValue4 = 0;
			SET @WasteValue5 = 0;
			SET @WasteValue6 = 0;
			SET @WasteValue7 = 0;
		END

		IF @Value1 < @MinCutOff AND @IntStart = -1 GOTO GET_NEXT;
		IF @Value1 >= @MinCutoff GOTO BUILD_INTERCEPT;
		IF @Value1 < @MinCutOff AND @IntStart <> -1 GOTO CHECK_WASTE;


BUILD_INTERCEPT:
		IF @Value1 > @MaxCutoff SET @Value1 = @MaxCutoff;/*Trim value back to maximum cut off value*/

		IF @IntStart = -1 /*Start a brand new intercept*/
		BEGIN
			SET @PrevTo = @mTo;
			SET @IntStart = @mFrom;
			SET @IntEnd = @mTo;
			SET @IntValue1 = dbo.ZeroDL(@Value1) * (@mTo - @mFrom);
			SET @IntValue2 = dbo.ZeroDL(@Value2) * (@mTo - @mFrom);
			SET @IntValue3 = dbo.ZeroDL(@Value3) * (@mTo - @mFrom);
			SET @IntValue4 = dbo.ZeroDL(@Value4) * (@mTo - @mFrom);
			SET @IntValue5 = dbo.ZeroDL(@Value5) * (@mTo - @mFrom);
			SET @IntValue6 = dbo.ZeroDL(@Value6) * (@mTo - @mFrom);
			SET @IntValue7 = dbo.ZeroDL(@Value7) * (@mTo - @mFrom);
			SET @WasteValue1 = 0;
			SET @WasteValue2 = 0;
			SET @WasteValue3 = 0;
			SET @WasteValue4 = 0;
			SET @WasteValue5 = 0;
			SET @WasteValue6 = 0;
			SET @WasteValue7 = 0;
		END
		ELSE /*Add to existing intercept*/
		BEGIN
			SET @PrevTo = @mTo;
			SET @IntEnd = @mTo;
			SET @IntValue1 = @IntValue1 + dbo.ZeroDL(@Value1) * (@mTo - @mFrom) + @WasteValue1;
			SET @IntValue2 = @IntValue2 + dbo.ZeroDL(@Value2) * (@mTo - @mFrom) + @WasteValue2;
			SET @IntValue3 = @IntValue3 + dbo.ZeroDL(@Value3) * (@mTo - @mFrom) + @WasteValue3;
			SET @IntValue4 = @IntValue4 + dbo.ZeroDL(@Value4) * (@mTo - @mFrom) + @WasteValue4;
			SET @IntValue5 = @IntValue5 + dbo.ZeroDL(@Value5) * (@mTo - @mFrom) + @WasteValue5;
			SET @IntValue6 = @IntValue6 + dbo.ZeroDL(@Value6) * (@mTo - @mFrom) + @WasteValue6;
			SET @IntValue7 = @IntValue7 + dbo.ZeroDL(@Value7) * (@mTo - @mFrom) + @WasteValue7;
			SET @WasteValue1 = 0;
			SET @WasteValue2 = 0;
			SET @WasteValue3 = 0;
			SET @WasteValue4 = 0;
			SET @WasteValue5 = 0;
			SET @WasteValue6 = 0;
			SET @WasteValue7 = 0;
		END
		GOTO GET_NEXT

CHECK_WASTE:

	IF (@mTo - @IntEnd) <= @MaxInternalWaste
	BEGIN
		SET @WasteValue1 = @WasteValue1 + dbo.ZeroDL(@Value1) * (@mTo - @mFrom);
		SET @WasteValue2 = @WasteValue2 + dbo.ZeroDL(@Value2) * (@mTo - @mFrom);
		SET @WasteValue3 = @WasteValue3 + dbo.ZeroDL(@Value3) * (@mTo - @mFrom);
		SET @WasteValue4 = @WasteValue4 + dbo.ZeroDL(@Value4) * (@mTo - @mFrom);
		SET @WasteValue5 = @WasteValue5 + dbo.ZeroDL(@Value5) * (@mTo - @mFrom);
		SET @WasteValue6 = @WasteValue6 + dbo.ZeroDL(@Value6) * (@mTo - @mFrom);
		SET @WasteValue7 = @WasteValue7 + dbo.ZeroDL(@Value7) * (@mTo - @mFrom);
		SET @PrevTo = @mTo;
		--PRINT @Hole_ID + ',' + Cast(@mFrom as varchar) + ',' + cast(@WasteValue1 as varchar);
	END
	ELSE /*maximum waste will be exceeded by this interval so check if an intercept can be written*/
	BEGIN
		IF @IntEnd - @IntStart >= @MinIntercept
		BEGIN
		/*Write Intercept*/
			SET @Grade1 = ROUND((@IntValue1/(@IntEnd - @IntStart)),2);
			SET @Grade2 = ROUND((@IntValue2/(@IntEnd - @IntStart)),2);
			SET @Grade3 = ROUND((@IntValue3/(@IntEnd - @IntStart)),2);
			SET @Grade4 = ROUND((@IntValue4/(@IntEnd - @IntStart)),2);
			SET @Grade5 = ROUND((@IntValue5/(@IntEnd - @IntStart)),2);
			SET @Grade6 = ROUND((@IntValue6/(@IntEnd - @IntStart)),2);
			SET @Grade7 = ROUND((@IntValue7/(@IntEnd - @IntStart)),2);
			SET @SQL_A = 'INSERT INTO tblDHInterceptsCustom (PackageID, Owner, Dataset, Hole_ID, mFrom, mTo, IntervalWidth, Element, Grade,MinCutOff,MaxCutOff,MinIntercept,MaxInternalWaste';
			SET @SQL_B = '''' + cast(@PackageID as varchar) + ''',''' + @Owner + ''',''' +  @Dataset + ''',''' + @Hole_ID + ''',' +  cast(@IntStart as varchar);
			SET @SQL_B = @SQL_B + ',' +  cast(@IntEnd as varchar) + ',' + cast(ROUND((@IntEnd - @IntStart),2) as varchar);
			SET @SQL_B = @SQL_B + ',''' + @Element1 + ''',' + cast(@Grade1 as varchar);
			SET @SQL_B = @SQL_B + ',' + cast(@MinCutOff as varchar) + ',' +  cast(@MaxCutOff as varchar) + ',' +  cast(@MinIntercept as varchar) + ',' +  cast(@MaxInternalWaste as varchar);
			
			IF @ElementCount = 2
			BEGIN
				SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
				SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
			END
			IF @ElementCount = 3
			BEGIN
				SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
				SET @SQL_A = @SQL_A + ', CoElement2Name, CoElement2Grade';
				SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element3 + ''',' + Cast(@Grade3 as varchar);
			END
			IF @ElementCount = 4
			BEGIN
				SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
				SET @SQL_A = @SQL_A + ', CoElement2Name, CoElement2Grade';
				SET @SQL_A = @SQL_A + ', CoElement3Name, CoElement3Grade';
				SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element3 + ''',' + Cast(@Grade3 as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element4 + ''',' + Cast(@Grade4 as varchar);
			END
			IF @ElementCount = 5
			BEGIN
				SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
				SET @SQL_A = @SQL_A + ', CoElement2Name, CoElement2Grade';
				SET @SQL_A = @SQL_A + ', CoElement3Name, CoElement3Grade';
				SET @SQL_A = @SQL_A + ', CoElement4Name, CoElement4Grade';
				SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element3 + ''',' + Cast(@Grade3 as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element4 + ''',' + Cast(@Grade4 as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element5 + ''',' + Cast(@Grade5 as varchar);
			END
			IF @ElementCount = 6
			BEGIN
				SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
				SET @SQL_A = @SQL_A + ', CoElement2Name, CoElement2Grade';
				SET @SQL_A = @SQL_A + ', CoElement3Name, CoElement3Grade';
				SET @SQL_A = @SQL_A + ', CoElement4Name, CoElement4Grade';
				SET @SQL_A = @SQL_A + ', CoElement5Name, CoElement5Grade';
				SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element3 + ''',' + Cast(@Grade3 as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element4 + ''',' + Cast(@Grade4 as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element5 + ''',' + Cast(@Grade5 as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element6 + ''',' + Cast(@Grade6 as varchar);
			END
			IF @ElementCount = 7
			BEGIN
				SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
				SET @SQL_A = @SQL_A + ', CoElement2Name, CoElement2Grade';
				SET @SQL_A = @SQL_A + ', CoElement3Name, CoElement3Grade';
				SET @SQL_A = @SQL_A + ', CoElement4Name, CoElement4Grade';
				SET @SQL_A = @SQL_A + ', CoElement5Name, CoElement5Grade';
				SET @SQL_A = @SQL_A + ', CoElement6Name, CoElement6Grade';
				SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element3 + ''',' + Cast(@Grade3 as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element4 + ''',' + Cast(@Grade4 as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element5 + ''',' + Cast(@Grade5 as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element6 + ''',' + Cast(@Grade6 as varchar);
				SET @SQL_B = @SQL_B + ',''' + @Element7 + ''',' + Cast(@Grade7 as varchar);
			END

			SET @SQL = @SQL_A + ') VALUES (' + @SQL_B + ');';

			EXEC (@SQL);
		

			/*reset intercept and waste*/
			SET @PrevTo = @mTo;
			SET @IntStart = -1;
			SET @IntEnd = -1;
			SET @IntValue1 = 0;
			SET @IntValue2 = 0;
			SET @IntValue3 = 0;
			SET @IntValue4 = 0;
			SET @IntValue5 = 0;
			SET @IntValue6 = 0;
			SET @IntValue7 = 0;
			SET @WasteValue1 = 0;
			SET @WasteValue2 = 0;
			SET @WasteValue3 = 0;
			SET @WasteValue4 = 0;
			SET @WasteValue5 = 0;
			SET @WasteValue6 = 0;
			SET @WasteValue7 = 0;
		END
	END

GET_NEXT:
/*Fetch the next record*/
		IF @ElementCount = 1
		BEGIN
			FETCH NEXT FROM DHAssays into @mFrom, @mTo, @Value1;
			SET @Value1 = isnull(@Value1,0)
		END
		IF @ElementCount = 2
		BEGIN
			FETCH NEXT FROM DHAssays into @mFrom, @mTo, @Value1, @Value2;
			SET @Value1 = isnull(@Value1,0)
			SET @Value2 = isnull(@Value2,0)

		END
		IF @ElementCount = 3
		BEGIN
			FETCH NEXT FROM DHAssays into @mFrom, @mTo, @Value1, @Value2, @Value3;
			SET @Value1 = isnull(@Value1,0)
			SET @Value2 = isnull(@Value2,0)
			SET @Value3 = isnull(@Value3,0)
		END
		IF @ElementCount = 4
		BEGIN
			FETCH NEXT FROM DHAssays into @mFrom, @mTo, @Value1, @Value2, @Value3, @Value4;
			SET @Value1 = isnull(@Value1,0)
			SET @Value2 = isnull(@Value2,0)
			SET @Value3 = isnull(@Value3,0)
			SET @Value4 = isnull(@Value4,0)
		END
		IF @ElementCount = 5
		BEGIN
			FETCH NEXT FROM DHAssays into @mFrom, @mTo, @Value1, @Value2, @Value3, @Value4, @Value5;
			SET @Value1 = isnull(@Value1,0)
			SET @Value2 = isnull(@Value2,0)
			SET @Value3 = isnull(@Value3,0)
			SET @Value4 = isnull(@Value4,0)
			SET @Value5 = isnull(@Value5,0)
		END
		IF @ElementCount = 6
		BEGIN
			FETCH NEXT FROM DHAssays into @mFrom, @mTo, @Value1, @Value2, @Value3, @Value4, @Value5, @Value6;
			SET @Value1 = isnull(@Value1,0)
			SET @Value2 = isnull(@Value2,0)
			SET @Value3 = isnull(@Value3,0)
			SET @Value4 = isnull(@Value4,0)
			SET @Value5 = isnull(@Value5,0)
			SET @Value6 = isnull(@Value6,0)
		END
		IF @ElementCount = 7
		BEGIN
			FETCH NEXT FROM DHAssays into @mFrom, @mTo, @Value1, @Value2, @Value3, @Value4, @Value5, @Value6, @Value7;
			SET @Value1 = isnull(@Value1,0)
			SET @Value2 = isnull(@Value2,0)
			SET @Value3 = isnull(@Value3,0)
			SET @Value4 = isnull(@Value4,0)
			SET @Value5 = isnull(@Value5,0)
			SET @Value6 = isnull(@Value6,0)
			SET @Value7 = isnull(@Value7,0)
		END
END /*End of loop*/
/*Write the last intercept if it is valid*/
IF @IntStart <> -1 AND (@IntEnd - @IntStart >= @MinIntercept)
BEGIN
	/*Write Intercept*/
	SET @Grade1 = ROUND((@IntValue1/(@IntEnd - @IntStart)),2);
	SET @Grade2 = ROUND((@IntValue2/(@IntEnd - @IntStart)),2);
	SET @Grade3 = ROUND((@IntValue3/(@IntEnd - @IntStart)),2);
	SET @Grade4 = ROUND((@IntValue4/(@IntEnd - @IntStart)),2);
	SET @Grade5 = ROUND((@IntValue5/(@IntEnd - @IntStart)),2);
	SET @Grade6 = ROUND((@IntValue6/(@IntEnd - @IntStart)),2);
	SET @Grade7 = ROUND((@IntValue7/(@IntEnd - @IntStart)),2);
	SET @SQL_A = 'INSERT INTO tblDHInterceptsCustom (PackageID, Owner, Dataset, Hole_ID, mFrom, mTo, IntervalWidth, Element, Grade,MinCutOff,MaxCutOff,MinIntercept,MaxInternalWaste';
	SET @SQL_B = '''' + cast(@PackageID as varchar) + ''',''' + @Owner + ''',''' +  @Dataset + ''',''' + @Hole_ID + ''',' +  cast(@IntStart as varchar);
	SET @SQL_B = @SQL_B + ',' +  cast(@IntEnd as varchar) + ',' + cast(ROUND((@IntEnd - @IntStart),2) as varchar);
	SET @SQL_B = @SQL_B + ',''' + @Element1 + ''',' + cast(@Grade1 as varchar);
	SET @SQL_B = @SQL_B + ',' + cast(@MinCutOff as varchar) + ',' +  cast(@MaxCutOff as varchar) + ',' +  cast(@MinIntercept as varchar) + ',' +  cast(@MaxInternalWaste as varchar);
	
	IF @ElementCount = 2
	BEGIN
		SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
		SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
	END
	IF @ElementCount = 3
	BEGIN
		SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
		SET @SQL_A = @SQL_A + ', CoElement2Name, CoElement2Grade';
		SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
		SET @SQL_B = @SQL_B + ',''' + @Element3 + ''',' + Cast(@Grade3 as varchar);
	END
	IF @ElementCount = 4
	BEGIN
		SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
		SET @SQL_A = @SQL_A + ', CoElement2Name, CoElement2Grade';
		SET @SQL_A = @SQL_A + ', CoElement3Name, CoElement3Grade';
		SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
		SET @SQL_B = @SQL_B + ',''' + @Element3 + ''',' + Cast(@Grade3 as varchar);
		SET @SQL_B = @SQL_B + ',''' + @Element4 + ''',' + Cast(@Grade4 as varchar);
	END
	IF @ElementCount = 5
	BEGIN
		SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
		SET @SQL_A = @SQL_A + ', CoElement2Name, CoElement2Grade';
		SET @SQL_A = @SQL_A + ', CoElement3Name, CoElement3Grade';
		SET @SQL_A = @SQL_A + ', CoElement4Name, CoElement4Grade';
		SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
		SET @SQL_B = @SQL_B + ',''' + @Element3 + ''',' + Cast(@Grade3 as varchar);
		SET @SQL_B = @SQL_B + ',''' + @Element4 + ''',' + Cast(@Grade4 as varchar);
		SET @SQL_B = @SQL_B + ',''' + @Element5 + ''',' + Cast(@Grade5 as varchar);
	END
	IF @ElementCount = 6
	BEGIN
		SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
		SET @SQL_A = @SQL_A + ', CoElement2Name, CoElement2Grade';
		SET @SQL_A = @SQL_A + ', CoElement3Name, CoElement3Grade';
		SET @SQL_A = @SQL_A + ', CoElement4Name, CoElement4Grade';
		SET @SQL_A = @SQL_A + ', CoElement5Name, CoElement5Grade';
		SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
		SET @SQL_B = @SQL_B + ',''' + @Element3 + ''',' + Cast(@Grade3 as varchar);
		SET @SQL_B = @SQL_B + ',''' + @Element4 + ''',' + Cast(@Grade4 as varchar);
		SET @SQL_B = @SQL_B + ',''' + @Element5 + ''',' + Cast(@Grade5 as varchar);
		SET @SQL_B = @SQL_B + ',''' + @Element6 + ''',' + Cast(@Grade6 as varchar);
	END
	IF @ElementCount = 7
	BEGIN
		SET @SQL_A = @SQL_A + ', CoElement1Name, CoElement1Grade';
		SET @SQL_A = @SQL_A + ', CoElement2Name, CoElement2Grade';
		SET @SQL_A = @SQL_A + ', CoElement3Name, CoElement3Grade';
		SET @SQL_A = @SQL_A + ', CoElement4Name, CoElement4Grade';
		SET @SQL_A = @SQL_A + ', CoElement5Name, CoElement5Grade';
		SET @SQL_A = @SQL_A + ', CoElement6Name, CoElement6Grade';
		SET @SQL_B = @SQL_B + ',''' + @Element2 + ''',' + Cast(@Grade2 as varchar);
		SET @SQL_B = @SQL_B + ',''' + @Element3 + ''',' + Cast(@Grade3 as varchar);
		SET @SQL_B = @SQL_B + ',''' + @Element4 + ''',' + Cast(@Grade4 as varchar);
		SET @SQL_B = @SQL_B + ',''' + @Element5 + ''',' + Cast(@Grade5 as varchar);
		SET @SQL_B = @SQL_B + ',''' + @Element6 + ''',' + Cast(@Grade6 as varchar);
		SET @SQL_B = @SQL_B + ',''' + @Element7 + ''',' + Cast(@Grade7 as varchar);
	END

	SET @SQL = @SQL_A + ') VALUES (' + @SQL_B + ');';

	EXEC (@SQL);
END

CLOSE DHAssays;
DEALLOCATE DHAssays;
END