sql query to find customers who order too frequently?

Question

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.

Answer 1

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"

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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