if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spWIASRDExport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spWIASRDExport]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- spWIASRDExport 1
CREATE PROCEDURE spWIASRDExport
		@pnUserID int
AS
Begin

	DECLARE @nProgYr int
	DECLARE @StDate datetime
	DECLARE @EndDate datetime
	DECLARE @StCode varchar(5)
	DECLARE @strSQL varchar(1000)
	DECLARE @strAdultSQL varchar(8000)
	DECLARE @strNEGSQL varchar(8000)
	DECLARE @strYouthSQL varchar(8000)
	DECLARE @strYYSQL varchar(8000)
	DECLARE @strOYSQL varchar(8000)
	DECLARE @cTempDBName varchar(50)
	
	SELECT @nProgYr = RIGHT(C.CodeAbbrv, 4), @StCode = uro.StateCode
	FROM   UserReportingOptions uro
	INNER JOIN Code C
	ON C.CodeID = uro.ProgramYearID
	AND C.CodeGroup = 'ProgramYear'
	
	SELECT @cTempDBName = TempDBName
	FROM WIALXSystem..DBNames
	WHERE UserID = @pnUserID
	AND	ApplicationAbbrv = 'WIA'
	AND  IsActive = 1

	CREATE TABLE #tmpDates(StDate DateTime, EndDate DateTime)
	
	SELECT @StDate  = CONVERT(datetime, '01/01/' + str(@nProgYr - 1), 101)
	SELECT @EndDate = CONVERT(datetime, '06/30/' + str(@nProgYr + 1), 101)

--	SELECT @StDate  = ltrim(rtrim(str(@nProgYr - 1))) + '-01-01'
--	SELECT @EndDate = ltrim(rtrim(str(@nProgYr + 1))) + '-06-30'

	INSERT INTO #tmpDates(StDate, EndDate)
	VALUES( @StDate, @EndDate)

	SELECT @StCode as StCode, @nProgYr as ProgYr 

	SET @strAdultSQL = ''
	SET @strNEGSQL = ''
	SET @strYouthSQL = ''
	SET @strYYSQL = ''
	SET @strOYSQL = ''

	/* All adult and dislocated worker records that received more than self-service and have exit dates between 1/1/PY-1 and 6/30/PY+1 (inclusive). */
	/* All NEG records that have exit dates between 1/1/PY-1 and 6/30/PY+1 (inclusive). */
	/* All youth records that have exit dates between 1/1/PY-1 and 6/30/PY+1 (inclusive). */
	/* All younger youth records with null exit dates that are included in the Skill Attainment and/or Literacy/Numeracy calculations for the program year.*/	
	/* All older youth records with null exit dates that are included in the Literacy/Numeracy calculations for the program year. */

--	SET @strSQL = 'SELECT * INTO #tmpResults FROM ' + @cTempDBName + '..tmpFileUpload_WIA'

