compare dates in rows of a table

• tndanley

  Grasshopper

  Points: 11

  More actions

  April 8, 2015 at 2:01 pm

  #398298

  I have the following information in a table. What I would like to do is pull out all the visits for each customer that are less than 30 days apart. I think a Cursor (or two) i the way to accomplish this but it's been awhile. Can anyone help me get started with this.

  Customer# VisitDate

  9082012-07-28 00:00:00.000

  9082013-09-20 00:00:00.000

  9082013-12-23 00:00:00.000

  9082014-01-10 00:00:00.000

  9082014-01-27 00:00:00.000

  9082014-02-16 00:00:00.000

  9082014-05-21 00:00:00.000

  9082014-05-30 00:00:00.000

  9082014-10-01 00:00:00.000

  9082015-02-28 00:00:00.000

  9082015-03-22 00:00:00.000

  9272012-02-16 00:00:00.000

  9272014-12-14 00:00:00.000

  9272014-12-23 00:00:00.000

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  April 8, 2015 at 2:19 pm

  #1789695

  This might get you started.

  WITH SampleData(CustomerID, VisitDate) AS(

  SELECT *

  FROM (VALUES(908,'2012-07-28 00:00:00.000'),

  (908,'2013-09-20 00:00:00.000'),

  (908,'2013-12-23 00:00:00.000'),

  (908,'2014-01-10 00:00:00.000'),

  (908,'2014-01-27 00:00:00.000'),

  (908,'2014-02-16 00:00:00.000'),

  (908,'2014-05-21 00:00:00.000'),

  (908,'2014-05-30 00:00:00.000'),

  (908,'2014-10-01 00:00:00.000'),

  (908,'2015-02-28 00:00:00.000'),

  (908,'2015-03-22 00:00:00.000'),

  (927,'2012-02-16 00:00:00.000'),

  (927,'2014-12-14 00:00:00.000'),

  (927,'2014-12-23 00:00:00.000'))x(CustomerID, VisitDate)

  ),

  cteRows AS(

  SELECT *, ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY VisitDate) rn

  FROM SampleData

  )

  SELECT *, DATEDIFF( dd, b.VisitDate, a.VisitDate)

  FROM cteRows a

  LEFT

  JOIN cteRows b ON a.CustomerID = b.CustomerID

  AND a.rn = b.rn + 1

  AND a.VisitDate < DATEADD( dd, 30, b.VisitDate)

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Dwain Camps

  SSC Guru

  Points: 86978

  More actions

  April 8, 2015 at 7:29 pm

  #1789755

  I believe that this is also an option:

  WITH SampleData(CustomerID, VisitDate) AS(

  SELECT *

  FROM (VALUES(908,CAST('2012-07-28 00:00:00.000' AS DATETIME)),

  (908,'2013-09-20 00:00:00.000'),

  (908,'2013-12-23 00:00:00.000'),

  (908,'2014-01-10 00:00:00.000'),

  (908,'2014-01-27 00:00:00.000'),

  (908,'2014-02-16 00:00:00.000'),

  (908,'2014-05-21 00:00:00.000'),

  (908,'2014-05-30 00:00:00.000'),

  (908,'2014-10-01 00:00:00.000'),

  (908,'2015-02-28 00:00:00.000'),

  (908,'2015-03-22 00:00:00.000'),

  (927,'2012-02-16 00:00:00.000'),

  (927,'2014-12-14 00:00:00.000'),

  (927,'2014-12-23 00:00:00.000'))x(CustomerID, VisitDate)

  )

  SELECT *

  FROM SampleData a

  WHERE EXISTS

  (

  SELECT 1

  FROM SampleData b

  WHERE a.CustomerID = b.CustomerID AND

  a.VisitDate <> b.VisitDate AND

  ABS(DATEDIFF(day, a.VisitDate, b.VisitDate)) <= 30

  );

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St