Multiple calculations on columns in a select statement

# Multiple calculations on columns in a select statement

• Thursday, February 21, 2013 9:49 PM

I have a query that performs date calculations on columns that are included in a select statement.  A few of the calculations are based on calculated column values that are included in the same select statement.  Below is an example of what I'm trying do.

Table

column 1    column 2    column 3      column 4    column 5

02/10/12    02/19/12    null             03/06/12    null

I need to calculate the amount of time from column 1 to column 2 or column 3 depending on which value is not null.  In the sample table above, the first value(val1) would be calculated by the difference of column 1 and column 2.  I would then need to calculate the time between the first calculated value(val1) and column 4 or column 5 depending on which value is not null...I need to repeat this type of calculation on 4 or 5 columns.   Is there a quick way to do this in a select statement or should I use temp tables?

### All Replies

• Thursday, February 21, 2013 10:21 PM

Hi Brian, You might give this a try. Hope this helps.

`SELECT DATEDIFF(DD, Column1, COALESCE(Column2, Column3))`

• Thursday, February 21, 2013 10:33 PM

So "val" would be datediff(DAY, col1, coalesce(col2, col3)). Then you say that you want the time beteen val and one of cole 4 and col5, but that is a little spooky, since val is an interval.

But apart from that, if you don't want to repeat expressions, you can use CTEs (Common Table Expression):

WITH CTE (
SELECT col1, col2, col3, col4, col5,
datediff(DAY, col1, coalesce(col2, col3) as val1
FROM   tbl
), CTE2 (
SELECT col1, col2, col3, col4, col5, val,
datediff(DAY, convert(datetime, val), coalesce(col, col5)) AS val2
FROM CTE
)
SELECT * FROM CTE

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
• Thursday, February 21, 2013 10:36 PM

I would then need to calculate the time between the first calculated value(val1) and column 4 or column 5 depending on which value is not null...I need to repeat this type of calculation on 4 or 5 columns.   Is there a quick way to do this in a select statement or should I use temp tables?

One method is with a CTE.  Extending ione721's example:

```WITH result1 AS (
SELECT
column4
,column5
,DATEDIFF(day, column1, COALESCE(column2, column3)) AS val1
FROM dbo.SomeTable
)
,result2 AS (
SELECT
val1
,DATEDIFF(day, val2, COALESCE(column4, column5)) AS val2
FROM result1
)
SELECT
val1
,val2
FROM result2;
```

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

• Monday, February 25, 2013 7:58 PM

I need to calculate the difference in day and time.  When I change the datediff function above to datediff to use SS instead of DAY, I receive the following error message:

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

`select datediff(SS, '1900-01-01 11:37:32.000', '2010-12-07 14:34:34.883') as val1`

• Monday, February 25, 2013 8:25 PM
Moderator

Well, the error is clear, right? You can not use second for two such dates. You have to use days to get difference in days.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Monday, February 25, 2013 9:50 PM

Yes, but I'm not sure how to calculate the difference between two datetime values without using a datepart.  Is there a way to return the calculated value in the sql datetime format? ex. 2010-09-09 14:34:34.883

• Monday, February 25, 2013 10:50 PM
Moderator

Can you explain again what you're trying to achieve? The difference between two dates can be measured in some interval, say, days, weeks, years, etc.

How do you want to construct a date?

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Tuesday, February 26, 2013 1:31 PM

The dates in the my example above are actually datetime datatypes.  I want to be able to compare two datetime fields and report the difference in days, hours, and minutes.  When I try to use datediff, I must specify a datepart.
• Tuesday, February 26, 2013 1:46 PM
Moderator

Yes, that's correct. You first get difference in days. Then you add that day difference to the smaller date and calculate difference in minutes. The difference in minutes you can change into difference in hours and in minutes. You can not use difference in minutes (and in seconds) directly as you have a risk of the overflow.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog