Geek Answers Handbook

Sql query to find customers who order too often?

My database is not really customers and orders, its customers and recipes for their eye tests (just in case someone wondered why I want my customers to order less often!)

I have a database for an optics chain, the recipe table has a branch ID, a patient ID, and the date they checked the eyes. Over time, patients will have more than one eye test indicated in the database. How can I get a list of patients who have had a prescription entered into the system more than once every six months. In other words, if the date of one prescription, for example, within three months from the date of the previous vacation 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 do not need to know the actual dates in the result set, and this should not be exactly three months, but simply a list of patients whose prescription is too close to the previous prescription. In the given sample data, I want the request to be returned:

 Branch Patient 1 1

Such a request will not be run very regularly, so I'm not too worried about efficiency. In our live database, I have a quarter of a million entries in the recipe table.

+4
source share
5 answers

Something like that

 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 ... you can add

 and p1.dateoftest > getdate()

to limit future test prescriptions.

+7
source

This will effectively use the index on (Branch, Patient, DateOfTest) , which of course you 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
+1
source

On my 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 to be changed - I am not familiar with SQL Server date operations:

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

auto 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 suggests that you want patients to visit the same branch twice. If you do not, take out part of the branch.

0
source 
 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
0
source

More articles:


All Articles