HATCHERY HLI VIEWS AS OF 2025-08-22. From BroodstockSpawning table VIEW name: XPortCA_BroodstockSpawning SELECT TOP (100) PERCENT hp.HatcheryProgramName ,bs.HatcheryFacilityName ,h.SiteLat ,h.SiteLong ,bs.PrimaryOperator ,bs.TimeSeriesID ,bs.CommonName ,bs.Run ,bs.StockID ,hs.HatcheryStockName ,hs.AgencyStockCode ,hs.AgencyStockName ,bs.SpawningLocation ,bs.BroodYear ,a.Acronym AS ContactAgy ,bs.ContactAgency ,CASE WHEN bs.NullRecord = 'Yes' THEN NULL ELSE ISNULL(bs.BroodFemales, 0) + ISNULL(bs.BroodMales, 0) + ISNULL(bs.BroodJacks, 0) END AS BroodTotal ,bs.BroodFemales ,bs.BroodMales ,bs.BroodJacks ,bs.HOBFemales ,bs.HOBMales ,bs.HOBJacks ,bs.NOBFemales ,bs.NOBMales ,bs.NOBJacks ,bs.pHOBIJ ,bs.pHOBEJ ,bs.pNOBIJ ,bs.pNOBEJ ,bs.ProtMethName ,bs.ProtMethURL ,bs.ProtMethDocumentation ,bs.MethodAdjustments ,bs.OtherDataSources ,bs.BPAprojNum ,bs.Comments ,bs.NullRecord ,bs.DataStatus ,bs.IndicatorLocation ,bs.MeasureLocation ,bs.ContactPersonFirst ,bs.ContactPersonLast ,bs.ContactPhone ,bs.ContactEmail ,bs.MetaComments ,bs.SubmitAgency ,bs.RefID ,r.Citation ,r.URL AS CitationURL ,bs.CompilerRecordID ,bs.ID ,CAST(FORMAT(GETDATE(), 'yyyy/MM/dd hh:mm') AS VARCHAR(20)) AS DataSetVersion ,CAST(FORMAT(bs.UpdDate, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS UpdDate ,CAST(FORMAT(bs.SNLoadDate, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS SNLoadDate ,CAST(FORMAT(bs.HLI_LastUpdated, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS HLI_LastUpdated ,CAST(FORMAT(bs.LastUpdated, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS LastUpdated ,CAST(FORMAT(bs.LastModifiedOn, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS LastModifiedOn FROM BroodstockSpawning bs INNER JOIN HatcheryProgram hp ON hp.HatcheryProgramID = bs.HatcheryProgramID INNER JOIN HatcheryStock hs ON hs.StockID = bs.StockID INNER JOIN Agency a ON a.Agency = bs.ContactAgency LEFT OUTER JOIN Hatchery h ON h.Hatch_Name = bs.HatcheryFacilityName LEFT OUTER JOIN Reference r ON r.RefID = bs.RefID WHERE (bs.Publish = 'Yes') ORDER BY hp.HatcheryProgramName ,bs.HatcheryFacilityName ,hs.HatcheryStockName ,bs.TimeSeriesID ,bs.BroodYear; From HatcheryReleases table VIEW name: XPortCA_HatcheryReleases SELECT TOP (100) PERCENT hp.HatcheryProgramName ,hr.HatcheryFacilityName ,h.SiteLat ,h.SiteLong ,hr.PrimaryOperator ,hr.TimeSeriesID ,hr.CommonName ,hr.Run ,hr.StockID ,hs.HatcheryStockName ,hs.AgencyStockCode ,hs.AgencyStockName ,p.PopulationName AS AffiliatedPopulation ,hr.AffiliatedPopID ,hr.ReleaseRegion ,hr.ReleaseHUC8 ,hr.ReleaseLocation ,hr.ReleaseLong ,hr.ReleaseLat ,hr.BroodYear ,hr.ReleaseYear ,hr.ReleaseSeason ,hr.ReleaseStartDate ,hr.ReleaseEndDate ,hr.ReleaseStrategy ,hr.LifeStage ,a.Acronym AS ContactAgy ,hr.ContactAgency ,hr.NumberReleased ,hr.AvgLength ,hr.LengthType ,hr.AvgWeight ,hr.FishPerPound ,hr.ProtMethName ,hr.ProtMethURL ,hr.ProtMethDocumentation ,hr.MethodAdjustments ,hr.OtherDataSources ,hr.BPAprojNum ,hr.Comments ,hr.NullRecord ,hr.DataStatus ,hr.IndicatorLocation ,hr.MeasureLocation ,hr.ContactPersonFirst ,hr.ContactPersonLast ,hr.ContactPhone ,hr.ContactEmail ,hr.MetaComments ,hr.SubmitAgency ,hr.RefID ,r.Citation ,r.URL AS CitationURL ,hr.CompilerRecordID ,hr.ID ,CAST(FORMAT(GETDATE(), 'yyyy/MM/dd hh:mm') AS VARCHAR(20)) AS DataSetVersion ,CAST(FORMAT(hr.UpdDate, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS UpdDate ,CAST(FORMAT(hr.SNLoadDate, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS SNLoadDate ,CAST(FORMAT(hr.HLI_LastUpdated, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS HLI_LastUpdated ,CAST(FORMAT(hr.LastUpdated, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS LastUpdated ,CAST(FORMAT(hr.LastModifiedOn, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS LastModifiedOn FROM HatcheryReleases hr INNER JOIN HatcheryProgram hp ON hp.HatcheryProgramID = hr.HatcheryProgramID INNER JOIN HatcheryStock hs ON hs.StockID = hr.StockID INNER JOIN Agency a ON a.Agency = hr.ContactAgency LEFT OUTER JOIN Populations p ON p.ID = hr.AffiliatedPopID LEFT OUTER JOIN Hatchery h ON h.Hatch_Name = hr.HatcheryFacilityName LEFT OUTER JOIN Reference r ON r.RefID = hr.RefID WHERE (hr.Publish = 'Yes') ORDER BY hp.HatcheryProgramName ,hr.HatcheryFacilityName ,hs.HatcheryStockName ,hr.TimeSeriesID ,hr.BroodYear ,hr.ReleaseYear ,hr.ReleaseSeason; From HatcheryReturns table VIEW name: XPortCA_HatcheryReturns SELECT TOP (100) PERCENT hp.HatcheryProgramName ,hr.HatcheryFacilityName ,h.SiteLat ,h.SiteLong ,hr.PrimaryOperator ,hr.TimeSeriesID ,hr.CommonName ,hr.Run ,hr.StockID ,hs.HatcheryStockName ,hs.AgencyStockCode ,hs.AgencyStockName ,p.PopulationName AS AffiliatedPopulation ,hr.ReturnLocation ,hr.ReturnLong ,hr.ReturnLat ,hr.ReturnYear ,a.Acronym AS ContactAgy ,hr.ContactAgency ,hr.TotalReturn ,hr.HatcheryTotal ,hr.StrayTotal ,hr.UnmarkedTotal ,hr.NaturalTotal ,hr.HatcheryFemales ,hr.HatcheryMales ,hr.HatcheryJacks ,hr.HatcheryJennies ,hr.HatcheryUnknown ,hr.StrayFemales ,hr.StrayMales ,hr.StrayJacks ,hr.StrayJennies ,hr.StrayUnknown ,hr.UnmarkedFemales ,hr.UnmarkedMales ,hr.UnmarkedJacks ,hr.UnmarkedJennies ,hr.UnmarkedUnknown ,hr.ProtMethName ,hr.ProtMethURL ,hr.ProtMethDocumentation ,hr.MethodAdjustments ,hr.OtherDataSources ,hr.BPAprojNum ,hr.Comments ,hr.NullRecord ,hr.DataStatus ,hr.IndicatorLocation ,hr.MeasureLocation ,hr.ContactPersonFirst ,hr.ContactPersonLast ,hr.ContactPhone ,hr.ContactEmail ,hr.MetaComments ,hr.SubmitAgency ,hr.RefID ,r.Citation ,r.URL AS CitationURL ,hr.CompilerRecordID ,hr.ID ,CAST(FORMAT(GETDATE(), 'yyyy/MM/dd hh:mm') AS VARCHAR(20)) AS DataSetVersion ,CAST(FORMAT(hr.UpdDate, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS UpdDate ,CAST(FORMAT(hr.SNLoadDate, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS SNLoadDate ,CAST(FORMAT(hr.HLI_LastUpdated, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS HLI_LastUpdated ,CAST(FORMAT(hr.LastUpdated, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS LastUpdated ,CAST(FORMAT(hr.LastModifiedOn, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS LastModifiedOn FROM HatcheryReturns hr INNER JOIN HatcheryProgram hp ON hp.HatcheryProgramID = hr.HatcheryProgramID INNER JOIN HatcheryStock hs ON hs.StockID = hr.StockID INNER JOIN Agency a ON a.Agency = hr.ContactAgency LEFT OUTER JOIN Populations p ON p.ID = hr.AffiliatedPopID LEFT OUTER JOIN Hatchery h ON h.Hatch_Name = hr.HatcheryFacilityName LEFT OUTER JOIN Reference r ON r.RefID = hr.RefID WHERE (hr.Publish = 'Yes') ORDER BY hp.HatcheryProgramName ,hr.HatcheryFacilityName ,hs.HatcheryStockName ,hr.TimeSeriesID ,hr.ReturnYear; From SARHatchery table VIEW name: XPortCA_SARHatchery (It looks like this one needs a few modifications to match the others.) SELECT TOP (100) PERCENT hp.HatcheryProgramName AS HatcheryProgram ,SARH.HatcheryFacilityName AS HatcheryFacility ,SARH.PrimaryOperator ,SARH.TimeSeriesID ,SARH.CommonName ,SARH.Run ,SARH.ReleaseRegion ,SARH.StockID ,HatcheryStock.HatcheryStockName AS Stock ,SARH.ReleaseLocation ,SARH.ReleaseHUC8 ,SARH.ReleaseLong ,SARH.ReleaseLat ,SARH.ReleaseDef ,SARH.ReleaseLocPTcode ,SARH.ReturnLocation ,SARH.ReturnLong ,SARH.ReturnLat ,'From ' + SARH.ReleaseLocation + ' to ' + SARH.ReturnLocation AS SmoltToAdultLocs ,SARH.ReturnDef ,SARH.SARtype ,SARH.BroodYear ,SARH.ReleaseYear ,SARH.ReleaseSeason ,SARH.OutmigrationYear ,a.Acronym AS ContactAgy ,SARH.ContactAgency ,SARH.SAR ,SARH.StraysRemoved ,SARH.ReturnsMissing ,SARH.ReturnsMissingExplanation ,SARH.NumberReleased ,SARH.AvgLength ,SARH.LengthType ,SARH.AvgWeight ,SARH.FishPerPound ,SARH.TAR ,SARH.ProtMethName ,SARH.ProtMethURL ,SARH.ProtMethDocumentation ,SARH.MethodAdjustments ,SARH.OtherDataSources ,SARH.BPAprojNum ,SARH.Comments ,SARH.NullRecord ,SARH.DataStatus ,SARH.IndicatorLocation ,SARH.MetricLocation ,SARH.MeasureLocation ,SARH.ContactPersonFirst ,SARH.ContactPersonLast ,SARH.ContactPhone ,SARH.ContactEmail ,SARH.MetaComments ,SARH.SubmitAgency ,SARH.RefID ,r.Citation ,r.URL AS CitationURL ,SARH.CompilerRecordID ,SARH.ID ,CAST(FORMAT(GETDATE(), 'yyyy/MM/dd hh:mm') AS VARCHAR(20)) AS DataSetVersion ,CAST(FORMAT(SARH.UpdDate, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS UpdDate ,CAST(FORMAT(SARH.SNLoadDate, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS SNLoadDate ,CAST(FORMAT(SARH.HLI_LastUpdated, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS HLI_LastUpdated ,CAST(FORMAT(SARH.LastUpdated, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS LastUpdated ,CAST(FORMAT(SARH.LastModifiedOn, 'yyyy/MM/dd hh:mm:ss') AS VARCHAR(20)) AS LastModifiedOn FROM SARHatchery SARH INNER JOIN HatcheryProgram hp ON hp.HatcheryProgramID = SARH.HatcheryProgramID INNER JOIN HatcheryStock ON HatcheryStock.StockID = SARH.StockID INNER JOIN Agency a ON a.Agency = SARH.ContactAgency LEFT OUTER JOIN Reference r ON r.RefID = SARH.RefID WHERE (SARH.Publish = 'Yes') ORDER BY HatcheryProgram ,HatcheryFacility ,Stock ,SARH.OutmigrationYear ,SARH.BroodYear ,SARH.ReleaseYear ,SARH.ReleaseSeason; From HatcheryProgram table VIEW name: View_HCAX_HatcheryProgramsInfo (It looks like this one needs a few modifications to match the others.) (This view's name will be changed.) SELECT TOP (100) PERCENT HatcheryProgram.ID ,HatcheryProgram.HatcheryProgramID ,HatcheryProgram.HatcheryProgramName ,HatcheryProgram.HatcheryFacilityName ,Hatchery.SiteLat AS HatcheryLat ,Hatchery.SiteLong AS HatcheryLong ,Hatchery.HatchType AS HatcheryType ,Hatchery.AgencyID AS AgencyHatchID ,Hatchery.InitYear AS HatcheryInitYear ,Hatchery.LastYear AS HatcheryLastYear ,HatcheryProgram.StockID ,HatcheryStock.HatcheryStockName ,HatcheryStock.AgencyStockName ,HatcheryStock.AgencyStockCode ,HatcheryStock.Comments AS StockComments ,HatcheryProgram.CommonName ,HatcheryProgram.Run ,HatcheryProgram.HatcheryProgramType ,HatcheryProgram.HatcheryProgramUse ,HatcheryProgram.Authorizations ,HatcheryProgram.FundingSource ,HatcheryProgram.ContactAgency ,HatcheryProgram.Comments AS ProgramComments ,HatcheryProgram.SubmitAgency ,HatcheryProgram.RefID ,HatcheryProgram.UpdDate ,HatcheryProgram.DataEntry ,HatcheryProgram.DataEntryNotes ,HatcheryProgram.CompilerRecordID ,HatcheryProgram.apikey FROM HatcheryProgram INNER JOIN HatcheryStock ON HatcheryProgram.StockID = HatcheryStock.StockID INNER JOIN Hatchery ON HatcheryProgram.HatcheryFacilityName = Hatchery.Hatch_Name WHERE (HatcheryProgram.Publish = 'Yes');