Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Multiple calculations on columns in a select statement

Answered Multiple calculations on columns in a select statement

  • Thursday, February 21, 2013 9:49 PM
     
     
    Sign In to Vote
    0

    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
     
      Has Code
    Sign In to Vote
    0

    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
     
     Answered
    Sign In to Vote
    1

    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
     
     Answered Has Code
    Sign In to Vote
    1

      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
     
      Has Code
    Sign In to Vote
    0
    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
     
     
    Sign In to Vote
    0
    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
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0
    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
     
     Answered
    Sign In to Vote
    1
    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