2

Basics

I have a table of patients with visit dates. There can be an arbitrary number of patients each with an arbitrary number of visits. A visit is recorded by saving the date of the visit. Only one visit is recorded per day, so patient+visit is unique for each row in the table.

I would like to extract the following in information from this data using basic SQL queries (as vendor-neutral as possible):

  • List of patients and their corresponding maximum number of visits within any 90-day period
  • List of patients and their corresponding maximum number of visits within any 90-day period, plus the most recent date beginning the 90-day period

I’ve got the first one solved already thanks to these questions that were resolved: Rolling sum / count / average over date interval and Can I do a max(count(*)) in SQL?. But the resulting code seems to work only in PostgreSQL 9.3 in SQL Fiddle and Microsoft Access 2016. I would like to get this working with a portable solution like SQLite for the .NET Framework.

Table Creation Code

CREATE TABLE PatientVisits (
    Patient int NOT NULL,
    Visit date NOT NULL,
    CONSTRAINT PatientVisits_pk PRIMARY KEY (Patient,Visit)
);

INSERT INTO PatientVisits
    (Patient, Visit)
VALUES
    (11111, '2016-02-15 00:00:00'),
    (11111, '2016-03-15 00:00:00'),
    (11111, '2016-04-15 00:00:00'),
    (11111, '2016-04-16 00:00:00'),
    (11111, '2016-04-17 00:00:00'),
    (11111, '2016-07-11 00:00:00'),
    (11111, '2016-07-12 00:00:00'),
    (11111, '2016-07-13 00:00:00'),
    (22222, '2016-02-01 00:00:00'),
    (22222, '2016-04-30 00:00:00'),
    (22222, '2016-05-01 00:00:00'),
    (22222, '2016-05-02 00:00:00'),
    (22222, '2016-07-26 00:00:00'),
    (22222, '2016-07-27 00:00:00'),
    (22222, '2016-07-28 00:00:00'),
    (22222, '2016-09-01 00:00:00'),
    (33333, '2016-01-01 00:00:00'),
    (33333, '2016-02-16 00:00:00'),
    (33333, '2016-03-04 00:00:00'),
    (33333, '2016-03-22 00:00:00'),
    (33333, '2016-04-08 00:00:00'),
    (33333, '2016-04-26 00:00:00'),
    (33333, '2016-05-14 00:00:00'),
    (33333, '2016-09-01 00:00:00'),
    (44444, '2016-04-30 00:00:00'),
    (44444, '2016-08-31 00:00:00'),
    (44444, '2016-09-17 00:00:00'),
    (44444, '2016-10-05 00:00:00'),
    (44444, '2016-10-22 00:00:00'),
    (44444, '2016-11-09 00:00:00'),
    (44444, '2016-11-27 00:00:00'),
    (44444, '2016-12-31 00:00:00'),
    (55555, '2016-04-01 00:00:00'),
    (55555, '2016-06-16 00:00:00'),
    (55555, '2016-06-30 00:00:00'),
    (55555, '2016-07-01 00:00:00'),
    (55555, '2016-07-15 00:00:00'),
    (55555, '2016-08-31 00:00:00'),
    (66666, '2016-07-01 00:00:00'),
    (66666, '2016-09-16 00:00:00'),
    (66666, '2016-09-26 00:00:00'),
    (66666, '2016-10-05 00:00:00'),
    (66666, '2016-10-15 00:00:00'),
    (66666, '2016-12-31 00:00:00'),
    (77777, '2016-01-01 00:00:00'),
    (77777, '2016-05-31 00:00:00'),
    (77777, '2016-06-30 00:00:00'),
    (77777, '2016-07-10 00:00:00'),
    (77777, '2016-07-19 00:00:00'),
    (77777, '2016-07-29 00:00:00'),
    (77777, '2016-08-28 00:00:00'),
    (77777, '2016-12-31 00:00:00'),
    (88888, '2016-01-31 00:00:00'),
    (88888, '2016-02-10 00:00:00'),
    (88888, '2016-02-19 00:00:00'),
    (88888, '2016-02-29 00:00:00'),
    (88888, '2016-03-30 00:00:00'),
    (88888, '2016-05-31 00:00:00'),
    (99999, '2016-05-01 00:00:00'),
    (99999, '2016-07-01 00:00:00'),
    (99999, '2016-08-01 00:00:00'),
    (99999, '2016-10-02 00:00:00'),
    (99999, '2016-10-31 00:00:00'),
    (99999, '2016-11-10 00:00:00'),
    (99999, '2016-11-19 00:00:00'),
    (99999, '2016-11-29 00:00:00');

