sql - 简化查询以用作 winform rdlc 报告中的数据集
问题描述
我试图让这个查询在我正在构建的winform应用程序中填充reportviewer(并将查询中的参数更改为在填充要查看的报告之前在表单上选择的值)。但是over功能无法工作。用户将从单选框、下拉列表等中输入参数,单击搜索,reportviewer 将打开以便他们可以打印。查询非常难看,我已经研究了一段时间。这是我获得所需格式的结果集的唯一方法。
SQL QUERY 寻求帮助以缩短相同的结果
DECLARE @EndReport DATETIME, @StartReport DATETIME, @Location INT, @Department varchar(50)
SET @EndReport = '10-15-2018' SET @StartReport = '10-15-2018' SET @Department = 'fb' SET @Location = 10
SELECT row_number() over (order by (ai.FirstName + ' ' + ai.LastName)) RowNum
,AssociateName = isnull(upper(ai.FirstName + ' ' + ai.LastName),'**' + cast(t.ID as varchar(30)) + '**')
,ID = t.ID
,Codes = (t.DeptCode + '-' + t.OpCode)
,TimeSUM = cast(SUM(datediff(second,StartTime, FinishTime) /3600.0) as decimal (6,2))
,Units = SUM(Units)
,UPH = cast(isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60 as decimal(10,0))
into temptable10
FROM TimeLogNEW t LEFT JOIN AssociateInfo ai
ON t.ID = ai.ID
JOIN GoalSetUp g
ON (t.DeptCode + '-' + t.OpCode) = (g.DeptCode + '-' + g.OpCode)
WHERE EventDate between @StartReport and @EndReport
and t.Location = @Location and g.location= @Location and ((t.DeptCode + t.OpCode) in (g.DeptCode + g.OpCode)) and t.DeptCode = @Department
GROUP BY t.DeptCode,t.OpCode,ai.FirstName,ai.LastName, t.ID
SELECT
[Associate Name] = AssociateName
,[Codes] = Codes
,[TimeSUM] = TimeSUM
,[Units] = Units
,[UPH] = UPH
,[UPH Target] = Goal
,[Actual %] = CASE WHEN goal = 0 then '0%'
else convert(varchar,cast(100* (isnull(UPH,0)/nullif(Goal,0)) as decimal(10,0))) + '%' END
FROM goalsetup g join temptable10 on g.DeptCode = left(codes,2)and g.opcode = RIGHT(codes,2)
WHERE g.Location = @Location
ORDER BY Codes, UPH Desc
drop table temptable10
SQL 结果集
视觉工作室错误
添加视觉工作室屏幕截图。在下面的答案后更新
解决方案
我不知道这是否有效,因为我没有要测试的表。(即如果出现错误,您将不得不决定要做什么。)
- row_number() 列没有明显的原因,所以我删除了它
Codes = (t.DeptCode + '-' + t.OpCode)
制作表格时,其次是:join temptable10 on g.DeptCode = left(codes,2)and g.opcode = RIGHT(codes,2)
- 这是低效且不必要的,只需保留两列
实际上,发生了很多串联的部门和操作代码,这似乎是不必要的,只需使用 2 列而不需要额外的努力。
between
在用于日期范围时是一条狗,我强烈建议使用下面查询中的内容。这个日期范围结构>=
适用< (+1 day)
于所有日期/时间数据类型- 请在引用任何列时使用表别名,像我这样的读者无法知道其中一些列来自哪个表,这使得调试/维护变得更加困难。
建议查询:
DECLARE @EndReport datetime
, @StartReport datetime
, @Location int
, @Department varchar(50)
SET @EndReport = '10-15-2018'
SET @StartReport = '10-15-2018'
SET @Department = 'fb'
SET @Location = 10
SELECT
AssociateName = ISNULL(UPPER(ai.FirstName + ' ' + ai.LastName), '**' + CAST(t.ID AS varchar(30)) + '**')
, ID = t.ID
, Codes = (t.DeptCode + '-' + t.OpCode)
, t.DeptCode
, t.OpCode
, TimeSUM = CAST(SUM(DATEDIFF(SECOND, StartTime, FinishTime) / 3600.0) AS decimal(6, 2))
, Units = SUM(Units)
, UPH = CAST(ISNULL(SUM(Units) / NULLIF(SUM(DATEDIFF(MINUTE, StartTime, FinishTime)) * 1.0, 0), 0.0) * 60 AS decimal(10, 0))
INTO temptable10
FROM TimeLogNEW t
LEFT JOIN AssociateInfo ai
ON t.ID = ai.ID
JOIN GoalSetUp g
ON t.DeptCode = g.DeptCode AND t.OpCode = g.OpCode
WHERE EventDate >= @StartReport AND EventDate < dateadd(day,1,@EndReport)
AND t.Location = @Location
AND g.location = @Location
AND t.DeptCode = @Department
GROUP BY
t.DeptCode
, t.OpCode
, ai.FirstName
, ai.LastName
, t.ID
SELECT
[Associate Name] = t10.AssociateName
, [Codes] = t10.Codes
, [TimeSUM] = t10.TimeSUM
, [Units] = t10.Units
, [UPH] = t10.UPH
, [UPH Target] = g.Goal
, [Actual %] =
CASE
WHEN g.goal = 0 THEN '0%'
ELSE CONVERT(varchar, CAST(100 * (ISNULL(t10.UPH, 0) / NULLIF(g.Goal, 0)) AS decimal(10, 0))) + '%'
END
FROM goalsetup g
JOIN temptable10 t10
ON g.DeptCode = t10.DeptCode
AND g.opcode = t10.opcode
WHERE g.Location = @Location
ORDER BY
t10.Codes
, t10.UPH DESC
DROP TABLE temptable10
推荐阅读
- ansible - 带有标签的 Ansible Azure 动态清单不起作用
- go - 从邮递员获取http post请求中的参数
- javascript - 如何使用 lodash _isEmpty 检查对象是否为空?
- vb.net - 你调用的对象是空的。- SagePayV3Server.GetServerPaymentRequest
- r - 通过 for 循环在 pdf 中保存多个成对相关图
- excel - 在一个单元格范围内,如何使用 vba 输入基于条件的公式?
- airflow - 如何创作具有相对于执行日期的动态任务集的 DAG?
- docker - 构建 Docker 映像时,“npm install”失败并显示 ECONNREFUSED
- ios - 点击 PresentationLink (SwiftUI) 时如何禁用闪烁?
- python - 谁能解释以下代码的输出?