--	SELECT * FROM #tmpResults

	CREATE TABLE #tmpAdults (IndividualIdentifier varchar(19),DateOfBirth varchar(18),Gender varchar(11),DisabilityStatus varchar(11),Hispanic varchar(11),NativeAmerican varchar(11),Asian varchar(11),
	Black varchar(11),PacificIslander varchar(11),White varchar(11),VeteranStatus varchar(11),VeteranCampaign varchar(11),VeteranDisabled varchar(11),VeteranRecentlySeparated varchar(11),EmploymentStatus varchar(11),
	LEPStatus varchar(11),SingleParentStatus varchar(11),UCEligibleStatus varchar(11),LowIncomeStatus varchar(11),NeedyFamilyStatus varchar(11),OtherPublicAssistanceStatus varchar(11),HighestGradeCompleted varchar(12),
	DisplaceHomemakerIndicator varchar(11),DislocationDate varchar(18),HomelessRunawayIndicator varchar(11),OffenderIndicator varchar(11),PregnantParentIndicator varchar(11),AdditionalAssistanceIndicator varchar(11),
	SchoolParticipation varchar(11),LiteracySkillsIndicator varchar(11),FosterCareIndicator varchar(11),ETACode varchar(15),ProgramParticipationDate varchar(18),ProgramExitDate varchar(18),AdultLocal varchar(11),
	DWLocal varchar(11),FirstYouthServiceDate varchar(18),YouthStatewide15Percent varchar(11),DWStatewide15Percent varchar(11),IWStatewide15Percent varchar(11),AdultStatewide15Percent varchar(11),RapidResponse varchar(11),
	AddRapidResponse varchar(11),NEGProject1 varchar(14),NEGProject2 varchar(14),NEGProject3 varchar(14),AdultEducation varchar(11),JobCorps varchar(11),FarmworkerJobs varchar(11),IndianPrograms varchar(11),VeteranPrograms varchar(11),
	TradeAssistance varchar(11),VocationalEducation varchar(11),VocationalRehabilitation varchar(11),WagnerPeyser varchar(11),YouthBuild varchar(11),TitleVOlder varchar(11),FoodStamps varchar(11),OtherNonWIA varchar(11),
	OtherExitReasons varchar(12),SupportiveServices varchar(11),NeedsRelated varchar(11),DisasterRelief varchar(11),CoreSelfServices varchar(11),FirstCoreServiceDate varchar(18),WorkforceInfoService varchar(11),FirstIntensiveService varchar(18),
	DateEnterTraining varchar(18),DateExitTraining varchar(18),ITA varchar(11),PellGrant varchar(11),PreVocational varchar(11),TrainingService1 varchar(11),TrainingService2 varchar(11),OTCCode varchar(18),Enrolled varchar(11),
	AchievementServices varchar(11),EmploymentServices varchar(11),SummerEmployment varchar(11),AdditionalSupport varchar(11),LeadershipDevelopment varchar(11),FollowupServices varchar(11),ExitEmployed1 varchar(11),ExitEmployedMatch1 varchar(11),
	OccupationCode varchar(18),EmploymentTraining varchar(11),NonTraditionalEmployment varchar(11),ExitEmployed2 varchar(11),ExitEmployedMatch2 varchar(11),ExitEmployed3 varchar(11),ExitEmployedMatch3 varchar(11),ExitEmployed4 varchar(11),
	ExitEmployedMatch4 varchar(11),PriorWages3 varchar(19),PriorWages2 varchar(19),PriorWages1 varchar(19),ExitWages1 varchar(19),ExitWages2 varchar(19),ExitWages3 varchar(19),ExitWages4 varchar(19),CredentialType varchar(11),
	GoalType1 varchar(11),DateGoalSet1 varchar(18),GoalAttainment1 varchar(11),DateGoalAttained1 varchar(18),GoalType2 varchar(11),DateGoalSet2 varchar(18),GoalAttainment2 varchar(11),DateGoalAttained2 varchar(18),GoalType3 varchar(11),
	DateGoalSet3 varchar(18),GoalAttainment3 varchar(11),DateGoalAttained3 varchar(18),GoalType4 varchar(11),DateGoalSet4 varchar(18),GoalAttainment4 varchar(11),DateGoalAttained4 varchar(18),GoalType5 varchar(11),DateGoalSet5 varchar(18),
	GoalAttainment5 varchar(11),DateGoalAttained5 varchar(18),GoalType6 varchar(11),DateGoalSet6 varchar(18),GoalAttainment6 varchar(11),DateGoalAttained6 varchar(18),GoalType7 varchar(11),DateGoalSet7 varchar(18),GoalAttainment7 varchar(11),
	DateGoalAttained7 varchar(18),GoalType8 varchar(11),DateGoalSet8 varchar(18),GoalAttainment8 varchar(11),DateGoalAttained8 varchar(18),GoalType9 varchar(11),DateGoalSet9 varchar(18),GoalAttainment9 varchar(11),DateGoalAttained9 varchar(18),
	GoalType10 varchar(11),DateGoalSet10 varchar(18),GoalAttainment10 varchar(11),DateGoalAttained10 varchar(18),GoalType11 varchar(11),DateGoalSet11 varchar(18),GoalAttainment11 varchar(11),DateGoalAttained11 varchar(18),
	GoalType12 varchar(11),DateGoalSet12 varchar(18),GoalAttainment12 varchar(11),DateGoalAttained12 varchar(18),DegreeCertificate varchar(11),DateDegreeCertificate varchar(18),ExitSchoolStatus varchar(11),YouthPlacement varchar(11),
	YouthRetention varchar(11),AssessmentCategory varchar(11),AssessmentType1 varchar(11),FunctionalArea1 varchar(11),PreTestDate1 varchar(18),PreTestScore1 varchar(13),PreTestEducationLevel1 varchar(11),PostTestDate1Y1 varchar(18),
	PostTestScore1Y1 varchar(13),PostTestEducationLevel1Y1 varchar(11),PostTestDate1Y2 varchar(18),PostTestScore1Y2 varchar(13),PostTestEducationLevel1Y2 varchar(11),PostTestDate1Y3 varchar(18),PostTestScore1Y3 varchar(13),
	PostTestEducationLevel1Y3 varchar(11),AssessmentType2 varchar(11),FunctionalArea2 varchar(11),PreTestDate2 varchar(18),PreTestScore2 varchar(13),PreTestEducationLevel2 varchar(11),PostTestDate2Y1 varchar(18),PostTestScore2Y1 varchar(13),
	PostTestEducationLevel2Y1 varchar(11),PostTestDate2Y2 varchar(18),PostTestScore2Y2 varchar(13),PostTestEducationLevel2Y2 varchar(11),PostTestDate2Y3 varchar(18),PostTestScore2Y3 varchar(13),PostTestEducationLevel2Y3 varchar(11),
	AssessmentType3 varchar(11),FunctionalArea3 varchar(11),PreTestDate3 varchar(18),PreTestScore3 varchar(13),PreTestEducationLevel3 varchar(11),PostTestDate3Y1 varchar(18),PostTestScore3Y1 varchar(13),PostTestEducationLevel3Y1 varchar(11),
	PostTestDate3Y2 varchar(18),PostTestScore3Y2 varchar(13),PostTestEducationLevel3Y2 varchar(11),PostTestDate3Y3 varchar(18),PostTestScore3Y3 varchar(13),PostTestEducationLevel3Y3 varchar(11)  NULL)
	
	SET @strAdultSQL = 'INSERT INTO #tmpAdults SELECT tmp.IndividualIdentifier, tmp.DateOfBirth, tmp.Gender, tmp.DisabilityStatus, tmp.Hispanic, tmp.NativeAmerican, tmp.Asian, tmp.Black, tmp.PacificIslander, tmp.White, tmp.VeteranStatus, tmp.VeteranCampaign, tmp.VeteranDisabled, tmp.VeteranRecentlySeparated, tmp.EmploymentStatus, tmp.LEPStatus, tmp.SingleParentStatus, tmp.UCEligibleStatus, tmp.LowIncomeStatus, tmp.NeedyFamilyStatus, tmp.OtherPublicAssistanceStatus, tmp.HighestGradeCompleted, tmp.DisplaceHomemakerIndicator, tmp.DislocationDate, tmp.HomelessRunawayIndicator, tmp.OffenderIndicator, tmp.PregnantParentIndicator, tmp.AdditionalAssistanceIndicator, tmp.SchoolParticipation, tmp.LiteracySkillsIndicator, tmp.FosterCareIndicator, tmp.ETACode, tmp.ProgramParticipationDate, tmp.ProgramExitDate, tmp.AdultLocal, tmp.DWLocal, tmp.FirstYouthServiceDate, tmp.YouthStatewide15Percent, tmp.DWStatewide15Percent, tmp.IWStatewide15Percent, tmp.AdultStatewide15Percent, tmp.RapidResponse, tmp.AddRapidResponse, tmp.NEGProject1, tmp.NEGProject2, tmp.NEGProject3, tmp.AdultEducation, tmp.JobCorps, tmp.FarmworkerJobs, tmp.IndianPrograms, tmp.VeteranPrograms, tmp.TradeAssistance, tmp.VocationalEducation, tmp.VocationalRehabilitation, tmp.WagnerPeyser, tmp.YouthBuild, tmp.TitleVOlder, tmp.FoodStamps, tmp.OtherNonWIA, tmp.OtherExitReasons, tmp.SupportiveServices, tmp.NeedsRelated, tmp.DisasterRelief, tmp.CoreSelfServices, tmp.FirstCoreServiceDate, tmp.WorkforceInfoService, tmp.FirstIntensiveService, tmp.DateEnterTraining, tmp.DateExitTraining, tmp.ITA, tmp.PellGrant, tmp.PreVocational, tmp.TrainingService1, tmp.TrainingService2, tmp.OTCCode, tmp.Enrolled, tmp.AchievementServices, tmp.EmploymentServices, tmp.SummerEmployment, tmp.AdditionalSupport, tmp.LeadershipDevelopment, tmp.FollowupServices, tmp.ExitEmployed1, tmp.ExitEmployedMatch1, tmp.OccupationCode, tmp.EmploymentTraining, tmp.NonTraditionalEmployment, tmp.ExitEmployed2, tmp.ExitEmployedMatch2, tmp.ExitEmployed3, tmp.ExitEmployedMatch3, tmp.ExitEmployed4, tmp.ExitEmployedMatch4, tmp.PriorWages3, tmp.PriorWages2, tmp.PriorWages1, tmp.ExitWages1, tmp.ExitWages2, tmp.ExitWages3, tmp.ExitWages4, tmp.CredentialType, tmp.GoalType1, tmp.DateGoalSet1, tmp.GoalAttainment1, tmp.DateGoalAttained1, tmp.GoalType2, tmp.DateGoalSet2, tmp.GoalAttainment2, tmp.DateGoalAttained2, tmp.GoalType3, tmp.DateGoalSet3, tmp.GoalAttainment3, tmp.DateGoalAttained3, tmp.GoalType4, tmp.DateGoalSet4, tmp.GoalAttainment4, tmp.DateGoalAttained4, tmp.GoalType5, tmp.DateGoalSet5, tmp.GoalAttainment5, tmp.DateGoalAttained5, tmp.GoalType6, tmp.DateGoalSet6, tmp.GoalAttainment6, tmp.DateGoalAttained6, tmp.GoalType7, tmp.DateGoalSet7, tmp.GoalAttainment7, tmp.DateGoalAttained7, tmp.GoalType8, tmp.DateGoalSet8, tmp.GoalAttainment8, tmp.DateGoalAttained8, tmp.GoalType9, tmp.DateGoalSet9, tmp.GoalAttainment9, tmp.DateGoalAttained9, tmp.GoalType10, tmp.DateGoalSet10, tmp.GoalAttainment10, tmp.DateGoalAttained10, tmp.GoalType11, tmp.DateGoalSet11, tmp.GoalAttainment11, tmp.DateGoalAttained11, tmp.GoalType12, tmp.DateGoalSet12, tmp.GoalAttainment12, tmp.DateGoalAttained12, tmp.DegreeCertificate, tmp.DateDegreeCertificate, tmp.ExitSchoolStatus, tmp.YouthPlacement, tmp.YouthRetention, tmp.AssessmentCategory, tmp.AssessmentType1, tmp.FunctionalArea1, tmp.PreTestDate1, tmp.PreTestScore1, tmp.PreTestEducationLevel1, tmp.PostTestDate1Y1, tmp.PostTestScore1Y1, tmp.PostTestEducationLevel1Y1, tmp.PostTestDate1Y2, tmp.PostTestScore1Y2, tmp.PostTestEducationLevel1Y2, tmp.PostTestDate1Y3, tmp.PostTestScore1Y3, tmp.PostTestEducationLevel1Y3, tmp.AssessmentType2, tmp.FunctionalArea2, tmp.PreTestDate2, tmp.PreTestScore2, tmp.PreTestEducationLevel2, tmp.PostTestDate2Y1, tmp.PostTestScore2Y1, tmp.PostTestEducationLevel2Y1, tmp.PostTestDate2Y2, tmp.PostTestScore2Y2, tmp.PostTestEducationLevel2Y2, tmp.PostTestDate2Y3, tmp.PostTestScore2Y3, tmp.PostTestEducationLevel2Y3, tmp.AssessmentType3, tmp.FunctionalArea3, tmp.PreTestDate3, tmp.PreTestScore3, tmp.PreTestEducationLevel3, tmp.PostTestDate3Y1, tmp.PostTestScore3Y1, tmp.PostTestEducationLevel3Y1, tmp.PostTestDate3Y2, tmp.PostTestScore3Y2, 
		  tmp.PostTestEducationLevel3Y2, tmp.PostTestDate3Y3, tmp.PostTestScore3Y3, tmp.PostTestEducationLevel3Y3
	FROM ' + @cTempDBName + '..tmpFileUpload_WIA tmp
	INNER JOIN Participant P
	ON tmp.RecordID = P.ParticipantID
	AND P.ProgramExitDate BETWEEN (SELECT StDate FROM #tmpDates) AND (SELECT EndDate FROM #tmpDates)
	INNER JOIN ParticipantXFundingStream PxFS
	ON P.ParticipantID = PxFS.ParticipantID
	INNER JOIN Code C
	ON PxFS.FundingStreamTypeID = C.CodeID
	AND C.CodeGroup LIKE ''FundingStream%''
	AND C.CodeAbbrv IN (''Adult'', ''DW'')
	WHERE (LEN(ISNULL(RTRIM(LTRIM(tmp.FirstCoreServiceDate)),'''')) > 0 
	OR LEN(ISNULL(RTRIM(LTRIM(tmp.FirstIntensiveService)),'''')) > 0 
	OR (LEN(ISNULL(RTRIM(LTRIM(tmp.DateEnterTraining)),'''')) > 0  
	AND tmp.TrainingService1 > ''0'') )'



	CREATE TABLE #tmpNEG (IndividualIdentifier varchar(19),DateOfBirth varchar(18),Gender varchar(11),DisabilityStatus varchar(11),Hispanic varchar(11),NativeAmerican varchar(11),Asian varchar(11),
	Black varchar(11),PacificIslander varchar(11),White varchar(11),VeteranStatus varchar(11),VeteranCampaign varchar(11),VeteranDisabled varchar(11),VeteranRecentlySeparated varchar(11),EmploymentStatus varchar(11),
	LEPStatus varchar(11),SingleParentStatus varchar(11),UCEligibleStatus varchar(11),LowIncomeStatus varchar(11),NeedyFamilyStatus varchar(11),OtherPublicAssistanceStatus varchar(11),HighestGradeCompleted varchar(12),
	DisplaceHomemakerIndicator varchar(11),DislocationDate varchar(18),HomelessRunawayIndicator varchar(11),OffenderIndicator varchar(11),PregnantParentIndicator varchar(11),AdditionalAssistanceIndicator varchar(11),
	SchoolParticipation varchar(11),LiteracySkillsIndicator varchar(11),FosterCareIndicator varchar(11),ETACode varchar(15),ProgramParticipationDate varchar(18),ProgramExitDate varchar(18),AdultLocal varchar(11),
	DWLocal varchar(11),FirstYouthServiceDate varchar(18),YouthStatewide15Percent varchar(11),DWStatewide15Percent varchar(11),IWStatewide15Percent varchar(11),AdultStatewide15Percent varchar(11),RapidResponse varchar(11),
	AddRapidResponse varchar(11),NEGProject1 varchar(14),NEGProject2 varchar(14),NEGProject3 varchar(14),AdultEducation varchar(11),JobCorps varchar(11),FarmworkerJobs varchar(11),IndianPrograms varchar(11),VeteranPrograms varchar(11),
	TradeAssistance varchar(11),VocationalEducation varchar(11),VocationalRehabilitation varchar(11),WagnerPeyser varchar(11),YouthBuild varchar(11),TitleVOlder varchar(11),FoodStamps varchar(11),OtherNonWIA varchar(11),
	OtherExitReasons varchar(12),SupportiveServices varchar(11),NeedsRelated varchar(11),DisasterRelief varchar(11),CoreSelfServices varchar(11),FirstCoreServiceDate varchar(18),WorkforceInfoService varchar(11),FirstIntensiveService varchar(18),
	DateEnterTraining varchar(18),DateExitTraining varchar(18),ITA varchar(11),PellGrant varchar(11),PreVocational varchar(11),TrainingService1 varchar(11),TrainingService2 varchar(11),OTCCode varchar(18),Enrolled varchar(11),
	AchievementServices varchar(11),EmploymentServices varchar(11),SummerEmployment varchar(11),AdditionalSupport varchar(11),LeadershipDevelopment varchar(11),FollowupServices varchar(11),ExitEmployed1 varchar(11),ExitEmployedMatch1 varchar(11),
	OccupationCode varchar(18),EmploymentTraining varchar(11),NonTraditionalEmployment varchar(11),ExitEmployed2 varchar(11),ExitEmployedMatch2 varchar(11),ExitEmployed3 varchar(11),ExitEmployedMatch3 varchar(11),ExitEmployed4 varchar(11),
	ExitEmployedMatch4 varchar(11),PriorWages3 varchar(19),PriorWages2 varchar(19),PriorWages1 varchar(19),ExitWages1 varchar(19),ExitWages2 varchar(19),ExitWages3 varchar(19),ExitWages4 varchar(19),CredentialType varchar(11),
	GoalType1 varchar(11),DateGoalSet1 varchar(18),GoalAttainment1 varchar(11),DateGoalAttained1 varchar(18),GoalType2 varchar(11),DateGoalSet2 varchar(18),GoalAttainment2 varchar(11),DateGoalAttained2 varchar(18),GoalType3 varchar(11),
	DateGoalSet3 varchar(18),GoalAttainment3 varchar(11),DateGoalAttained3 varchar(18),GoalType4 varchar(11),DateGoalSet4 varchar(18),GoalAttainment4 varchar(11),DateGoalAttained4 varchar(18),GoalType5 varchar(11),DateGoalSet5 varchar(18),
	GoalAttainment5 varchar(11),DateGoalAttained5 varchar(18),GoalType6 varchar(11),DateGoalSet6 varchar(18),GoalAttainment6 varchar(11),DateGoalAttained6 varchar(18),GoalType7 varchar(11),DateGoalSet7 varchar(18),GoalAttainment7 varchar(11),
	DateGoalAttained7 varchar(18),GoalType8 varchar(11),DateGoalSet8 varchar(18),GoalAttainment8 varchar(11),DateGoalAttained8 varchar(18),GoalType9 varchar(11),DateGoalSet9 varchar(18),GoalAttainment9 varchar(11),DateGoalAttained9 varchar(18),
	GoalType10 varchar(11),DateGoalSet10 varchar(18),GoalAttainment10 varchar(11),DateGoalAttained10 varchar(18),GoalType11 varchar(11),DateGoalSet11 varchar(18),GoalAttainment11 varchar(11),DateGoalAttained11 varchar(18),
	GoalType12 varchar(11),DateGoalSet12 varchar(18),GoalAttainment12 varchar(11),DateGoalAttained12 varchar(18),DegreeCertificate varchar(11),DateDegreeCertificate varchar(18),ExitSchoolStatus varchar(11),YouthPlacement varchar(11),
	YouthRetention varchar(11),AssessmentCategory varchar(11),AssessmentType1 varchar(11),FunctionalArea1 varchar(11),PreTestDate1 varchar(18),PreTestScore1 varchar(13),PreTestEducationLevel1 varchar(11),PostTestDate1Y1 varchar(18),
	PostTestScore1Y1 varchar(13),PostTestEducationLevel1Y1 varchar(11),PostTestDate1Y2 varchar(18),PostTestScore1Y2 varchar(13),PostTestEducationLevel1Y2 varchar(11),PostTestDate1Y3 varchar(18),PostTestScore1Y3 varchar(13),
	PostTestEducationLevel1Y3 varchar(11),AssessmentType2 varchar(11),FunctionalArea2 varchar(11),PreTestDate2 varchar(18),PreTestScore2 varchar(13),PreTestEducationLevel2 varchar(11),PostTestDate2Y1 varchar(18),PostTestScore2Y1 varchar(13),
	PostTestEducationLevel2Y1 varchar(11),PostTestDate2Y2 varchar(18),PostTestScore2Y2 varchar(13),PostTestEducationLevel2Y2 varchar(11),PostTestDate2Y3 varchar(18),PostTestScore2Y3 varchar(13),PostTestEducationLevel2Y3 varchar(11),
	AssessmentType3 varchar(11),FunctionalArea3 varchar(11),PreTestDate3 varchar(18),PreTestScore3 varchar(13),PreTestEducationLevel3 varchar(11),PostTestDate3Y1 varchar(18),PostTestScore3Y1 varchar(13),PostTestEducationLevel3Y1 varchar(11),
	PostTestDate3Y2 varchar(18),PostTestScore3Y2 varchar(13),PostTestEducationLevel3Y2 varchar(11),PostTestDate3Y3 varchar(18),PostTestScore3Y3 varchar(13),PostTestEducationLevel3Y3 varchar(11)  NULL)

	SET @strNEGSQL = 'INSERT INTO #tmpNEG SELECT tmp.IndividualIdentifier, tmp.DateOfBirth, tmp.Gender, tmp.DisabilityStatus, tmp.Hispanic, tmp.NativeAmerican, tmp.Asian, tmp.Black, tmp.PacificIslander, tmp.White, tmp.VeteranStatus, tmp.VeteranCampaign, tmp.VeteranDisabled, tmp.VeteranRecentlySeparated, tmp.EmploymentStatus, tmp.LEPStatus, tmp.SingleParentStatus, tmp.UCEligibleStatus, tmp.LowIncomeStatus, tmp.NeedyFamilyStatus, tmp.OtherPublicAssistanceStatus, tmp.HighestGradeCompleted, tmp.DisplaceHomemakerIndicator, tmp.DislocationDate, tmp.HomelessRunawayIndicator, tmp.OffenderIndicator, tmp.PregnantParentIndicator, tmp.AdditionalAssistanceIndicator, tmp.SchoolParticipation, tmp.LiteracySkillsIndicator, tmp.FosterCareIndicator, tmp.ETACode, tmp.ProgramParticipationDate, tmp.ProgramExitDate, tmp.AdultLocal, tmp.DWLocal, tmp.FirstYouthServiceDate, tmp.YouthStatewide15Percent, tmp.DWStatewide15Percent, tmp.IWStatewide15Percent, tmp.AdultStatewide15Percent, tmp.RapidResponse, tmp.AddRapidResponse, tmp.NEGProject1, tmp.NEGProject2, tmp.NEGProject3, tmp.AdultEducation, tmp.JobCorps, tmp.FarmworkerJobs, tmp.IndianPrograms, tmp.VeteranPrograms, tmp.TradeAssistance, tmp.VocationalEducation, tmp.VocationalRehabilitation, tmp.WagnerPeyser, tmp.YouthBuild, tmp.TitleVOlder, tmp.FoodStamps, tmp.OtherNonWIA, tmp.OtherExitReasons, tmp.SupportiveServices, tmp.NeedsRelated, tmp.DisasterRelief, tmp.CoreSelfServices, tmp.FirstCoreServiceDate, tmp.WorkforceInfoService, tmp.FirstIntensiveService, tmp.DateEnterTraining, tmp.DateExitTraining, tmp.ITA, tmp.PellGrant, tmp.PreVocational, tmp.TrainingService1, tmp.TrainingService2, tmp.OTCCode, tmp.Enrolled, tmp.AchievementServices, tmp.EmploymentServices, tmp.SummerEmployment, tmp.AdditionalSupport, tmp.LeadershipDevelopment, tmp.FollowupServices, tmp.ExitEmployed1, tmp.ExitEmployedMatch1, tmp.OccupationCode, tmp.EmploymentTraining, tmp.NonTraditionalEmployment, tmp.ExitEmployed2, tmp.ExitEmployedMatch2, tmp.ExitEmployed3, tmp.ExitEmployedMatch3, tmp.ExitEmployed4, tmp.ExitEmployedMatch4, tmp.PriorWages3, tmp.PriorWages2, tmp.PriorWages1, tmp.ExitWages1, tmp.ExitWages2, tmp.ExitWages3, tmp.ExitWages4, tmp.CredentialType, tmp.GoalType1, tmp.DateGoalSet1, tmp.GoalAttainment1, tmp.DateGoalAttained1, tmp.GoalType2, tmp.DateGoalSet2, tmp.GoalAttainment2, tmp.DateGoalAttained2, tmp.GoalType3, tmp.DateGoalSet3, tmp.GoalAttainment3, tmp.DateGoalAttained3, tmp.GoalType4, tmp.DateGoalSet4, tmp.GoalAttainment4, tmp.DateGoalAttained4, tmp.GoalType5, tmp.DateGoalSet5, tmp.GoalAttainment5, tmp.DateGoalAttained5, tmp.GoalType6, tmp.DateGoalSet6, tmp.GoalAttainment6, tmp.DateGoalAttained6, tmp.GoalType7, tmp.DateGoalSet7, tmp.GoalAttainment7, tmp.DateGoalAttained7, tmp.GoalType8, tmp.DateGoalSet8, tmp.GoalAttainment8, tmp.DateGoalAttained8, tmp.GoalType9, tmp.DateGoalSet9, tmp.GoalAttainment9, tmp.DateGoalAttained9, tmp.GoalType10, tmp.DateGoalSet10, tmp.GoalAttainment10, tmp.DateGoalAttained10, tmp.GoalType11, tmp.DateGoalSet11, tmp.GoalAttainment11, tmp.DateGoalAttained11, tmp.GoalType12, tmp.DateGoalSet12, tmp.GoalAttainment12, tmp.DateGoalAttained12, tmp.DegreeCertificate, tmp.DateDegreeCertificate, tmp.ExitSchoolStatus, tmp.YouthPlacement, tmp.YouthRetention, tmp.AssessmentCategory, tmp.AssessmentType1, tmp.FunctionalArea1, tmp.PreTestDate1, tmp.PreTestScore1, tmp.PreTestEducationLevel1, tmp.PostTestDate1Y1, tmp.PostTestScore1Y1, tmp.PostTestEducationLevel1Y1, tmp.PostTestDate1Y2, tmp.PostTestScore1Y2, tmp.PostTestEducationLevel1Y2, tmp.PostTestDate1Y3, tmp.PostTestScore1Y3, tmp.PostTestEducationLevel1Y3, tmp.AssessmentType2, tmp.FunctionalArea2, tmp.PreTestDate2, tmp.PreTestScore2, tmp.PreTestEducationLevel2, tmp.PostTestDate2Y1, tmp.PostTestScore2Y1, tmp.PostTestEducationLevel2Y1, tmp.PostTestDate2Y2, tmp.PostTestScore2Y2, tmp.PostTestEducationLevel2Y2, tmp.PostTestDate2Y3, tmp.PostTestScore2Y3, tmp.PostTestEducationLevel2Y3, tmp.AssessmentType3, tmp.FunctionalArea3, tmp.PreTestDate3, tmp.PreTestScore3, tmp.PreTestEducationLevel3, tmp.PostTestDate3Y1, tmp.PostTestScore3Y1, tmp.PostTestEducationLevel3Y1, tmp.PostTestDate3Y2, tmp.PostTestScore3Y2, 
		  tmp.PostTestEducationLevel3Y2, tmp.PostTestDate3Y3, tmp.PostTestScore3Y3, tmp.PostTestEducationLevel3Y3
	FROM ' + @cTempDBName + '..tmpFileUpload_WIA tmp
	INNER JOIN Participant P
	ON tmp.RecordID = P.ParticipantID
	AND P.ProgramExitDate BETWEEN (SELECT StDate FROM #tmpDates) AND (SELECT EndDate FROM #tmpDates)
	INNER JOIN ParticipantXFundingStream PxFS
	ON P.ParticipantID = PxFS.ParticipantID
	INNER JOIN Code C
	ON PxFS.FundingStreamTypeID = C.CodeID
	AND C.CodeGroup LIKE ''FundingStream%''
	AND C.CodeAbbrv IN (''NEG'')'


	CREATE TABLE #tmpYouth (IndividualIdentifier varchar(19),DateOfBirth varchar(18),Gender varchar(11),DisabilityStatus varchar(11),Hispanic varchar(11),NativeAmerican varchar(11),Asian varchar(11),
	Black varchar(11),PacificIslander varchar(11),White varchar(11),VeteranStatus varchar(11),VeteranCampaign varchar(11),VeteranDisabled varchar(11),VeteranRecentlySeparated varchar(11),EmploymentStatus varchar(11),
	LEPStatus varchar(11),SingleParentStatus varchar(11),UCEligibleStatus varchar(11),LowIncomeStatus varchar(11),NeedyFamilyStatus varchar(11),OtherPublicAssistanceStatus varchar(11),HighestGradeCompleted varchar(12),
	DisplaceHomemakerIndicator varchar(11),DislocationDate varchar(18),HomelessRunawayIndicator varchar(11),OffenderIndicator varchar(11),PregnantParentIndicator varchar(11),AdditionalAssistanceIndicator varchar(11),
	SchoolParticipation varchar(11),LiteracySkillsIndicator varchar(11),FosterCareIndicator varchar(11),ETACode varchar(15),ProgramParticipationDate varchar(18),ProgramExitDate varchar(18),AdultLocal varchar(11),
	DWLocal varchar(11),FirstYouthServiceDate varchar(18),YouthStatewide15Percent varchar(11),DWStatewide15Percent varchar(11),IWStatewide15Percent varchar(11),AdultStatewide15Percent varchar(11),RapidResponse varchar(11),
	AddRapidResponse varchar(11),NEGProject1 varchar(14),NEGProject2 varchar(14),NEGProject3 varchar(14),AdultEducation varchar(11),JobCorps varchar(11),FarmworkerJobs varchar(11),IndianPrograms varchar(11),VeteranPrograms varchar(11),
	TradeAssistance varchar(11),VocationalEducation varchar(11),VocationalRehabilitation varchar(11),WagnerPeyser varchar(11),YouthBuild varchar(11),TitleVOlder varchar(11),FoodStamps varchar(11),OtherNonWIA varchar(11),
	OtherExitReasons varchar(12),SupportiveServices varchar(11),NeedsRelated varchar(11),DisasterRelief varchar(11),CoreSelfServices varchar(11),FirstCoreServiceDate varchar(18),WorkforceInfoService varchar(11),FirstIntensiveService varchar(18),
	DateEnterTraining varchar(18),DateExitTraining varchar(18),ITA varchar(11),PellGrant varchar(11),PreVocational varchar(11),TrainingService1 varchar(11),TrainingService2 varchar(11),OTCCode varchar(18),Enrolled varchar(11),
	AchievementServices varchar(11),EmploymentServices varchar(11),SummerEmployment varchar(11),AdditionalSupport varchar(11),LeadershipDevelopment varchar(11),FollowupServices varchar(11),ExitEmployed1 varchar(11),ExitEmployedMatch1 varchar(11),
	OccupationCode varchar(18),EmploymentTraining varchar(11),NonTraditionalEmployment varchar(11),ExitEmployed2 varchar(11),ExitEmployedMatch2 varchar(11),ExitEmployed3 varchar(11),ExitEmployedMatch3 varchar(11),ExitEmployed4 varchar(11),
	ExitEmployedMatch4 varchar(11),PriorWages3 varchar(19),PriorWages2 varchar(19),PriorWages1 varchar(19),ExitWages1 varchar(19),ExitWages2 varchar(19),ExitWages3 varchar(19),ExitWages4 varchar(19),CredentialType varchar(11),
	GoalType1 varchar(11),DateGoalSet1 varchar(18),GoalAttainment1 varchar(11),DateGoalAttained1 varchar(18),GoalType2 varchar(11),DateGoalSet2 varchar(18),GoalAttainment2 varchar(11),DateGoalAttained2 varchar(18),GoalType3 varchar(11),
	DateGoalSet3 varchar(18),GoalAttainment3 varchar(11),DateGoalAttained3 varchar(18),GoalType4 varchar(11),DateGoalSet4 varchar(18),GoalAttainment4 varchar(11),DateGoalAttained4 varchar(18),GoalType5 varchar(11),DateGoalSet5 varchar(18),
	GoalAttainment5 varchar(11),DateGoalAttained5 varchar(18),GoalType6 varchar(11),DateGoalSet6 varchar(18),GoalAttainment6 varchar(11),DateGoalAttained6 varchar(18),GoalType7 varchar(11),DateGoalSet7 varchar(18),GoalAttainment7 varchar(11),
	DateGoalAttained7 varchar(18),GoalType8 varchar(11),DateGoalSet8 varchar(18),GoalAttainment8 varchar(11),DateGoalAttained8 varchar(18),GoalType9 varchar(11),DateGoalSet9 varchar(18),GoalAttainment9 varchar(11),DateGoalAttained9 varchar(18),
	GoalType10 varchar(11),DateGoalSet10 varchar(18),GoalAttainment10 varchar(11),DateGoalAttained10 varchar(18),GoalType11 varchar(11),DateGoalSet11 varchar(18),GoalAttainment11 varchar(11),DateGoalAttained11 varchar(18),
	GoalType12 varchar(11),DateGoalSet12 varchar(18),GoalAttainment12 varchar(11),DateGoalAttained12 varchar(18),DegreeCertificate varchar(11),DateDegreeCertificate varchar(18),ExitSchoolStatus varchar(11),YouthPlacement varchar(11),
	YouthRetention varchar(11),AssessmentCategory varchar(11),AssessmentType1 varchar(11),FunctionalArea1 varchar(11),PreTestDate1 varchar(18),PreTestScore1 varchar(13),PreTestEducationLevel1 varchar(11),PostTestDate1Y1 varchar(18),
	PostTestScore1Y1 varchar(13),PostTestEducationLevel1Y1 varchar(11),PostTestDate1Y2 varchar(18),PostTestScore1Y2 varchar(13),PostTestEducationLevel1Y2 varchar(11),PostTestDate1Y3 varchar(18),PostTestScore1Y3 varchar(13),
	PostTestEducationLevel1Y3 varchar(11),AssessmentType2 varchar(11),FunctionalArea2 varchar(11),PreTestDate2 varchar(18),PreTestScore2 varchar(13),PreTestEducationLevel2 varchar(11),PostTestDate2Y1 varchar(18),PostTestScore2Y1 varchar(13),
	PostTestEducationLevel2Y1 varchar(11),PostTestDate2Y2 varchar(18),PostTestScore2Y2 varchar(13),PostTestEducationLevel2Y2 varchar(11),PostTestDate2Y3 varchar(18),PostTestScore2Y3 varchar(13),PostTestEducationLevel2Y3 varchar(11),
	AssessmentType3 varchar(11),FunctionalArea3 varchar(11),PreTestDate3 varchar(18),PreTestScore3 varchar(13),PreTestEducationLevel3 varchar(11),PostTestDate3Y1 varchar(18),PostTestScore3Y1 varchar(13),PostTestEducationLevel3Y1 varchar(11),
	PostTestDate3Y2 varchar(18),PostTestScore3Y2 varchar(13),PostTestEducationLevel3Y2 varchar(11),PostTestDate3Y3 varchar(18),PostTestScore3Y3 varchar(13),PostTestEducationLevel3Y3 varchar(11)  NULL)

	SET @strYouthSQL = 'INSERT INTO #tmpYouth SELECT tmp.IndividualIdentifier, tmp.DateOfBirth, tmp.Gender, tmp.DisabilityStatus, tmp.Hispanic, tmp.NativeAmerican, tmp.Asian, tmp.Black, tmp.PacificIslander, tmp.White, tmp.VeteranStatus, tmp.VeteranCampaign, tmp.VeteranDisabled, tmp.VeteranRecentlySeparated, tmp.EmploymentStatus, tmp.LEPStatus, tmp.SingleParentStatus, tmp.UCEligibleStatus, tmp.LowIncomeStatus, tmp.NeedyFamilyStatus, tmp.OtherPublicAssistanceStatus, tmp.HighestGradeCompleted, tmp.DisplaceHomemakerIndicator, tmp.DislocationDate, tmp.HomelessRunawayIndicator, tmp.OffenderIndicator, tmp.PregnantParentIndicator, tmp.AdditionalAssistanceIndicator, tmp.SchoolParticipation, tmp.LiteracySkillsIndicator, tmp.FosterCareIndicator, tmp.ETACode, tmp.ProgramParticipationDate, tmp.ProgramExitDate, tmp.AdultLocal, tmp.DWLocal, tmp.FirstYouthServiceDate, tmp.YouthStatewide15Percent, tmp.DWStatewide15Percent, tmp.IWStatewide15Percent, tmp.AdultStatewide15Percent, tmp.RapidResponse, tmp.AddRapidResponse, tmp.NEGProject1, tmp.NEGProject2, tmp.NEGProject3, tmp.AdultEducation, tmp.JobCorps, tmp.FarmworkerJobs, tmp.IndianPrograms, tmp.VeteranPrograms, tmp.TradeAssistance, tmp.VocationalEducation, tmp.VocationalRehabilitation, tmp.WagnerPeyser, tmp.YouthBuild, tmp.TitleVOlder, tmp.FoodStamps, tmp.OtherNonWIA, tmp.OtherExitReasons, tmp.SupportiveServices, tmp.NeedsRelated, tmp.DisasterRelief, tmp.CoreSelfServices, tmp.FirstCoreServiceDate, tmp.WorkforceInfoService, tmp.FirstIntensiveService, tmp.DateEnterTraining, tmp.DateExitTraining, tmp.ITA, tmp.PellGrant, tmp.PreVocational, tmp.TrainingService1, tmp.TrainingService2, tmp.OTCCode, tmp.Enrolled, tmp.AchievementServices, tmp.EmploymentServices, tmp.SummerEmployment, tmp.AdditionalSupport, tmp.LeadershipDevelopment, tmp.FollowupServices, tmp.ExitEmployed1, tmp.ExitEmployedMatch1, tmp.OccupationCode, tmp.EmploymentTraining, tmp.NonTraditionalEmployment, tmp.ExitEmployed2, tmp.ExitEmployedMatch2, tmp.ExitEmployed3, tmp.ExitEmployedMatch3, tmp.ExitEmployed4, tmp.ExitEmployedMatch4, tmp.PriorWages3, tmp.PriorWages2, tmp.PriorWages1, tmp.ExitWages1, tmp.ExitWages2, tmp.ExitWages3, tmp.ExitWages4, tmp.CredentialType, tmp.GoalType1, tmp.DateGoalSet1, tmp.GoalAttainment1, tmp.DateGoalAttained1, tmp.GoalType2, tmp.DateGoalSet2, tmp.GoalAttainment2, tmp.DateGoalAttained2, tmp.GoalType3, tmp.DateGoalSet3, tmp.GoalAttainment3, tmp.DateGoalAttained3, tmp.GoalType4, tmp.DateGoalSet4, tmp.GoalAttainment4, tmp.DateGoalAttained4, tmp.GoalType5, tmp.DateGoalSet5, tmp.GoalAttainment5, tmp.DateGoalAttained5, tmp.GoalType6, tmp.DateGoalSet6, tmp.GoalAttainment6, tmp.DateGoalAttained6, tmp.GoalType7, tmp.DateGoalSet7, tmp.GoalAttainment7, tmp.DateGoalAttained7, tmp.GoalType8, tmp.DateGoalSet8, tmp.GoalAttainment8, tmp.DateGoalAttained8, tmp.GoalType9, tmp.DateGoalSet9, tmp.GoalAttainment9, tmp.DateGoalAttained9, tmp.GoalType10, tmp.DateGoalSet10, tmp.GoalAttainment10, tmp.DateGoalAttained10, tmp.GoalType11, tmp.DateGoalSet11, tmp.GoalAttainment11, tmp.DateGoalAttained11, tmp.GoalType12, tmp.DateGoalSet12, tmp.GoalAttainment12, tmp.DateGoalAttained12, tmp.DegreeCertificate, tmp.DateDegreeCertificate, tmp.ExitSchoolStatus, tmp.YouthPlacement, tmp.YouthRetention, tmp.AssessmentCategory, tmp.AssessmentType1, tmp.FunctionalArea1, tmp.PreTestDate1, tmp.PreTestScore1, tmp.PreTestEducationLevel1, tmp.PostTestDate1Y1, tmp.PostTestScore1Y1, tmp.PostTestEducationLevel1Y1, tmp.PostTestDate1Y2, tmp.PostTestScore1Y2, tmp.PostTestEducationLevel1Y2, tmp.PostTestDate1Y3, tmp.PostTestScore1Y3, tmp.PostTestEducationLevel1Y3, tmp.AssessmentType2, tmp.FunctionalArea2, tmp.PreTestDate2, tmp.PreTestScore2, tmp.PreTestEducationLevel2, tmp.PostTestDate2Y1, tmp.PostTestScore2Y1, tmp.PostTestEducationLevel2Y1, tmp.PostTestDate2Y2, tmp.PostTestScore2Y2, tmp.PostTestEducationLevel2Y2, tmp.PostTestDate2Y3, tmp.PostTestScore2Y3, tmp.PostTestEducationLevel2Y3, tmp.AssessmentType3, tmp.FunctionalArea3, tmp.PreTestDate3, tmp.PreTestScore3, tmp.PreTestEducationLevel3, tmp.PostTestDate3Y1, tmp.PostTestScore3Y1, tmp.PostTestEducationLevel3Y1, tmp.PostTestDate3Y2, tmp.PostTestScore3Y2, 
		  tmp.PostTestEducationLevel3Y2, tmp.PostTestDate3Y3, tmp.PostTestScore3Y3, tmp.PostTestEducationLevel3Y3
	FROM ' + @cTempDBName + '..tmpFileUpload_WIA tmp
	INNER JOIN Participant P
	ON tmp.RecordID = P.ParticipantID
	AND P.ProgramExitDate BETWEEN (SELECT StDate FROM #tmpDates) AND (SELECT EndDate FROM #tmpDates)
	INNER JOIN ParticipantXFundingStream PxFS
	ON P.ParticipantID = PxFS.ParticipantID
	INNER JOIN Code C
	ON PxFS.FundingStreamTypeID = C.CodeID
	AND C.CodeGroup LIKE ''FundingStream%''
	AND C.CodeAbbrv IN (''Youth'')'


	CREATE TABLE #tmpYY (IndividualIdentifier varchar(19),DateOfBirth varchar(18),Gender varchar(11),DisabilityStatus varchar(11),Hispanic varchar(11),NativeAmerican varchar(11),Asian varchar(11),
	Black varchar(11),PacificIslander varchar(11),White varchar(11),VeteranStatus varchar(11),VeteranCampaign varchar(11),VeteranDisabled varchar(11),VeteranRecentlySeparated varchar(11),EmploymentStatus varchar(11),
	LEPStatus varchar(11),SingleParentStatus varchar(11),UCEligibleStatus varchar(11),LowIncomeStatus varchar(11),NeedyFamilyStatus varchar(11),OtherPublicAssistanceStatus varchar(11),HighestGradeCompleted varchar(12),
	DisplaceHomemakerIndicator varchar(11),DislocationDate varchar(18),HomelessRunawayIndicator varchar(11),OffenderIndicator varchar(11),PregnantParentIndicator varchar(11),AdditionalAssistanceIndicator varchar(11),
	SchoolParticipation varchar(11),LiteracySkillsIndicator varchar(11),FosterCareIndicator varchar(11),ETACode varchar(15),ProgramParticipationDate varchar(18),ProgramExitDate varchar(18),AdultLocal varchar(11),
	DWLocal varchar(11),FirstYouthServiceDate varchar(18),YouthStatewide15Percent varchar(11),DWStatewide15Percent varchar(11),IWStatewide15Percent varchar(11),AdultStatewide15Percent varchar(11),RapidResponse varchar(11),
	AddRapidResponse varchar(11),NEGProject1 varchar(14),NEGProject2 varchar(14),NEGProject3 varchar(14),AdultEducation varchar(11),JobCorps varchar(11),FarmworkerJobs varchar(11),IndianPrograms varchar(11),VeteranPrograms varchar(11),
	TradeAssistance varchar(11),VocationalEducation varchar(11),VocationalRehabilitation varchar(11),WagnerPeyser varchar(11),YouthBuild varchar(11),TitleVOlder varchar(11),FoodStamps varchar(11),OtherNonWIA varchar(11),
	OtherExitReasons varchar(12),SupportiveServices varchar(11),NeedsRelated varchar(11),DisasterRelief varchar(11),CoreSelfServices varchar(11),FirstCoreServiceDate varchar(18),WorkforceInfoService varchar(11),FirstIntensiveService varchar(18),
	DateEnterTraining varchar(18),DateExitTraining varchar(18),ITA varchar(11),PellGrant varchar(11),PreVocational varchar(11),TrainingService1 varchar(11),TrainingService2 varchar(11),OTCCode varchar(18),Enrolled varchar(11),
	AchievementServices varchar(11),EmploymentServices varchar(11),SummerEmployment varchar(11),AdditionalSupport varchar(11),LeadershipDevelopment varchar(11),FollowupServices varchar(11),ExitEmployed1 varchar(11),ExitEmployedMatch1 varchar(11),
	OccupationCode varchar(18),EmploymentTraining varchar(11),NonTraditionalEmployment varchar(11),ExitEmployed2 varchar(11),ExitEmployedMatch2 varchar(11),ExitEmployed3 varchar(11),ExitEmployedMatch3 varchar(11),ExitEmployed4 varchar(11),
	ExitEmployedMatch4 varchar(11),PriorWages3 varchar(19),PriorWages2 varchar(19),PriorWages1 varchar(19),ExitWages1 varchar(19),ExitWages2 varchar(19),ExitWages3 varchar(19),ExitWages4 varchar(19),CredentialType varchar(11),
	GoalType1 varchar(11),DateGoalSet1 varchar(18),GoalAttainment1 varchar(11),DateGoalAttained1 varchar(18),GoalType2 varchar(11),DateGoalSet2 varchar(18),GoalAttainment2 varchar(11),DateGoalAttained2 varchar(18),GoalType3 varchar(11),
	DateGoalSet3 varchar(18),GoalAttainment3 varchar(11),DateGoalAttained3 varchar(18),GoalType4 varchar(11),DateGoalSet4 varchar(18),GoalAttainment4 varchar(11),DateGoalAttained4 varchar(18),GoalType5 varchar(11),DateGoalSet5 varchar(18),
	GoalAttainment5 varchar(11),DateGoalAttained5 varchar(18),GoalType6 varchar(11),DateGoalSet6 varchar(18),GoalAttainment6 varchar(11),DateGoalAttained6 varchar(18),GoalType7 varchar(11),DateGoalSet7 varchar(18),GoalAttainment7 varchar(11),
	DateGoalAttained7 varchar(18),GoalType8 varchar(11),DateGoalSet8 varchar(18),GoalAttainment8 varchar(11),DateGoalAttained8 varchar(18),GoalType9 varchar(11),DateGoalSet9 varchar(18),GoalAttainment9 varchar(11),DateGoalAttained9 varchar(18),
	GoalType10 varchar(11),DateGoalSet10 varchar(18),GoalAttainment10 varchar(11),DateGoalAttained10 varchar(18),GoalType11 varchar(11),DateGoalSet11 varchar(18),GoalAttainment11 varchar(11),DateGoalAttained11 varchar(18),
	GoalType12 varchar(11),DateGoalSet12 varchar(18),GoalAttainment12 varchar(11),DateGoalAttained12 varchar(18),DegreeCertificate varchar(11),DateDegreeCertificate varchar(18),ExitSchoolStatus varchar(11),YouthPlacement varchar(11),
	YouthRetention varchar(11),AssessmentCategory varchar(11),AssessmentType1 varchar(11),FunctionalArea1 varchar(11),PreTestDate1 varchar(18),PreTestScore1 varchar(13),PreTestEducationLevel1 varchar(11),PostTestDate1Y1 varchar(18),
	PostTestScore1Y1 varchar(13),PostTestEducationLevel1Y1 varchar(11),PostTestDate1Y2 varchar(18),PostTestScore1Y2 varchar(13),PostTestEducationLevel1Y2 varchar(11),PostTestDate1Y3 varchar(18),PostTestScore1Y3 varchar(13),
	PostTestEducationLevel1Y3 varchar(11),AssessmentType2 varchar(11),FunctionalArea2 varchar(11),PreTestDate2 varchar(18),PreTestScore2 varchar(13),PreTestEducationLevel2 varchar(11),PostTestDate2Y1 varchar(18),PostTestScore2Y1 varchar(13),
	PostTestEducationLevel2Y1 varchar(11),PostTestDate2Y2 varchar(18),PostTestScore2Y2 varchar(13),PostTestEducationLevel2Y2 varchar(11),PostTestDate2Y3 varchar(18),PostTestScore2Y3 varchar(13),PostTestEducationLevel2Y3 varchar(11),
	AssessmentType3 varchar(11),FunctionalArea3 varchar(11),PreTestDate3 varchar(18),PreTestScore3 varchar(13),PreTestEducationLevel3 varchar(11),PostTestDate3Y1 varchar(18),PostTestScore3Y1 varchar(13),PostTestEducationLevel3Y1 varchar(11),
	PostTestDate3Y2 varchar(18),PostTestScore3Y2 varchar(13),PostTestEducationLevel3Y2 varchar(11),PostTestDate3Y3 varchar(18),PostTestScore3Y3 varchar(13),PostTestEducationLevel3Y3 varchar(11)  NULL)

	SET @strYYSQL = 'INSERT INTO #tmpYY SELECT distinct tmp.IndividualIdentifier, tmp.DateOfBirth, tmp.Gender, tmp.DisabilityStatus, tmp.Hispanic, tmp.NativeAmerican, tmp.Asian, tmp.Black, tmp.PacificIslander, tmp.White, tmp.VeteranStatus, tmp.VeteranCampaign, tmp.VeteranDisabled, tmp.VeteranRecentlySeparated, tmp.EmploymentStatus, tmp.LEPStatus, tmp.SingleParentStatus, tmp.UCEligibleStatus, tmp.LowIncomeStatus, tmp.NeedyFamilyStatus, tmp.OtherPublicAssistanceStatus, tmp.HighestGradeCompleted, tmp.DisplaceHomemakerIndicator, tmp.DislocationDate, tmp.HomelessRunawayIndicator, tmp.OffenderIndicator, tmp.PregnantParentIndicator, tmp.AdditionalAssistanceIndicator, tmp.SchoolParticipation, tmp.LiteracySkillsIndicator, tmp.FosterCareIndicator, tmp.ETACode, tmp.ProgramParticipationDate, tmp.ProgramExitDate, tmp.AdultLocal, tmp.DWLocal, tmp.FirstYouthServiceDate, tmp.YouthStatewide15Percent, tmp.DWStatewide15Percent, tmp.IWStatewide15Percent, tmp.AdultStatewide15Percent, tmp.RapidResponse, tmp.AddRapidResponse, tmp.NEGProject1, tmp.NEGProject2, tmp.NEGProject3, tmp.AdultEducation, tmp.JobCorps, tmp.FarmworkerJobs, tmp.IndianPrograms, tmp.VeteranPrograms, tmp.TradeAssistance, tmp.VocationalEducation, tmp.VocationalRehabilitation, tmp.WagnerPeyser, tmp.YouthBuild, tmp.TitleVOlder, tmp.FoodStamps, tmp.OtherNonWIA, tmp.OtherExitReasons, tmp.SupportiveServices, tmp.NeedsRelated, tmp.DisasterRelief, tmp.CoreSelfServices, tmp.FirstCoreServiceDate, tmp.WorkforceInfoService, tmp.FirstIntensiveService, tmp.DateEnterTraining, tmp.DateExitTraining, tmp.ITA, tmp.PellGrant, tmp.PreVocational, tmp.TrainingService1, tmp.TrainingService2, tmp.OTCCode, tmp.Enrolled, tmp.AchievementServices, tmp.EmploymentServices, tmp.SummerEmployment, tmp.AdditionalSupport, tmp.LeadershipDevelopment, tmp.FollowupServices, tmp.ExitEmployed1, tmp.ExitEmployedMatch1, tmp.OccupationCode, tmp.EmploymentTraining, tmp.NonTraditionalEmployment, tmp.ExitEmployed2, tmp.ExitEmployedMatch2, tmp.ExitEmployed3, tmp.ExitEmployedMatch3, tmp.ExitEmployed4, tmp.ExitEmployedMatch4, tmp.PriorWages3, tmp.PriorWages2, tmp.PriorWages1, tmp.ExitWages1, tmp.ExitWages2, tmp.ExitWages3, tmp.ExitWages4, tmp.CredentialType, tmp.GoalType1, tmp.DateGoalSet1, tmp.GoalAttainment1, tmp.DateGoalAttained1, tmp.GoalType2, tmp.DateGoalSet2, tmp.GoalAttainment2, tmp.DateGoalAttained2, tmp.GoalType3, tmp.DateGoalSet3, tmp.GoalAttainment3, tmp.DateGoalAttained3, tmp.GoalType4, tmp.DateGoalSet4, tmp.GoalAttainment4, tmp.DateGoalAttained4, tmp.GoalType5, tmp.DateGoalSet5, tmp.GoalAttainment5, tmp.DateGoalAttained5, tmp.GoalType6, tmp.DateGoalSet6, tmp.GoalAttainment6, tmp.DateGoalAttained6, tmp.GoalType7, tmp.DateGoalSet7, tmp.GoalAttainment7, tmp.DateGoalAttained7, tmp.GoalType8, tmp.DateGoalSet8, tmp.GoalAttainment8, tmp.DateGoalAttained8, tmp.GoalType9, tmp.DateGoalSet9, tmp.GoalAttainment9, tmp.DateGoalAttained9, tmp.GoalType10, tmp.DateGoalSet10, tmp.GoalAttainment10, tmp.DateGoalAttained10, tmp.GoalType11, tmp.DateGoalSet11, tmp.GoalAttainment11, tmp.DateGoalAttained11, tmp.GoalType12, tmp.DateGoalSet12, tmp.GoalAttainment12, tmp.DateGoalAttained12, tmp.DegreeCertificate, tmp.DateDegreeCertificate, tmp.ExitSchoolStatus, tmp.YouthPlacement, tmp.YouthRetention, tmp.AssessmentCategory, tmp.AssessmentType1, tmp.FunctionalArea1, tmp.PreTestDate1, tmp.PreTestScore1, tmp.PreTestEducationLevel1, tmp.PostTestDate1Y1, tmp.PostTestScore1Y1, tmp.PostTestEducationLevel1Y1, tmp.PostTestDate1Y2, tmp.PostTestScore1Y2, tmp.PostTestEducationLevel1Y2, tmp.PostTestDate1Y3, tmp.PostTestScore1Y3, tmp.PostTestEducationLevel1Y3, tmp.AssessmentType2, tmp.FunctionalArea2, tmp.PreTestDate2, tmp.PreTestScore2, tmp.PreTestEducationLevel2, tmp.PostTestDate2Y1, tmp.PostTestScore2Y1, tmp.PostTestEducationLevel2Y1, tmp.PostTestDate2Y2, tmp.PostTestScore2Y2, tmp.PostTestEducationLevel2Y2, tmp.PostTestDate2Y3, tmp.PostTestScore2Y3, tmp.PostTestEducationLevel2Y3, tmp.AssessmentType3, tmp.FunctionalArea3, tmp.PreTestDate3, tmp.PreTestScore3, tmp.PreTestEducationLevel3, tmp.PostTestDate3Y1, tmp.PostTestScore3Y1, tmp.PostTestEducationLevel3Y1, tmp.PostTestDate3Y2, tmp.PostTestScore3Y2, 
		  tmp.PostTestEducationLevel3Y2, tmp.PostTestDate3Y3, tmp.PostTestScore3Y3, tmp.PostTestEducationLevel3Y3
	FROM ' + @cTempDBName + '..tmpFileUpload_WIA tmp
	INNER JOIN Participant P
	ON tmp.RecordID = P.ParticipantID
	AND P.ProgramExitDate IS NULL
	INNER JOIN ParticipantXFundingStream PxFS
	ON P.ParticipantID = PxFS.ParticipantID
	INNER JOIN ParticipantXMetricCount PxMC
	ON P.ParticipantID = PxMC.ParticipantID
	INNER JOIN MetricCount MC
	ON PxMC.MetricCountID = MC.MetricCountID
	INNER JOIN ProgramYearRange PYR
	ON PYR.ProgramYearRangeID = MC.ProgramYearRangeID
	where MC.MetricTypeID in (39,40,168,169,4075,4076)'
/*

	INNER JOIN Code C
	ON PxFS.ServiceTypeID = C.CodeID
	AND C.CodeGroup LIKE ''ServiceType%''
	AND C.CodeAbbrv IN (''YOUNGYTH'')

	INNER JOIN UserReportingOptions URO
	ON URO.ProgramYearID = PYR.ProgramYearID
	AND URO.ReportTypeID = PYR.RangeTypeID
	INNER JOIN RVNameLookUp RVN
	ON RVN.MetricTypeID = MC.MetricTypeID
	AND (RVN.Description LIKE ''skill%''
	AND RVN.Description NOT LIKE ''%Negotiated%'')
	OR  (RVN.Description LIKE ''Lit%''
	AND RVN.Description NOT LIKE ''%Negotiated%'')'
*/

	CREATE TABLE #tmpOY (IndividualIdentifier varchar(19),DateOfBirth varchar(18),Gender varchar(11),DisabilityStatus varchar(11),Hispanic varchar(11),NativeAmerican varchar(11),Asian varchar(11),
	Black varchar(11),PacificIslander varchar(11),White varchar(11),VeteranStatus varchar(11),VeteranCampaign varchar(11),VeteranDisabled varchar(11),VeteranRecentlySeparated varchar(11),EmploymentStatus varchar(11),
	LEPStatus varchar(11),SingleParentStatus varchar(11),UCEligibleStatus varchar(11),LowIncomeStatus varchar(11),NeedyFamilyStatus varchar(11),OtherPublicAssistanceStatus varchar(11),HighestGradeCompleted varchar(12),
	DisplaceHomemakerIndicator varchar(11),DislocationDate varchar(18),HomelessRunawayIndicator varchar(11),OffenderIndicator varchar(11),PregnantParentIndicator varchar(11),AdditionalAssistanceIndicator varchar(11),
	SchoolParticipation varchar(11),LiteracySkillsIndicator varchar(11),FosterCareIndicator varchar(11),ETACode varchar(15),ProgramParticipationDate varchar(18),ProgramExitDate varchar(18),AdultLocal varchar(11),
	DWLocal varchar(11),FirstYouthServiceDate varchar(18),YouthStatewide15Percent varchar(11),DWStatewide15Percent varchar(11),IWStatewide15Percent varchar(11),AdultStatewide15Percent varchar(11),RapidResponse varchar(11),
	AddRapidResponse varchar(11),NEGProject1 varchar(14),NEGProject2 varchar(14),NEGProject3 varchar(14),AdultEducation varchar(11),JobCorps varchar(11),FarmworkerJobs varchar(11),IndianPrograms varchar(11),VeteranPrograms varchar(11),
	TradeAssistance varchar(11),VocationalEducation varchar(11),VocationalRehabilitation varchar(11),WagnerPeyser varchar(11),YouthBuild varchar(11),TitleVOlder varchar(11),FoodStamps varchar(11),OtherNonWIA varchar(11),
	OtherExitReasons varchar(12),SupportiveServices varchar(11),NeedsRelated varchar(11),DisasterRelief varchar(11),CoreSelfServices varchar(11),FirstCoreServiceDate varchar(18),WorkforceInfoService varchar(11),FirstIntensiveService varchar(18),
	DateEnterTraining varchar(18),DateExitTraining varchar(18),ITA varchar(11),PellGrant varchar(11),PreVocational varchar(11),TrainingService1 varchar(11),TrainingService2 varchar(11),OTCCode varchar(18),Enrolled varchar(11),
	AchievementServices varchar(11),EmploymentServices varchar(11),SummerEmployment varchar(11),AdditionalSupport varchar(11),LeadershipDevelopment varchar(11),FollowupServices varchar(11),ExitEmployed1 varchar(11),ExitEmployedMatch1 varchar(11),
	OccupationCode varchar(18),EmploymentTraining varchar(11),NonTraditionalEmployment varchar(11),ExitEmployed2 varchar(11),ExitEmployedMatch2 varchar(11),ExitEmployed3 varchar(11),ExitEmployedMatch3 varchar(11),ExitEmployed4 varchar(11),
	ExitEmployedMatch4 varchar(11),PriorWages3 varchar(19),PriorWages2 varchar(19),PriorWages1 varchar(19),ExitWages1 varchar(19),ExitWages2 varchar(19),ExitWages3 varchar(19),ExitWages4 varchar(19),CredentialType varchar(11),
	GoalType1 varchar(11),DateGoalSet1 varchar(18),GoalAttainment1 varchar(11),DateGoalAttained1 varchar(18),GoalType2 varchar(11),DateGoalSet2 varchar(18),GoalAttainment2 varchar(11),DateGoalAttained2 varchar(18),GoalType3 varchar(11),
	DateGoalSet3 varchar(18),GoalAttainment3 varchar(11),DateGoalAttained3 varchar(18),GoalType4 varchar(11),DateGoalSet4 varchar(18),GoalAttainment4 varchar(11),DateGoalAttained4 varchar(18),GoalType5 varchar(11),DateGoalSet5 varchar(18),
	GoalAttainment5 varchar(11),DateGoalAttained5 varchar(18),GoalType6 varchar(11),DateGoalSet6 varchar(18),GoalAttainment6 varchar(11),DateGoalAttained6 varchar(18),GoalType7 varchar(11),DateGoalSet7 varchar(18),GoalAttainment7 varchar(11),
	DateGoalAttained7 varchar(18),GoalType8 varchar(11),DateGoalSet8 varchar(18),GoalAttainment8 varchar(11),DateGoalAttained8 varchar(18),GoalType9 varchar(11),DateGoalSet9 varchar(18),GoalAttainment9 varchar(11),DateGoalAttained9 varchar(18),
	GoalType10 varchar(11),DateGoalSet10 varchar(18),GoalAttainment10 varchar(11),DateGoalAttained10 varchar(18),GoalType11 varchar(11),DateGoalSet11 varchar(18),GoalAttainment11 varchar(11),DateGoalAttained11 varchar(18),
	GoalType12 varchar(11),DateGoalSet12 varchar(18),GoalAttainment12 varchar(11),DateGoalAttained12 varchar(18),DegreeCertificate varchar(11),DateDegreeCertificate varchar(18),ExitSchoolStatus varchar(11),YouthPlacement varchar(11),
	YouthRetention varchar(11),AssessmentCategory varchar(11),AssessmentType1 varchar(11),FunctionalArea1 varchar(11),PreTestDate1 varchar(18),PreTestScore1 varchar(13),PreTestEducationLevel1 varchar(11),PostTestDate1Y1 varchar(18),
	PostTestScore1Y1 varchar(13),PostTestEducationLevel1Y1 varchar(11),PostTestDate1Y2 varchar(18),PostTestScore1Y2 varchar(13),PostTestEducationLevel1Y2 varchar(11),PostTestDate1Y3 varchar(18),PostTestScore1Y3 varchar(13),
	PostTestEducationLevel1Y3 varchar(11),AssessmentType2 varchar(11),FunctionalArea2 varchar(11),PreTestDate2 varchar(18),PreTestScore2 varchar(13),PreTestEducationLevel2 varchar(11),PostTestDate2Y1 varchar(18),PostTestScore2Y1 varchar(13),
	PostTestEducationLevel2Y1 varchar(11),PostTestDate2Y2 varchar(18),PostTestScore2Y2 varchar(13),PostTestEducationLevel2Y2 varchar(11),PostTestDate2Y3 varchar(18),PostTestScore2Y3 varchar(13),PostTestEducationLevel2Y3 varchar(11),
	AssessmentType3 varchar(11),FunctionalArea3 varchar(11),PreTestDate3 varchar(18),PreTestScore3 varchar(13),PreTestEducationLevel3 varchar(11),PostTestDate3Y1 varchar(18),PostTestScore3Y1 varchar(13),PostTestEducationLevel3Y1 varchar(11),
	PostTestDate3Y2 varchar(18),PostTestScore3Y2 varchar(13),PostTestEducationLevel3Y2 varchar(11),PostTestDate3Y3 varchar(18),PostTestScore3Y3 varchar(13),PostTestEducationLevel3Y3 varchar(11)  NULL)

	SET @strOYSQL = 'INSERT INTO #tmpOY SELECT distinct tmp.IndividualIdentifier, tmp.DateOfBirth, tmp.Gender, tmp.DisabilityStatus, tmp.Hispanic, tmp.NativeAmerican, tmp.Asian, tmp.Black, tmp.PacificIslander, tmp.White, tmp.VeteranStatus, tmp.VeteranCampaign, tmp.VeteranDisabled, tmp.VeteranRecentlySeparated, tmp.EmploymentStatus, tmp.LEPStatus, tmp.SingleParentStatus, tmp.UCEligibleStatus, tmp.LowIncomeStatus, tmp.NeedyFamilyStatus, tmp.OtherPublicAssistanceStatus, tmp.HighestGradeCompleted, tmp.DisplaceHomemakerIndicator, tmp.DislocationDate, tmp.HomelessRunawayIndicator, tmp.OffenderIndicator, tmp.PregnantParentIndicator, tmp.AdditionalAssistanceIndicator, tmp.SchoolParticipation, tmp.LiteracySkillsIndicator, tmp.FosterCareIndicator, tmp.ETACode, tmp.ProgramParticipationDate, tmp.ProgramExitDate, tmp.AdultLocal, tmp.DWLocal, tmp.FirstYouthServiceDate, tmp.YouthStatewide15Percent, tmp.DWStatewide15Percent, tmp.IWStatewide15Percent, tmp.AdultStatewide15Percent, tmp.RapidResponse, tmp.AddRapidResponse, tmp.NEGProject1, tmp.NEGProject2, tmp.NEGProject3, tmp.AdultEducation, tmp.JobCorps, tmp.FarmworkerJobs, tmp.IndianPrograms, tmp.VeteranPrograms, tmp.TradeAssistance, tmp.VocationalEducation, tmp.VocationalRehabilitation, tmp.WagnerPeyser, tmp.YouthBuild, tmp.TitleVOlder, tmp.FoodStamps, tmp.OtherNonWIA, tmp.OtherExitReasons, tmp.SupportiveServices, tmp.NeedsRelated, tmp.DisasterRelief, tmp.CoreSelfServices, tmp.FirstCoreServiceDate, tmp.WorkforceInfoService, tmp.FirstIntensiveService, tmp.DateEnterTraining, tmp.DateExitTraining, tmp.ITA, tmp.PellGrant, tmp.PreVocational, tmp.TrainingService1, tmp.TrainingService2, tmp.OTCCode, tmp.Enrolled, tmp.AchievementServices, tmp.EmploymentServices, tmp.SummerEmployment, tmp.AdditionalSupport, tmp.LeadershipDevelopment, tmp.FollowupServices, tmp.ExitEmployed1, tmp.ExitEmployedMatch1, tmp.OccupationCode, tmp.EmploymentTraining, tmp.NonTraditionalEmployment, tmp.ExitEmployed2, tmp.ExitEmployedMatch2, tmp.ExitEmployed3, tmp.ExitEmployedMatch3, tmp.ExitEmployed4, tmp.ExitEmployedMatch4, tmp.PriorWages3, tmp.PriorWages2, tmp.PriorWages1, tmp.ExitWages1, tmp.ExitWages2, tmp.ExitWages3, tmp.ExitWages4, tmp.CredentialType, tmp.GoalType1, tmp.DateGoalSet1, tmp.GoalAttainment1, tmp.DateGoalAttained1, tmp.GoalType2, tmp.DateGoalSet2, tmp.GoalAttainment2, tmp.DateGoalAttained2, tmp.GoalType3, tmp.DateGoalSet3, tmp.GoalAttainment3, tmp.DateGoalAttained3, tmp.GoalType4, tmp.DateGoalSet4, tmp.GoalAttainment4, tmp.DateGoalAttained4, tmp.GoalType5, tmp.DateGoalSet5, tmp.GoalAttainment5, tmp.DateGoalAttained5, tmp.GoalType6, tmp.DateGoalSet6, tmp.GoalAttainment6, tmp.DateGoalAttained6, tmp.GoalType7, tmp.DateGoalSet7, tmp.GoalAttainment7, tmp.DateGoalAttained7, tmp.GoalType8, tmp.DateGoalSet8, tmp.GoalAttainment8, tmp.DateGoalAttained8, tmp.GoalType9, tmp.DateGoalSet9, tmp.GoalAttainment9, tmp.DateGoalAttained9, tmp.GoalType10, tmp.DateGoalSet10, tmp.GoalAttainment10, tmp.DateGoalAttained10, tmp.GoalType11, tmp.DateGoalSet11, tmp.GoalAttainment11, tmp.DateGoalAttained11, tmp.GoalType12, tmp.DateGoalSet12, tmp.GoalAttainment12, tmp.DateGoalAttained12, tmp.DegreeCertificate, tmp.DateDegreeCertificate, tmp.ExitSchoolStatus, tmp.YouthPlacement, tmp.YouthRetention, tmp.AssessmentCategory, tmp.AssessmentType1, tmp.FunctionalArea1, tmp.PreTestDate1, tmp.PreTestScore1, tmp.PreTestEducationLevel1, tmp.PostTestDate1Y1, tmp.PostTestScore1Y1, tmp.PostTestEducationLevel1Y1, tmp.PostTestDate1Y2, tmp.PostTestScore1Y2, tmp.PostTestEducationLevel1Y2, tmp.PostTestDate1Y3, tmp.PostTestScore1Y3, tmp.PostTestEducationLevel1Y3, tmp.AssessmentType2, tmp.FunctionalArea2, tmp.PreTestDate2, tmp.PreTestScore2, tmp.PreTestEducationLevel2, tmp.PostTestDate2Y1, tmp.PostTestScore2Y1, tmp.PostTestEducationLevel2Y1, tmp.PostTestDate2Y2, tmp.PostTestScore2Y2, tmp.PostTestEducationLevel2Y2, tmp.PostTestDate2Y3, tmp.PostTestScore2Y3, tmp.PostTestEducationLevel2Y3, tmp.AssessmentType3, tmp.FunctionalArea3, tmp.PreTestDate3, tmp.PreTestScore3, tmp.PreTestEducationLevel3, tmp.PostTestDate3Y1, tmp.PostTestScore3Y1, tmp.PostTestEducationLevel3Y1, tmp.PostTestDate3Y2, tmp.PostTestScore3Y2, 
		  tmp.PostTestEducationLevel3Y2, tmp.PostTestDate3Y3, tmp.PostTestScore3Y3, tmp.PostTestEducationLevel3Y3
	FROM ' + @cTempDBName + '..tmpFileUpload_WIA tmp
	INNER JOIN Participant P
	ON tmp.RecordID = P.ParticipantID
	AND P.ProgramExitDate IS NULL
	INNER JOIN ParticipantXFundingStream PxFS
	ON P.ParticipantID = PxFS.ParticipantID
	INNER JOIN Code C
	ON PxFS.ServiceTypeID = C.CodeID
	AND C.CodeGroup LIKE ''ServiceType%''
	AND C.CodeAbbrv IN (''OLDYTH'')
	INNER JOIN ParticipantXMetricCount PxMC
	ON P.ParticipantID = PxMC.ParticipantID
	INNER JOIN MetricCount MC
	ON PxMC.MetricCountID = MC.MetricCountID
	INNER JOIN RVNameLookUp RVN
	ON RVN.MetricTypeID = MC.MetricTypeID
	AND (RVN.Description LIKE ''Lit%''
	AND RVN.Description NOT LIKE ''%Negotiated%'')'

	EXEC (@strAdultSQL)

	EXEC (@strNEGSQL)

	EXEC (@strYouthSQL)

	EXEC (@strYYSQL)

	EXEC (@strOYSQL)

	SELECT * FROM #tmpAdults
	UNION ALL
	SELECT * FROM #tmpNEG
	UNION ALL
	SELECT * FROM #tmpYouth
	UNION ALL
	SELECT * FROM #tmpYY
	where not exists (select 'c' from #tmpYouth tY where #tmpYY.IndividualIdentifier = tY.IndividualIdentifier)
	UNION ALL
	SELECT * FROM #tmpOY
	where not exists (select 'c' from #tmpYouth tY where #tmpOY.IndividualIdentifier = tY.IndividualIdentifier)
        and not exists (select 'c' from #tmpYY tY where #tmpOY.IndividualIdentifier = tY.IndividualIdentifier)

End

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO