sql query to find customers who order too frequently?

My database isn't actually customers and orders, it's customers and prescriptions for their eye tests (just in case anyone was wondering why I'd want my customers to make orders less frequently!)

I have a database for a chain of opticians, the prescriptions table has the branch ID number, the patient ID number, and the date they had their eyes tested. Over time, patients will have more than one eye test listed in the database. How can I get a list of patients who have had a prescription entered on the system more than once in six months. In other words, where the date of one prescription is, for example, within three months of the date of the previous prescription for the same patient.

Sample data:

Branch  Patient DateOfTest
1      1       2007-08-12
1      1       2008-08-30
1      1       2008-08-31
1      2       2006-04-15
1      2       2007-04-12

I don't need to know the actual dates in the result set, and it doesn't have to be exactly three months, just a list of patients who have a prescription too close to the previous prescription. In the sample data given, I want the query to return:

Branch   Patient
1       1

This sort of query isn't going to be run very regularly, so I'm not overly bothered about efficiency. On our live database I have a quarter of a million records in the prescriptions table.

From stackoverflow

• On way:

  select d.branch, d.patient
  from   data d
  where exists
  ( select null from data d1
    where  d1.branch = d.branch
    and    d1.patient = d.patient
    and    "difference (d1.dateoftest ,d.dateoftest) < 6 months"
  );
  

  This part needs changing - I'm not familiar with SQL Server's date operations:

  "difference (d1.dateoftest ,d.dateoftest) < 6 months"
  

• Something like this

  select p1.branch, p1.patient
  from prescription p1, prescription p2
  where p1.patient=p2.patient
  and p1.dateoftest > p2.dateoftest
  and datediff('day', p2.dateoftest, p1.dateoftest) < 90;
  

  should do... you might want to add

  and p1.dateoftest > getdate()
  

  to limit to future test prescriptions.

  Dan : You sir, are a scholar and a gentleman :-)

  tehvan : Thanks :) but what did i earn the gentleman part for?

  AnthonyWJones : +1 Simple and effective. However not very effecient since indexes can't be very helpful this will create large matrix before being filtered down.

• This one will efficiently use an index on (Branch, Patient, DateOfTest) which you of course should have:

  SELECT Patient, DateOfTest, pDate
  FROM (
    SELECT (
      SELECT TOP 1 DateOfTest AS last
      FROM Patients pp
      WHERE pp.Branch = p.Branch
        AND pp.Patient = p.Patient
        AND pp.DateOfTest BETWEEN DATEADD(month, -3, p.DateOfTest) AND p.DateOfTest
      ORDER BY 
        DateOfTest DESC
      ) pDate
    FROM Patients p
  ) po
  WHERE pDate IS NOT NULL
  

  AnthonyWJones : +1 Efficient. However its a little complicated, its not all that clear what its doing.

  Quassnoi : For each prescription case, it selects the previous case within 3 months, if any, and filters out those who had the cases.

• Self-join:

  select a.branch, a.patient
     from prescriptions a
     join prescriptions b
     on     a.branch = b.branch 
        and a.patient = b.patient
        and a.dateoftest > b.dateoftest
        and a.dateoftest - b.dateoftest < 180
  group by a.branch, a.patient
  

  This assumes you want patients who visit the same branch twice. If you don't, take out the branch part.

• SELECT Branch
        ,Patient
    FROM (SELECT Branch
                ,Patient
                ,DateOfTest
                ,DateOfOtherTest
            FROM Prescriptions P1
            JOIN Prescriptions P2
              ON P2.Branch = P1.Branch
             AND P2.Patient = P2.Patient
             AND P2.DateOfTest <> P1.DateOfTest
         ) AS SubQuery
    WHERE DATEDIFF(day, SubQuery.DateOfTest, SubQuery.DateOfOtherTest) < 90