- 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:3941954 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
]