I get a “create script error” for MySQL 5.5, Oracle 11g R2, MS SQL Server 2008, and MS SQL Server 2014.

Working Queries that I developed

Count the total number of visits for each patient.

This was easy enough…

Code

SELECT DISTINCT Patient, COUNT(Visit) As count
FROM PatientVisits
GROUP BY Patient
ORDER BY Patient

For each visit, get a count of following visits within the next 90 days of the visit date.

This was thanks to Rolling sum / count / average over date interval.

Code

SELECT T0.Patient, T0.Visit, COUNT(T0.Visit) AS count
FROM PatientVisits T0
JOIN PatientVisits T1 USING(Patient)
WHERE T0.Visit <= T1.Visit AND T0.Visit + 90 > T1.Visit
GROUP BY T0.Patient, T0.Visit
ORDER BY T0.Patient, T0.Visit

Results

count is not unique! There may be multiple visit dates with the same number of following visits within their respective 90-day windows.

| patient |                       visit | count |
|---------|-----------------------------|-------|
|   11111 |  February, 15 2016 00:00:00 |     5 |
|   11111 |     March, 15 2016 00:00:00 |     4 |
|   11111 |     April, 15 2016 00:00:00 |     6 |
|   11111 |     April, 16 2016 00:00:00 |     5 |
|   11111 |     April, 17 2016 00:00:00 |     4 |
|   11111 |      July, 11 2016 00:00:00 |     3 |
|   11111 |      July, 12 2016 00:00:00 |     2 |
|   11111 |      July, 13 2016 00:00:00 |     1 |
|   22222 |  February, 01 2016 00:00:00 |     2 |
|   22222 |     April, 30 2016 00:00:00 |     6 |
|   22222 |       May, 01 2016 00:00:00 |     5 |
|   22222 |       May, 02 2016 00:00:00 |     4 |
|   22222 |      July, 26 2016 00:00:00 |     4 |
|   22222 |      July, 27 2016 00:00:00 |     3 |
|   22222 |      July, 28 2016 00:00:00 |     2 |
|   22222 | September, 01 2016 00:00:00 |     1 |
|   33333 |   January, 01 2016 00:00:00 |     4 |
|   33333 |  February, 16 2016 00:00:00 |     6 |
|   33333 |     March, 04 2016 00:00:00 |     5 |
|   33333 |     March, 22 2016 00:00:00 |     4 |
|   33333 |     April, 08 2016 00:00:00 |     3 |
|   33333 |     April, 26 2016 00:00:00 |     2 |
|   33333 |       May, 14 2016 00:00:00 |     1 |
|   33333 | September, 01 2016 00:00:00 |     1 |
|   44444 |     April, 30 2016 00:00:00 |     1 |
|   44444 |    August, 31 2016 00:00:00 |     6 |
|   44444 | September, 17 2016 00:00:00 |     5 |
|   44444 |   October, 05 2016 00:00:00 |     5 |
|   44444 |   October, 22 2016 00:00:00 |     4 |
|   44444 |  November, 09 2016 00:00:00 |     3 |
|   44444 |  November, 27 2016 00:00:00 |     2 |
|   44444 |  December, 31 2016 00:00:00 |     1 |
|   55555 |     April, 01 2016 00:00:00 |     2 |
|   55555 |      June, 16 2016 00:00:00 |     5 |
|   55555 |      June, 30 2016 00:00:00 |     4 |
|   55555 |      July, 01 2016 00:00:00 |     3 |
|   55555 |      July, 15 2016 00:00:00 |     2 |
|   55555 |    August, 31 2016 00:00:00 |     1 |
|   66666 |      July, 01 2016 00:00:00 |     3 |
|   66666 | September, 16 2016 00:00:00 |     4 |
|   66666 | September, 26 2016 00:00:00 |     3 |
|   66666 |   October, 05 2016 00:00:00 |     3 |
|   66666 |   October, 15 2016 00:00:00 |     2 |
|   66666 |  December, 31 2016 00:00:00 |     1 |
|   77777 |   January, 01 2016 00:00:00 |     1 |
|   77777 |       May, 31 2016 00:00:00 |     6 |
|   77777 |      June, 30 2016 00:00:00 |     5 |
|   77777 |      July, 10 2016 00:00:00 |     4 |
|   77777 |      July, 19 2016 00:00:00 |     3 |
|   77777 |      July, 29 2016 00:00:00 |     2 |
|   77777 |    August, 28 2016 00:00:00 |     1 |
|   77777 |  December, 31 2016 00:00:00 |     1 |
|   88888 |   January, 31 2016 00:00:00 |     5 |
|   88888 |  February, 10 2016 00:00:00 |     4 |
|   88888 |  February, 19 2016 00:00:00 |     3 |
|   88888 |  February, 29 2016 00:00:00 |     2 |
|   88888 |     March, 30 2016 00:00:00 |     2 |
|   88888 |       May, 31 2016 00:00:00 |     1 |
|   99999 |       May, 01 2016 00:00:00 |     2 |
|   99999 |      July, 01 2016 00:00:00 |     2 |
|   99999 |    August, 01 2016 00:00:00 |     2 |
|   99999 |   October, 02 2016 00:00:00 |     5 |
|   99999 |   October, 31 2016 00:00:00 |     4 |
|   99999 |  November, 10 2016 00:00:00 |     3 |
|   99999 |  November, 19 2016 00:00:00 |     2 |
|   99999 |  November, 29 2016 00:00:00 |     1 |

