CODEWITHSUNDEEP
sqlsql-serverdatesumsubquery
not_ur_avg_cookie
not_ur_avg_cookie

Reputation: 333

build sum of multiple subquery results

I am trying to build a sum of the results from multiple subqueries. I have numerous periods defined with two variables:

declare 
@left1 date = '2014-06-25',
@return1 date ='2014-08-21',

 @left2 date = '2014-10-29',
 @return2 date = '2015-03-15',

@left3 date = '2015-04-21',
@return3 date = '2015-09-19'

Then I am calculating the days between these two dates but ultimately I would like to add them up and get a total:

select  datediff(day, @left1, @return1 )

select  datediff(day, @left2, @return2 )

select  datediff(day, @left3, @return3 )

I thought of CTE's but it didn't work out. What is a proper way to do that?

Upvotes: 1

Views: 53

Answers (4)

Dheerendra
Dheerendra

Reputation: 306

SELECT SUM(DATEDIFF(DAY,[LEFT],[Return])) AS TOTALDAYS
FROM
(
  select @left1 AS [Left],@return1 AS [Return]
  UNION ALL
  select @left2 AS [Left],@return2 AS [Return]
  UNION ALL
  select @left3 AS [Left],@return3 AS [Return]
) X

Upvotes: 2

Sanpas
Sanpas

Reputation: 1180

This query can help you: 

Select 
    sum(NbDay) as totaldiffday
From (
        select  datediff(day, @left1, @return1 ) As NbDay
        Union
        select  datediff(day, @left2, @return2 ) As NbDay 
        Union 
        select  datediff(day, @left3, @return3 ) As NbDay
) as SubQuery

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33571

Pretty sure you just need some basic addition.

select datediff(day, @left1, @return1 )
     + datediff(day, @left2, @return2 )
     + datediff(day, @left3, @return3 )

Upvotes: 4

Anusha Subashini
Anusha Subashini

Reputation: 397

select  
   datediff(day, @left1, @return1 ) + 
   datediff(day, @left2, @return2 ) + 
   datediff(day, @left3, @return3 )

Upvotes: 3

Related Questions