SQL 报告列中参数的计数

发布于 2024-12-19 15:38:43 字数 436 浏览 1 评论 0原文

我正在 SSRS 3.0 中使用包含以下字段的 SQL 表：

ApptID    BookedBy   ConfirmedBy   CancelledBy

我还有一个参数设置来选择要筛选的用户（与 BookedBy、ConfirmedBy 中的数据匹配）和 CancelledBy 列）称为 @Scheduler （这是一个多值参数/数组）。

我需要获取预订、确认和计划的计数，以了解 Scheduler 参数中的任何值在该列中显示的次数。

基本上：

COUNT(BookedBy IN (@Scheduler)) AS BookedCount

任何人都可以帮我解决执行此操作的语法吗？

原文

I am working in SSRS 3.0 with a SQL table including the following fields:

ApptID    BookedBy   ConfirmedBy   CancelledBy

I also have a parameter setup to select which users to filter by (matches data in the BookedBy, ConfirmedBy and CancelledBy columns) called @Scheduler (which is a multi vale parameter/array).

I need to get a count for booked, confirmed and scheduled for how many times any value in the Scheduler parameter shows up in that column.

Basically:

COUNT(BookedBy IN (@Scheduler)) AS BookedCount

Can anyone help me out with the syntax for doing this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

很糊涂小朋友 2024-12-26 15:38:43

尝试这个

 SELECT Count(BookedBy = @Scheduler) as [BookedCount],
        Count(ConfirmedBy = @Scheduler) as [ConfirmedCount],
        Count(CancelledBy = @Scheduler) as [CancelledCount]
 FROM tablename
 WHERE BookedBy = @Scheduler OR 
       ConfirmedBy = @Scheduler OR
       CancelledBy = @Scheduler

注意 - 未经测试可能包含拼写错误

如果您的输入是用逗号分隔的列表，您可以将其转换为表格。请参阅如下参考：

http://www.projectdmx.com/tsql/sqlarrays.aspx

对于这个用例，我建议将结果保存在 CTE 中的解决方案之一（因为您只需要转换输入一次，这将是最快的）

然后您可以使用该表（称为 sTable 并带有列名）喜欢这个：

 SELECT Count(Bo.Name) as [BookedCount],
        Count(Co.Name) as [ConfirmedCount],
        Count(Ca.Name) as [CancelledCount]
 FROM tablename
 LEFT JOIN sTable Bo ON BookedBy = Bo.name
 LEFT JOIN sTable Co ON ConfirmedBy = Co.name 
 LEFT JOIN sTable Ca ON CancelledBy = Ca.name

我想这会起作用，但它看起来不像其他的那么好：

 SELECT (SELECT COUNT(*) FROM table WHERE BookedBy in (@Scheduler)) AS [BookedCount],
        (SELECT COUNT(*) FROM table WHERE ConfirmedBy in (@Scheduler)) as [ConfirmedCount],
        (SELECT COUNT(*) FROM table WHERE CancelledBy in (@Scheduler)) as [CancelledCount]

Try this

 SELECT Count(BookedBy = @Scheduler) as [BookedCount],
        Count(ConfirmedBy = @Scheduler) as [ConfirmedCount],
        Count(CancelledBy = @Scheduler) as [CancelledCount]
 FROM tablename
 WHERE BookedBy = @Scheduler OR 
       ConfirmedBy = @Scheduler OR
       CancelledBy = @Scheduler

NB - Not tested might contain typos

If your input is a list separated by commas you can convert that to a table. See a reference like this:

http://www.projectdmx.com/tsql/sqlarrays.aspx

For this use case I'd recommend one of the solutions that saves the result in a CTE (since you only need to convert your input once and this will be fastest)

Then you could use that table (called sTable with column name) like this:

 SELECT Count(Bo.Name) as [BookedCount],
        Count(Co.Name) as [ConfirmedCount],
        Count(Ca.Name) as [CancelledCount]
 FROM tablename
 LEFT JOIN sTable Bo ON BookedBy = Bo.name
 LEFT JOIN sTable Co ON ConfirmedBy = Co.name 
 LEFT JOIN sTable Ca ON CancelledBy = Ca.name

I guess this will work but it does not seem as nice as the others:

 SELECT (SELECT COUNT(*) FROM table WHERE BookedBy in (@Scheduler)) AS [BookedCount],
        (SELECT COUNT(*) FROM table WHERE ConfirmedBy in (@Scheduler)) as [ConfirmedCount],
        (SELECT COUNT(*) FROM table WHERE CancelledBy in (@Scheduler)) as [CancelledCount]

回复收藏 0 原文

~没有更多了~