datediff on dates contained in single column

• whitlander

  SSChasing Mays

  Points: 637

  More actions

  July 29, 2010 at 9:02 am

  #1199860

  --Where a date diff is calculated within the same month i.e. where the planned.period

  --dates used in the date diff are both in June 20-07-2010 (EventType=5) and 10-07-2010 (EventType=11),

  --the output, '+10', is accurate.

  INSERT INTO dbo.event (datasource_id, EventType_ID, User_ID, period, Notes, Success)

  SELECT '35', '11', '3', '10/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  go

  INSERT INTO dbo.event (datasource_id, EventType_ID, User_ID, period, Notes, Success)

  SELECT '35', '6', '3', '13/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  go

  INSERT INTO dbo.event (datasource_id, EventType_ID, User_ID, period, Notes, Success)

  SELECT '35', '5', '3', '20/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  go

  --However where the planned.period dates needed are speard over two month:10-07-2010(EventType=5)

  --and 05-06-2010(EventType=11), the output '-18' is inaccurate because instead of using 05-06-2010 in the

  --datediff, the output considers the next nearest planned.period in July. The output should be '+13'.

  INSERT INTO dbo.event (datasource_id, EventType_ID, User_ID, period, Notes, Success)

  SELECT '35', '3', '3', '10/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  go

  INSERT INTO dbo.event (datasource_id, EventType_ID, User_ID, period, Notes, Success)

  SELECT '35', '6', '3', '13/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  go

  INSERT INTO dbo.event (datasource_id, EventType_ID, User_ID, period, Notes, Success)

  SELECT '35', '11', '3', '20/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  go

  INSERT INTO dbo.event (datasource_id, EventType_ID, User_ID, period, Notes, Success)

  SELECT '35', '5', '3', '02/08/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  go

  INSERT INTO dbo.event (datasource_id, EventType_ID, User_ID, period, Notes, Success)

  SELECT '35', '11', '3', '20/08/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  go

  Does that help?

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  July 29, 2010 at 9:18 am

  #1199873

  whitlander (7/29/2010)

  ---

  Does that help?

  I'm feeling generous. . .

  This is what you were asked to produce

  SET DATEFORMAT dmy

  GO

  DECLARE @TABLE AS TABLE(

  datasource_id VARCHAR(2),

  EventType_ID VARCHAR(2),

  User_ID VARCHAR(1),

  period DATETIME,

  Notes VARCHAR(255),

  Success VARCHAR(1))

  INSERT INTO @TABLE

  SELECT '35', '11', '3', '10/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  UNION ALL SELECT '35', '6', '3', '13/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  UNION ALL SELECT '35', '5', '3', '20/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  UNION ALL SELECT '35', '3', '3', '10/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  UNION ALL SELECT '35', '6', '3', '13/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  UNION ALL SELECT '35', '11', '3', '20/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  UNION ALL SELECT '35', '5', '3', '02/08/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  UNION ALL SELECT '35', '11', '3', '20/08/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  GO

  SET DATEFORMAT mdy

  That way, anyone wishing to help only has to copy and paste to have sample data to play with.

  What is your expected results from the above? What do you want to get back?

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 29, 2010 at 9:20 am

  #1199875

  skcadavre (7/29/2010)

  ---

  whitlander (7/29/2010)

  ---

  Does that help?

  I'm feeling generous. . .

  This is what you were asked to produce

  SET DATEFORMAT dmy

  GO

  DECLARE @TABLE AS TABLE(

  datasource_id VARCHAR(2),

  EventType_ID VARCHAR(2),

  User_ID VARCHAR(1),

  period DATETIME,

  Notes VARCHAR(255),

  Success VARCHAR(1))

  INSERT INTO @TABLE

  SELECT '35', '11', '3', '10/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  UNION ALL SELECT '35', '6', '3', '13/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  UNION ALL SELECT '35', '5', '3', '20/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  UNION ALL SELECT '35', '3', '3', '10/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  UNION ALL SELECT '35', '6', '3', '13/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  UNION ALL SELECT '35', '11', '3', '20/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  UNION ALL SELECT '35', '5', '3', '02/08/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  UNION ALL SELECT '35', '11', '3', '20/08/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  GO

  SET DATEFORMAT mdy

  That way, anyone wishing to help only has to copy and paste to have sample data to play with.

  What is your expected results from the above? What do you want to get back?

  DROP TABLE #event

  CREATE TABLE #event (datasource_id INT, EventType_ID INT, [User_ID] INT, period DATETIME, Notes VARCHAR(60), Success CHAR(1))

  SET DATEFORMAT DMY

  INSERT INTO #event (datasource_id, EventType_ID, User_ID, period, Notes, Success)

  SELECT '35', '11', '3', '10/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1' UNION ALL

  SELECT '35', '6', '3', '13/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1' UNION ALL

  SELECT '35', '5', '3', '20/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1' UNION ALL

  SELECT '35', '3', '3', '10/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1' UNION ALL

  SELECT '36', '6', '3', '13/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1' UNION ALL

  SELECT '36', '5', '3', '02/08/2010 00:00:00', 'Planned Availabilty in TISDW', '1' UNION ALL

  SELECT '36', '11', '3', '20/08/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  SELECT [Days] = datediff(day, e5.period, e11.period), '#' '#',

  e5.*, '#' '#',

  e11.*

  FROM #event e5

  INNER JOIN #event e11 ON e11.datasource_id = e5.datasource_id

  AND e11.EventType_ID = 11

  WHERE e5.EventType_ID = 5

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  July 29, 2010 at 9:25 am

  #1199878

  You cheated Chris! You changed his sample data 😛

  I'm sure that what you posted is what he meant

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 29, 2010 at 9:35 am

  #1199882

  skcadavre (7/29/2010)

  ---

  You cheated Chris! You changed his sample data 😛

  I'm sure that what you posted is what he meant

  LOL!

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• whitlander

  SSChasing Mays

  Points: 637

  More actions

  July 30, 2010 at 7:45 am

  #1200328

  Thanks for your help.

  The initial proble, was where the two dates in the date diff span two months, the ouput is only considering datediff within the same month.

  using your code:

  CREATE TABLE #event1 (datasource_id INT, EventType_ID INT, [User_ID] INT, period DATETIME, Notes VARCHAR(60), Success CHAR(1))

  SET DATEFORMAT DMY

  INSERT INTO #event1 (datasource_id, EventType_ID, User_ID, period, Notes, Success)

  SELECT '35', '11', '3', '10/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1' UNION ALL

  SELECT '35', '6', '3', '13/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1' UNION ALL

  SELECT '35', '5', '3', '20/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1' UNION ALL

  SELECT '35', '3', '3', '10/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1' UNION ALL

  SELECT '36', '5', '3', '13/07/2010 00:00:00', 'Planned Availabilty in TISDW', '1' UNION ALL

  SELECT '36', '2', '3', '02/08/2010 00:00:00', 'Planned Availabilty in TISDW', '1' UNION ALL

  SELECT '36', '11', '3', '20/08/2010 00:00:00', 'Planned Availabilty in TISDW', '1'

  SELECT [Days] = datediff(day, e5.period, e11.period), '#' '#',

  e5.*, '#' '#',

  e11.*

  FROM #event1 e5

  INNER JOIN #event1 e11 ON e11.datasource_id = e5.datasource_id

  AND e11.EventType_ID = 11

  WHERE e5.EventType_ID = 5

  Solves this problem. Thank you both very very much.