CODEWITHSUNDEEP
sqlsql-serverselect
Cold fire
Cold fire

Reputation: 21

How to calculate sum of multiple columns value?

I'm training to calculate a new "Total" column from three new columns that I already calculated in my query, see below:

 SELECT TOP 500 request_key
      ,request_id
      ,CreatedTime
      ,AssignTime
      ,IssueTime
      ,CloseTime
      ,RequestDetails
      ,request_type
      ,Product
      ,SiteName
      ,DATEDIFF(MINUTE, createdtime, AssignTime + 1) AS Idle
      ,DATEDIFF(MINUTE, AssignTime, IssueTime + 1) AS Assign
        ,DATEDIFF(MINUTE, IssueTime, CloseTime +1 ) AS Delegate

FROM [wombat].[dbo].[VwWombatCrossSiteCorrelationReport]

The new "Total" column should have the next data: Idle + Assign + Delegate

Upvotes: 0

Views: 74

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

An interesting way to do this uses outer apply:

SELECT TOP 500 cr.*, v.idle, v.assign, v.delegate,
       (v.idle + v.assign + v.delegate) as Total
FROM [wombat].[dbo].[VwWombatCrossSiteCorrelationReport] cr outer apply
     (select DATEDIFF(MINUTE, createdtime, AssignTime + 1) as Idle,
             DATEDIFF(MINUTE, AssignTime, IssueTime + 1) as Assign,
             DATEDIFF(MINUTE, IssueTime, CloseTime +1 ) as Delegate
     ) v;

Upvotes: 0

BI Dude
BI Dude

Reputation: 2016

I would just make it simple and add another calculated column:

SELECT TOP 500 request_key
      ,request_id
      ,CreatedTime
      ,AssignTime
      ,IssueTime
      ,CloseTime
      ,RequestDetails
      ,request_type
      ,Product
      ,SiteName
      ,DATEDIFF(MINUTE, createdtime, AssignTime + 1) AS Idle
      ,DATEDIFF(MINUTE, AssignTime, IssueTime + 1) AS Assign
      ,DATEDIFF(MINUTE, IssueTime, CloseTime +1 ) AS Delegate
     , Total = DATEDIFF(MINUTE, createdtime, AssignTime + 1) + DATEDIFF(MINUTE, AssignTime, IssueTime + 1) + DATEDIFF(MINUTE, IssueTime, CloseTime +1 )
 FROM [wombat].[dbo].[VwWombatCrossSiteCorrelationReport]

Upvotes: 0

Mureinik
Mureinik

Reputation: 312219

You could, of course, just repeat the calculations that produced Idle, Assign and Delegate, but, well.. yuck. Another option would be to use this query as a subquery to avoid having to repeat calculations:

SELECT *, Idle + Assign + Delegate AS Total
FROM   (SELECT TOP 500 request_key,
                       request_id,
                       CreatedTime,
                       AssignTime,
                       IssueTime,
                       CloseTime,
                       RequestDetails,
                       request_type,
                       Product,
                       SiteName,
                       DATEDIFF(MINUTE, createdtime, AssignTime + 1) AS Idle,
                       DATEDIFF(MINUTE, AssignTime, IssueTime + 1) AS Assign,
                       DATEDIFF(MINUTE, IssueTime, CloseTime +1 ) AS Delegate
        FROM [wombat].[dbo].[VwWombatCrossSiteCorrelationReport]) t

Upvotes: 1

Gulmuhammad Akbari
Gulmuhammad Akbari

Reputation: 2034

(DATEDIFF(MINUTE, createdtime, AssignTime + 1) AS Idle
      + DATEDIFF(MINUTE, AssignTime, IssueTime + 1) AS Assign
        + DATEDIFF(MINUTE, IssueTime, CloseTime +1 ) AS Delegate) AS total

Upvotes: 0

Related Questions