How to get sum of date difference from multiple records

• Atif

  Hall of Fame

  Points: 3578

  More actions

  March 5, 2009 at 1:51 am

  #135260

  I have a table created and populated as

  USE Adventureworks

  GO

  CREATE TABLE datesum (id tinyint, name VARCHAR(25),sdate DATETIME, edate DATETIME )

  GO

  INSERT INTO datesum

  SELECT 1,'TestName1','2004-01-25','2005-02-06'

  UNION ALL

  SELECT 2,'TestName1','2003-07-20','2005-03-18'

  UNION ALL

  SELECT 3,'TestName1','2002-12-24','2004-03-21'

  UNION ALL

  SELECT 4,'TestName1','2001-10-27','2003-03-03'

  GO

  Now table has four rows with date records of single person. I am required to calculate the sum of datediff of all of records of a given person in days.

  For example in datesum records i require total days as 1930 days. Please some efficient way for it preferably by avoiding cursors.

  Regards

  DBDigger Microsoft Data Platform Consultancy.

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  March 5, 2009 at 2:12 am

  #953846

  Just write what you said...

  DECLARE @datesum TABLE (id tinyint, name VARCHAR(25),sdate DATETIME, edate DATETIME )

  INSERT INTO @datesum

  SELECT 1,'TestName1','2004-01-25','2005-02-06'

  UNION ALL

  SELECT 2,'TestName1','2003-07-20','2005-03-18'

  UNION ALL

  SELECT 3,'TestName1','2002-12-24','2004-03-21'

  UNION ALL

  SELECT 4,'TestName1','2001-10-27','2003-03-03'

  SELECT SUM(DATEDIFF(DAY, sdate, edate)) FROM @datesum

  Greets

  Flo

• Atif

  Hall of Fame

  Points: 3578

  More actions

  March 5, 2009 at 2:13 am

  #953848

  Thanks, its good.

  DBDigger Microsoft Data Platform Consultancy.

• Dugi

  SSCoach

  Points: 17998

  More actions

  March 5, 2009 at 7:41 am

  #954031

  florian.reischl (3/5/2009)

  ---

  Just write what you said...

  DECLARE @datesum TABLE (id tinyint, name VARCHAR(25),sdate DATETIME, edate DATETIME )

  INSERT INTO @datesum

  SELECT 1,'TestName1','2004-01-25','2005-02-06'

  UNION ALL

  SELECT 2,'TestName1','2003-07-20','2005-03-18'

  UNION ALL

  SELECT 3,'TestName1','2002-12-24','2004-03-21'

  UNION ALL

  SELECT 4,'TestName1','2001-10-27','2003-03-03'

  SELECT SUM(DATEDIFF(DAY, sdate, edate)) FROM @datesum

  Greets

  Flo

  Yep I agree with you florian ....just write what you said!

  :hehe:

  ============================================================
  SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
  http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]