如何总结工作时间并在gridview中显示它 [英] How to sum up the working hours and display it in a gridview

问题描述

在执行以下查询时我能够得到输出



查询 -

On executing the below query i am able to get the output as

Query-

"SELECT ( select ProcessName From ITProcess where id=it.Process) as Process,(select ITDomainName from ITdomain where id=it.Domain) as Domain,(select ITTaskDEsc from ittaskdesc where id=it.Task) as Task, " +
" CAST(DATEDIFF(Minute,it.StartDate, it.EndDate) / 60 AS VARCHAR(5)) + ' Hrs' + ':' + RIGHT('0' + CAST(DATEDIFF(Minute, it.StartDate, it.EndDate) % 60 AS VARCHAR(2)), 2)  + ' Min' AS 'WorkingTime',StartDate,Enddate FROM ITDailyTask it where Process=" + Process + ""

Cs代码 -

Cs code-

DataTable dt = adm.GetITActivityBYProcess(ddlProcess.SelectedValue);
            if (dt.Rows.Count > 0)
            {
                grdReport.DataSource = dt;
                grdReport.DataBind();
            }
            else
            {
                grdReport.DataSource = null;
                grdReport.DataBind();
            }
        }
        else
        {
            DataTable dt = adm.GetITActivityBYProcess(ddlProcess.SelectedItem.Text);
            if (dt.Rows.Count > 0)
            {
                grdReport.DataSource = dt;
                grdReport.DataBind();
            }
            else
            {
                grdReport.DataSource = null;
                grdReport.DataBind();
            }
        }

工作时间



24小时：00分钟

48小时：00分



在网格视图中，但我需要总结这两个工作时间并显示为72小时：00分钟作为输出我怎么能这样做



我尝试过：

WorkingTime

24 Hrs:00 Min
48 Hrs:00 Min

in grid view but I need to sum up those two working times and display as 72hrs:00min as output how can I do this

What I have tried:

i had tried the above code but its not working

推荐答案

第一点 - 永远不要连接字符串创建sql命令。使用参数化查询 - 请参阅查询参数化备忘单 - OWASP [ ^ ]



第二点 - 不要使用所有这些子查询，效率都不高。使用联接 - 请参阅 SQL联接的可视化表示 [ ^ ]



第三点 - 以小时为单位列出差异，而不是嵌入了小时和最小的varchar。但是，您可以使用（n适当的）子查询或公用表表达式使SQL更易于阅读。以下查询将返回一个额外的结果行，其中包含总小时数和分钟数（正确处理分钟数> 60时总和）

First point - never concatenate strings to create sql commands. Use parameterized queries - see Query Parameterization Cheat Sheet - OWASP[^]

Second point - don't use all those sub-queries, it is not efficient. Use Joins instead - see Visual Representation of SQL Joins[^]

Third point - it would be far better to have a column for the difference in hours rather than the varchar with "Hrs" and "Min" embedded. However, you can make your SQL easier to read by using a(n appropriate) sub-query or Common Table Expression. The following query will return an extra line of results containing the total hours and minutes (properly handling minutes > 60 when summed)

;WITH CTE AS
(
	select 
		P.ProcessName, D.ITDomainName, T.ITTaskDEsc,
		DATEDIFF(Minute,it.StartDate, it.EndDate) as DiffTime,
		StartDate,Enddate 
	FROM ITDailyTask it 
	LEFT JOIN ITProcess P on it.Process = P.id
	LEFT JOIN ITdomain D on it.Domain = D.id
	LEFT JOIN ittaskdesc T on it.Task = T.id
	where Process=@Process
	UNION
	select 'Total','','',SUM(DATEDIFF(Minute,it.StartDate, it.EndDate)) as DiffTime,
	NULL, NULL
	FROM ITDailyTask it 
	where Process=@Process
) 
SELECT ProcessName, ITDomainName, ITTaskDEsc,
	CAST(DiffTime / 60 AS VARCHAR(5)) + ' Hrs : ' + RIGHT('0' + CAST(DiffTime % 60 AS VARCHAR(2)), 2) + ' Min' AS WorkingTime,
	StartDate,Enddate 
FROM CTE

这篇关于如何总结工作时间并在gridview中显示它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！