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