SQL Fiddle (PostgreSQL 9.3): http://sqlfiddle.com/#!15/b59fe/1

MySQL 5.6 gives the wrong results.

WebSQL and SQL.js produces no results.

For each patient, get a maximum count of visits within any 90-day period.

This was thanks to: Can I do a max(count(*)) in SQL?.

Code

SELECT PatientVisitCount.Patient, MAX(PatientVisitCount.count)
FROM (SELECT Patient, COUNT(T0.Visit) AS count
      FROM PatientVisits T0
      JOIN PatientVisits T1 USING(Patient)
      WHERE T0.Visit <= T1.Visit AND T0.Visit + 90 > T1.Visit
      GROUP BY T0.Patient, T0.Visit
      ORDER BY T0.Patient, T0.Visit) AS PatientVisitCount
GROUP BY PatientVisitCount.Patient

Results

| patient | max |
|---------|-----|
|   11111 |   6 |
|   22222 |   6 |
|   33333 |   6 |
|   44444 |   6 |
|   55555 |   5 |
|   66666 |   4 |
|   77777 |   6 |
|   88888 |   5 |
|   99999 |   5 |

SQL Fiddle (PostgreSQL 9.3): http://sqlfiddle.com/#!15/b59fe/2

MySQL 5.6 gives the wrong results.

WebSQL and SQL.js produces no results.

This post gave me a hint to solving the second problem (getting the maximum with the visit dates): Advanced Sql Selecting Rows With Max Value From Groups. I have to combine the above two queries somehow, but after much fiddling, I could not get a working solution.

In Microsoft Access 2016, however, the easy solution was just to join the previous two queries using the provided designer, producing an SQL statement like this:

SELECT [Visits in Following 90-day Window Per Visit].*
FROM [Maximum Visits in Any 90-day Window Per Patient] INNER JOIN [Visits in Following 90-day Window Per Visit] ON ([Maximum Visits in Any 90-day Window Per Patient].[Maximum Visits in Any 90-day Window] = [Visits in Following 90-day Window Per Visit].[Visits in Following 90-day Window]) AND ([Visits in Following 90-day Window Per Visit].Patient = [Maximum Visits in Any 90-day Window Per Patient].Patient);

It provided the data I’m looking for:

Patient  Visit      Visits in Following 90-day Window
11111    4/15/2016  6
22222    4/30/2016  6
33333    2/16/2016  6
44444    8/31/2016  6
55555    6/16/2016  5
66666    9/16/2016  4
77777    5/31/2016  6
88888    1/31/2016  5
99999    10/2/2016  5

The data here basically says that for patient 11111, there was a 90-day period beginning on 4/15/2016 within which he came 6 times, and that’s greater than any other 90-day period measured from any other date in the patient’s history.

Still looking for a lightweight solution that is portable. Microsoft Access only works on Windows and requires a Microsoft Office license; I don’t know of any free software that works with Microsoft Access databases.

Improve this question
1
  • Since SQL dialects tend to differ in details, the quest for complete portability is futile.
    – Erwin Brandstetter
    Jun 13, 2016 at 1:18

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.