Average Annual Rainfall Histogram: sea lampreys
- could not execute query [ DECLARE @numsteps AS INT; SET @numsteps = @p1; create table #temp ( n int ) Declare @counter int Set @counter = 1 While @counter <= @numsteps Begin insert into #temp (n) values (@counter) Set @counter = @counter + 1 End ;with Hierachy(ParentId, ChildId) as ( select ParentId, ChildId from dbo.LineageRelationship WHERE ParentId = @p0 union all select e.ParentId, e.ChildId from dbo.LineageRelationship e inner join Hierachy eh on e.ParentId = eh.ChildId ) ,OrganismIds as ( select distinct ParentId as OrganismId from Hierachy union select distinct ChildId as OrganismId from Hierachy union select @p0 as OrganismId ) ,cte1 as ( SELECT n AS step, mn + (n - 1) * stepsize AS lb, mn + n * stepsize AS hb FROM #temp, (SELECT MIN(AnnualRainfall) AS mn, ((1E0*MAX(AnnualRainfall) + 0.0000000001) - MIN(AnnualRainfall)) / @numsteps AS stepsize FROM dbo.Squares) AS D WHERE n <= @numsteps ) ,cte2 as ( select step, count(*) as count from dbo.OrganismGeoCountryInteraction as a inner join dbo.Squares as b on a.GeoCountryId = b.CountryID inner join cte1 as c on b.AnnualRainfall >= lb and b.AnnualRainfall < hb inner join dbo.RelationshipStatementSequences as d on a.StatementId = d.StatementId where AnnualRainfall is not null and organismid in ( select organismid from OrganismIds ) group by step ) ,cte3 as ( select round((a.hb + a.lb) / 2, 1) as X, case when b.step is null then 0 else count end as Y1 from cte1 as a left outer join cte2 as b on a.step = b.step ) select X, round(cast(Y1 as float) / (select sum(Y1) from cte3),2) as Y1 from cte3 order by X ] Name:OrganismId - Value:3513086 Name:Steps - Value:20 [SQL: DECLARE @numsteps AS INT; SET @numsteps = @p1; create table #temp ( n int ) Declare @counter int Set @counter = 1 While @counter <= @numsteps Begin insert into #temp (n) values (@counter) Set @counter = @counter + 1 End ;with Hierachy(ParentId, ChildId) as ( select ParentId, ChildId from dbo.LineageRelationship WHERE ParentId = @p0 union all select e.ParentId, e.ChildId from dbo.LineageRelationship e inner join Hierachy eh on e.ParentId = eh.ChildId ) ,OrganismIds as ( select distinct ParentId as OrganismId from Hierachy union select distinct ChildId as OrganismId from Hierachy union select @p0 as OrganismId ) ,cte1 as ( SELECT n AS step, mn + (n - 1) * stepsize AS lb, mn + n * stepsize AS hb FROM #temp, (SELECT MIN(AnnualRainfall) AS mn, ((1E0*MAX(AnnualRainfall) + 0.0000000001) - MIN(AnnualRainfall)) / @numsteps AS stepsize FROM dbo.Squares) AS D WHERE n <= @numsteps ) ,cte2 as ( select step, count(*) as count from dbo.OrganismGeoCountryInteraction as a inner join dbo.Squares as b on a.GeoCountryId = b.CountryID inner join cte1 as c on b.AnnualRainfall >= lb and b.AnnualRainfall < hb inner join dbo.RelationshipStatementSequences as d on a.StatementId = d.StatementId where AnnualRainfall is not null and organismid in ( select organismid from OrganismIds ) group by step ) ,cte3 as ( select round((a.hb + a.lb) / 2, 1) as X, case when b.step is null then 0 else count end as Y1 from cte1 as a left outer join cte2 as b on a.step = b.step ) select X, round(cast(Y1 as float) / (select sum(Y1) from cte3),2) as Y1 from cte3 order by X ]