如何仅从主选择中可用的数据中进行嵌套选择拉取

Question

我正在SSRS report Builder 2016中创建一个报告。

我有一组数据，其中包含一组机构以及每个机构每月的账单金额(TotalBilled(。每个机构的TotalBilled可以有多个值。我想找到每个机构的TotalBilled中值。

我在查询中计算中值，但它来自所有机构的所有TotalBilled，而不仅仅是与我在参数中设置的机构相关的TotalBilled。

这是我的数据集查询：

DECLARE @Cnt int = (SELECT COUNT(TotalBilled) FROM InvoiceLine)
select 
a.AgencyID
a.startdate,
a.enddate,
[TotalBilled],
((SELECT TOP 1 TotalBilled
FROM   (
SELECT  TOP (@Cnt/2) TotalBilled
FROM    InvoiceLine I
WHERE   TotalBilled is NOT NULL
ORDER BY TotalBilled ASC
) AS I
ORDER BY TotalBilled DESC) +
(
SELECT TOP 1 TotalBilled
FROM   (
SELECT  TOP (@Cnt/2) TotalBilled
FROM    InvoiceLine I
WHERE   TotalBilled is NOT NULL
ORDER BY TotalBilled DESC
) AS I
ORDER BY TotalBilled ASC)) / 2 AS MedianTotalBilled
from Agency a
left join invoiceLine invl
on a.InvoiceID = invl.InvoiceID
where (a.agencyid in (@agency))
and (a.startdate >= @startdate and a.enddate <= @enddate)

什么是正确的方法来更改我的meidan值计算，使其受到我的参数的影响，而不仅仅是从整个数据库中提取？

编辑：

此外，将连接添加到中值计算中并没有产生中值的预期值。以下是我目前正在尝试的：

(SELECT TOP 1 TotalBilled
FROM   (
SELECT  TOP (@Cnt/2) TotalBilled
left join invoiceLine invl
on a.InvoiceID = invl.InvoiceID
where (a.agencyid in (@agency))
and (a.startdate >= @startdate and a.enddate <= @enddate)
and  TotalBilled is NOT NULL
ORDER BY TotalBilled ASC
) AS I
ORDER BY TotalBilled DESC)

Answer 1

我相信您想将行限制添加到子选择中，如下

DECLARE @Cnt int = (SELECT COUNT(TotalBilled) FROM InvoiceLine)
select 
a.AgencyID
a.startdate,
a.enddate,
[TotalBilled],
((SELECT TOP 1 TotalBilled
FROM   (
SELECT  TOP (@Cnt/2) TotalBilled
FROM    InvoiceLine I
WHERE   TotalBilled is NOT NULL
and  a.InvoiceID = I.InvoiceID --new
ORDER BY TotalBilled
) AS I
ORDER BY TotalBilled DESC) +
(
SELECT TOP 1 TotalBilled
FROM   (
SELECT  TOP (@Cnt/2) TotalBilled
FROM    InvoiceLine I
WHERE   TotalBilled is NOT NULL
and  a.InvoiceID = I.InvoiceID --new
ORDER BY TotalBilled DESC
) AS I
ORDER BY TotalBilled ASC)) / 2 AS MedianTotalBilled
from Agency a
left join invoiceLine invl
on a.InvoiceID = invl.InvoiceID
where (a.agencyid in (@agency))
and (a.startdate >= @startdate and a.enddate <= @enddate)

编辑：行计数的选择器也需要限制

select 
a.AgencyID
a.startdate,
a.enddate,
[TotalBilled],

((SELECT TOP 1 TotalBilled
FROM   (
SELECT  TOP (
(
SELECT  count(*)
FROM    InvoiceLine I
WHERE   TotalBilled is NOT NULL
and  a.InvoiceID = I.InvoiceID 
)

/2) TotalBilled
FROM    InvoiceLine I
WHERE   TotalBilled is NOT NULL
and  a.InvoiceID = I.InvoiceID --new
ORDER BY TotalBilled
) AS I
ORDER BY TotalBilled DESC)
from Agency a
left join invoiceLine invl
on a.InvoiceID = invl.InvoiceID
where (a.agencyid in (@agency))
and (a.startdate >= @startdate and a.enddate <= @enddate)