How to sum up the working hours and display it in a gridview

Question

1.00/5 (1 vote)

See more:

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

Query-

SQL

"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 code-

C#

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();
            }
        }

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

Posted 27-Apr-17 20:19pm

kav@94

Updated 28-Apr-17 2:16am

Nirav Prabtani

v2

Add a Solution

Comments

Jochen Arndt 28-Apr-17 3:17am

The same question has been already asked at https://www.codeproject.com/Questions/1184397/How-to-sum-up-the-working-hours-and-display-it-in with a different user account.

kav@94 28-Apr-17 5:07am

does that had any answer

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-04-28T02:16:00

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)

SQL

;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