Trying to Calculate Date Differences Between Same Records in the Same Tables.

• ducksql@gmail.com

  SSCrazy

  Points: 2826

  More actions

  October 6, 2011 at 10:54 am

  #244162

  I have a table with items and their order dates. I am trying to calculate the differences betweeen the dates. Using the sample table:

  --===== If the test table already exists, drop it

  IF OBJECT_ID('TempDB..#DateTest','U') IS NOT NULL DROP TABLE #DateTest

  GO

  --===== Create the test table

  CREATE TABLE #DateTest(

  ItemNVARCHAR(15),

  OrderDateDATETIME,

  DaysBetweenFLOAT

  )

  --===== Insert the test data into the test table

  INSERT INTO #DateTest (Item, OrderDate)

  SELECT '2P112669', '09/29/2011' UNION ALL

  SELECT '2P112669', '08/30/2011' UNION ALL

  SELECT '2P112669', '06/18/2011' UNION ALL

  SELECT '2P112669', '06/02/2011' UNION ALL

  SELECT '4P158765', '10/03/2011' UNION ALL

  SELECT '4P158765', '09/15/2011' UNION ALL

  SELECT '4P158765', '07/31/2011' UNION ALL

  SELECT '4P158765', '07/13/2011'

  --==== SELECT the records

  SELECT * FROM #DateTest

  Here is the expected result:

  Item OrderDate DaysBetween

  2P1126692011-09-29 00:00:00.000 7

  2P1126692011-08-30 00:00:00.000 30

  2P1126692011-06-18 00:00:00.000 73

  2P1126692011-06-02 00:00:00.000 16

  4P1587652011-10-03 00:00:00.000 3

  4P1587652011-09-15 00:00:00.000 18

  4P1587652011-07-31 00:00:00.000 46

  4P1587652011-07-13 00:00:00.000 18

  For example, the 7 in the first record is the difference between today's date and 9/29/11. The 30 in the second row is the difference between 9/29/11 and 8/30/11. What is the best way to do this? If you need any more information, please let me know.

  Thanks.

  Stev

• bkubicek

  SSChampion

  Points: 10948

  More actions

  October 6, 2011 at 11:03 am

  #1391338

  Here is what I tried and it seemed to work:

  CREATE TABLE #DateTest( id int identity(1,1),

  Item NVARCHAR(15),

  OrderDate DATETIME,

  DaysBetween FLOAT

  )

  --===== Insert the test data into the test table

  INSERT INTO #DateTest (Item, OrderDate)

  SELECT '2P112669', '09/29/2011' UNION ALL

  SELECT '2P112669', '08/30/2011' UNION ALL

  SELECT '2P112669', '06/18/2011' UNION ALL

  SELECT '2P112669', '06/02/2011' UNION ALL

  SELECT '4P158765', '10/03/2011' UNION ALL

  SELECT '4P158765', '09/15/2011' UNION ALL

  SELECT '4P158765', '07/31/2011' UNION ALL

  SELECT '4P158765', '07/13/2011'

  --==== SELECT the records

  SELECT * FROM #DateTest dt

  update dt2 set DaysBetween = DATEDIFF(day,dt2.orderdate, isnull(dt.orderdate,GETDATE()))

  FROM #DateTest dt2

  left join #DateTest dt on dt.id = dt2.id - 1and dt.Item = dt2.item

  SELECT * FROM #DateTest dt

  drop table #datetest

• GSquared

  SSC Guru

  Points: 260824

  More actions

  October 6, 2011 at 11:26 am

  #1391345

  Try this, see if it performs well enough on your actual data volume.

  SELECT *,

  DATEDIFF(DAY, OrderDate,

  ISNULL((SELECT MIN(OrderDate)

  FROM #DateTest AS DT2

  WHERE DT2.Item = #DateTest.Item

  AND DT2.OrderDate > #DateTest.OrderDate),

  GETDATE()))

  FROM #DateTest ;

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• ducksql@gmail.com

  SSCrazy

  Points: 2826

  More actions

  October 6, 2011 at 12:25 pm

  #1391391

  GSquared, this code smokes! Thanks.

  Thanks to you both for your help. I am also going to keep the other code if you don't mind. I might need to incorporate it into something else.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  October 7, 2011 at 6:06 am

  #1391652

  You're welcome.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon