Link to home
Start Free TrialLog in
Avatar of KenBurke
KenBurkeFlag for United States of America

asked on

Trying to query a list of vaccinations

Hi Experts.

I am a pediatrician trying to use MySQL to analyze immunization records (although I may need to move my work to SQL Server). I am still a novice at SQL.

A bit of background may help. In order to achieve immunity to a given disease, a patient generally needs to receive a series of doses of a vaccine against that disease.
Vaccine doses must be given at the correct ages, and the time intervals between doses must meet prescribed minimums in order for immunity to be achieved. Recently the US Centers for Disease Control and Prevention published a formal schema to guide developers in writing software for analyzing immunization records. The goal of the schema is to help developers write software that helps clinicians know where a patient stands in their immunization process and that guides clinicians in deciding which additional vaccine doses will be needed going forward and when those additional doses will need to be given. The schema defines a series of what it calls "target doses" for each vaccine. Each "target dose" in the series has specific criteria such the patient's age when it was given and the minimum time since the previous dose was given. The vaccine doses actually given to a patient can be compared to the target doses to see whether they meet the criteria for the target doses. Doses given are analyzed in the order they were given. The earliest dose given that meets criteria for Target Dose 1 is said to have satisfied Target Dose 1. Subsequent doses given are analyzed to see which is the next earliest dose given that satisfies Target Dose 2, and so on, until either all target doses in the series have been satisfied or there are no more doses given left to analyze. Each dose given can satisfy at most 1 target dose. Once all of a patient's target doses have been satisfied, then that immunization series is considered to be complete.

So, my first task has been to take a list of vaccine doses that have been given to a patient and see how they match up with the target doses. Each dose given may happen to meet the criteria for more than one target dose, but an individual dose given can only count towards satisfying one of the target doses in the series.

Now, suppose I have this hypothetical list of 3 target doses

TargetDose1, <criteria_1>
TargetDose2, <criteria_2>
TargetDose3, <criteria_3>

where <criteria_n> is actually several fields that collectively give the criteria for satisfying the nth Target Dose.

And this hypothetical list of 4 doses given

DoseGiven1
DoseGiven2
DoseGiven3
DoseGiven4

Where DoseGiven1 < DoseGiven2 < DoseGiven3 < DoseGiven4
The doses given could be in the form of date fields or integers.

I can use the criteria to generate a cross product that lists every target dose along with every dose given that potentially could satisfy that target dose.
Suppose that using the criteria, I get this result set:

TargetDose1, DoseGiven1
TargetDose1, DoseGiven2
TargetDose1, DoseGiven3
TargetDose1, DoseGiven4
TargetDose2, DoseGiven3
TargetDose2, DoseGiven4
TargetDose3, DoseGiven3
TargetDose3, DoseGiven4

I need to narrow that result set to this one:

TargetDose1, DoseGiven1
TargetDose2, DoseGiven3
TargetDose3, DoseGiven4

or, equivalently, to this one:

TargetDose1, DoseGiven1, Yes
TargetDose1, DoseGiven2, No
TargetDose1, DoseGiven3, No
TargetDose1, DoseGiven4, No
TargetDose2, DoseGiven3, Yes
TargetDose2, DoseGiven4, No
TargetDose3, DoseGiven3, No
TargetDose3, DoseGiven4, Yes

Note that in this example, DoseGiven2 does not satisfy any of the target doses. It meets criteria for satisfying Target Dose 1, but DoseGiven1 is assigned to Target Dose 1. Target Dose 2 is satisfied by DoseGiven 3 but not by DoseGiven2.

I have spent many hours trying to figure this out. I would appreciate it if anyone could help.
Avatar of Dale Fye
Dale Fye
Flag of United States of America image
It would help considerably if you could provide some sample data, including the criteria.  It would be helpful if you provide the sample in the form of a database or XL spreadsheet.
Avatar of appari
appari
Flag of India image
>>Suppose that using the criteria, I get this result set:

>>TargetDose1, DoseGiven1

if you can write SQL to get the above intermediate result set you mentioned in your question, you can try the following:

Select yourcolumnList from (Select yourcolumnList , row_number() over(partition by TargetDose order by DoseGiven) as rowID from yourtablesjoinsandconditions) As A where  rowID = 1

content in bold italics is the part i added. this works in SQLServer 2005 onwards. In mySQL check if you have similar function available or not.
Avatar of KenBurke
KenBurke
Flag of United States of America image

ASKER

appari,
I think the solution you are proposing uses a window function. These do not exist yet in MySQL. Can you help me to construct a similar query without the use of window functions? Thanks.
Avatar of britpopfan74
britpopfan74
Flag of United States of America image
Hi Ken,

I've been trying to do something similar re: vaccination compliance in SQL...I don't know if this can be of any help



https://www.experts-exchange.com/questions/27836451/SQL-HELP-Best-method-to-determine-vaccination-compliance.html
Avatar of KenBurke
KenBurke
Flag of United States of America image

ASKER

appari,

Thanks for your reply. I looked at the links you mentioned in your last post. I think the problem is that finding group minimums will yield

TargetDose1, DoseGiven1
TargetDose2, DoseGiven3
TargetDose3, DoseGiven3

instead of

TargetDose1, DoseGiven1
TargetDose2, DoseGiven3
TargetDose3, DoseGiven4

Note the difference in the last line of output.
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Ken,

I guess I would ask the question why a sql novice was trying to develop a solution to meet regulated health requirements. My concern is that making decisions based on your software could put you at risk of making mistakes in your "day job".

Putting aside the reservation above I would find it useful to know what the formal guide is (i.e. "the US Centers for Disease Control and Prevention published a formal schema"). Can you provide a link to this?

e.g. I came across HL7 Version 2.5.1 Implementation Guide for Immunization Messaging is this relevant?

[btw: have you looked for any open source solutions to the cdc specification? e.g. http://www.healthcarefreeware.com/pub_hlth.htm]

Answering your specific question will very largely depend on the tables structures and data, it would certainly help anyone trying to answer you if you could supply some samples (in Excel probably).
Avatar of KenBurke
KenBurke
Flag of United States of America image

ASKER

Portletpaul,

As to why a novice like me would try this, I can only say that no one else has published or offered for sale a tool that I can use. We use an electronic health record system in my private pediatric practice. Of the many vendors of electronic health records, only one seems to have built a module for decision support for immunizations, and it is not the one we happen to have. Switching systems is not a realistic option. It is great to have more vaccines to use, but the increased number of vaccines now means that figuring out which immunizations a child needs has become time-consuming and error-prone. We already make mistakes in clinical practice. My hope is that better tools will reduce errors and save time. If I can produce some queries that are useful I would be glad to share them with other caregivers. Ideally the SQL code might be something I could show my electronic record vendor to get them interested in having real database developers work on this problem.

The CDC's schema can be found at
http://www.cdc.gov/vaccines/programs/iis/interop-proj/cds.html
and
http://www.cdc.gov/vaccines/programs/iis/interop-proj/downloads/logic-spec-acip-rec.pdf

I looked at the HL-7 specification. I think this is a standard for transmitting health care information rather than a means of analyzing information.

I have looked for open source solutions to the CDC's specification and have not found anything I can use. The closest thing I could find is the CDC's CoCASA tool. I have tried running the tool but the reports it generates do not fit well with my practice's needs.

As for sample tables and data, I will need some more time to put them together for this forum.

Thanks for your interest.
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Ken,

Thanks for the full and frank reply. I'll do some reading but doubt I can offer sufficient help to resolve your needs. Below are some ideas (off the top of my head and in no particular order).

submit a less technical question to EE (see 'a new question?')
approach your professional associations, push them to push others
suggest a "challenge" for some local universities? (perhaps a small prize?)
pester your incumbent system vendor for their plans

It would definitely seem desirable that this decision support information would be fully integrated into "the patient record", but until that nirvana occurs you should try to leverage some of your existing data if at all possible (e.g. you would not want to re-key in each patient). If this is true, then to a degree what information you can access from your current system could play a big part in the overall solution design. Does your incumbent system allow sql access? (and do you have permission to access that data that way?)

By the way - what are CDC doing about this - will they produce any software?

'a new question?':
perhaps a more generic question to EE could be posed, with some of the details you have just provided - seeking possible existing partial solutions and/or further ideas.

btw: there's a mini-guide for folks like me who hate reading tech doco
http://www.cdc.gov/vaccines/programs/iis/interop-proj/downloads/cdsi-brochure.pdf
Avatar of britpopfan74
britpopfan74
Flag of United States of America image
Ken,

I don't know your situation; however, you may be connected with HEDIS or other "Quality Improvement"/"Pay 4 Performance" program where you are...

I have worked on some of these projects and there is some great software out that will automate a lot of the vaccination combinations at every age level to make chart review easy for reviewers out of an EHR and then non-challenging to integrate into SQL to do further analysis.

Please feel free to reach out if you have any questions. Regards, Karen
Avatar of KenBurke
KenBurke
Flag of United States of America image

ASKER

PortletPaul,
Currently the easiest way for me to get at the immunization data in the electronic medical record system at my office is to use Cognos Query to generate a CSV file that lists immunizations given. I have imported such a CSV file into MySQL. I would hope to develop the query further before approaching my EHR vendor about directly querying the EHR database. The EHR system uses Microsoft SQL Server as its database management system.

I think I have come up with an idea about how to solve the specific problem I posed here. Sorry to be vague about this, but I have not had the time to actually write the code. Writing SQL is still a very slow process for me.

Karen,
Can you point me to some of the software available for analyzing immunization records? I have tried CoCASA and found it tricky to use.

Thanks to all.
Avatar of PortletPaul
PortletPaul
Flag of Australia image
ok then, at least you have a method for gathering base data without keying errors and effort...

My guess is you will "replace" the "immunizations given" data via the csv, which possibly means this data won't be "normalised"  (i.e. there will be some repetition in some columns) Hopefully you will get from that query something unique per row which remains "stable" (like a patient identity code).

In your own database you will be building tables that you maintain for the "criteria" (you may have imported this at some point); then you will go about comparing the predictive "criteria" against the real-world immunizations to seek "candidates for further immunization" and/or failures to meet the criteria.

Whilst I realize in no way would you reveal medical history, I/we cannot assist in your sql endeavours with tables/fields/types and at least sample data. i.e. Going back to your original question it is still unanswerable right now. I'd suggest - going forward - that you have "purified" sample data available for sql related questions plus table/field/type information - AND you break-up question into small chunks.

Hopefully this makes sense. Not sure what you want to do with this Q - I'll monitor and drop by if it helps - but new (smaller) questions may attract new participation and expertise. Good Luck with it!!

I guess you have already asked your colleagues if their partners/friends/children/nephews/nieces are geekish wizards who would love to help? :)
Avatar of britpopfan74
britpopfan74
Flag of United States of America image
Hi Ken,

There are several software packages available; the one I am most familiar with is through VIPS and is called MedMeasures.

Here is a link to their website and they have a demo; please let me know if you have any other questions!

Health Payor Solutions
Avatar of KenBurke
KenBurke
Flag of United States of America image

ASKER

In case anyone is still interested, I have devised a solution that I think works. If anyone can streamline or otherwise improve the code, I would be happy to learn. The code is intended only as a prototype. Its purpose is to match vaccine doses given with so called Target Doses as described in my earlier posts. There are 2 tables so far. The first table, VaccinesGiven, has 5 fields. It is a list of doses of vaccines given, described as follows:

mysql> describe VaccinesGiven;
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| CVX          | char(3)  | YES  |     | NULL    |       | A 2- or 3-digit code that identifies the vaccine
| ImmGivenDate | date     | YES  |     | NULL    |       | Date when dose of vaccine given
| PtBirthDate  | date     | YES  |     | NULL    |       | Patient's birth date
| PatientSex   | char(6)  | YES  |     | NULL    |       | Patient's sex
| PtID         | char(10) | YES  |     | NULL    |       | Unique identifier for each patient
+--------------+----------+------+-----+---------+-------+

Open in new window


The second table, HepB3DoseSeries, describes the requirements for the timing of doses of hepatitis B vaccine, and is described as follows:

mysql> describe HepB3DoseSeries;
+-------------------------------+-------------+------+-----+---------+-------+
| Field                         | Type        | Null | Key | Default | Extra |
+-------------------------------+-------------+------+-----+---------+-------+
| TargetDose                    | tinyint(4)  | YES  |     | NULL    |       | An integer: 1, 2, or 3 in the case of hepatitis B vaccine
| AbsMinAge                     | smallint(6) | YES  |     | NULL    |       | (not used here)
| MinAge                        | smallint(6) | YES  |     | NULL    |       | Minimum age, in days, at which a dose must be administered to satisfy the given Target Dose
| AbsMinIntervalLastDose        | smallint(6) | YES  |     | NULL    |       | (not used here)
| MinIntervalLastDose           | smallint(6) | YES  |     | NULL    |       | (not used here)
| AbsMinIntervalFromTargetDose1 | smallint(6) | YES  |     | NULL    |       | (not used here)
| MinIntervalFromTargetDose1    | smallint(6) | YES  |     | NULL    |       | (not used here)
+-------------------------------+-------------+------+-----+---------+-------+

Open in new window

(The queries I have written so far all look only at doses with CVX code 08, which happens to be for hepatitis B vaccine.)  I have oversimplified the queries by only having them look at the TargetDose and MinAge fields of the HepB3DoseSeries table, so the queries only look at whether each dose was given at the correct age. They ignore the intervals between the doses given. Also note that the patient's sex is irrelevant for hepatitis B vaccine purposes. The queries look at one patient identified as 00034-002.

Below are contents of the tables. VaccinesGiven is a list of vaccines given to 4 fictitious patients:

mysql> select * from VaccinesGiven;
+------+--------------+-------------+------------+-----------+
| CVX  | ImmGivenDate | PtBirthDate | PatientSex | PtID      |
+------+--------------+-------------+------------+-----------+
| 03   | 2001-01-28   | 2000-01-26  | F          | 00079-002 |
| 03   | 2005-04-17   | 2000-01-26  | F          | 00079-002 |
| 08   | 2000-01-26   | 2000-01-26  | F          | 00079-002 |
| 08   | 2000-03-31   | 2000-01-26  | F          | 00079-002 |
| 08   | 2000-10-29   | 2000-01-26  | F          | 00079-002 |
| 10   | 2000-03-31   | 2000-01-26  | F          | 00079-002 |
| 10   | 2000-06-02   | 2000-01-26  | F          | 00079-002 |
| 10   | 2000-07-28   | 2000-01-26  | F          | 00079-002 |
| 10   | 2005-01-28   | 2000-01-26  | F          | 00079-002 |
| 100  | 2000-10-29   | 2000-01-26  | F          | 00079-002 |
| 100  | 2000-12-10   | 2000-01-26  | F          | 00079-002 |
| 100  | 2001-06-03   | 2000-01-26  | F          | 00079-002 |
| 114  | 2011-05-07   | 2000-01-26  | F          | 00079-002 |
| 115  | 2011-05-07   | 2000-01-26  | F          | 00079-002 |
| 20   | 2000-03-31   | 2000-01-26  | F          | 00079-002 |
| 20   | 2000-06-02   | 2000-01-26  | F          | 00079-002 |
| 20   | 2000-07-28   | 2000-01-26  | F          | 00079-002 |
| 20   | 2001-06-03   | 2000-01-26  | F          | 00079-002 |
| 20   | 2005-04-17   | 2000-01-26  | F          | 00079-002 |
| 21   | 2001-03-28   | 2000-01-26  | F          | 00079-002 |
| 21   | 2007-06-22   | 2000-01-26  | F          | 00079-002 |
| 48   | 2000-03-31   | 2000-01-26  | F          | 00079-002 |
| 48   | 2000-06-02   | 2000-01-26  | F          | 00079-002 |
| 48   | 2000-07-28   | 2000-01-26  | F          | 00079-002 |
| 48   | 2001-05-28   | 2000-01-26  | F          | 00079-002 |
| 83   | 2011-06-03   | 2000-01-26  | F          | 00079-002 |
| 83   | 2012-07-28   | 2000-01-26  | F          | 00079-002 |
| 03   | 2005-09-13   | 2004-09-11  | F          | 00079-003 |
| 03   | 2012-07-28   | 2004-09-11  | F          | 00079-003 |
| 08   | 2004-09-12   | 2004-09-11  | F          | 00079-003 |
| 08   | 2004-11-12   | 2004-09-11  | F          | 00079-003 |
| 08   | 2005-01-16   | 2004-09-11  | F          | 00079-003 |
| 08   | 2005-04-16   | 2004-09-11  | F          | 00079-003 |
| 10   | 2004-11-12   | 2004-09-11  | F          | 00079-003 |
| 10   | 2005-01-16   | 2004-09-11  | F          | 00079-003 |
| 10   | 2005-04-16   | 2004-09-11  | F          | 00079-003 |
| 10   | 2011-06-03   | 2004-09-11  | F          | 00079-003 |
| 100  | 2004-11-12   | 2004-09-11  | F          | 00079-003 |
| 100  | 2005-01-16   | 2004-09-11  | F          | 00079-003 |
| 100  | 2005-04-16   | 2004-09-11  | F          | 00079-003 |
| 100  | 2005-09-13   | 2004-09-11  | F          | 00079-003 |
| 140  | 2005-09-13   | 2004-09-11  | F          | 00079-003 |
| 140  | 2005-10-14   | 2004-09-11  | F          | 00079-003 |
| 20   | 2004-11-12   | 2004-09-11  | F          | 00079-003 |
| 20   | 2005-01-16   | 2004-09-11  | F          | 00079-003 |
| 20   | 2005-04-16   | 2004-09-11  | F          | 00079-003 |
| 20   | 2006-01-10   | 2004-09-11  | F          | 00079-003 |
| 20   | 2011-06-03   | 2004-09-11  | F          | 00079-003 |
| 21   | 2006-03-12   | 2004-09-11  | F          | 00079-003 |
| 21   | 2012-07-28   | 2004-09-11  | F          | 00079-003 |
| 48   | 2004-11-12   | 2004-09-11  | F          | 00079-003 |
| 48   | 2005-01-16   | 2004-09-11  | F          | 00079-003 |
| 48   | 2005-04-16   | 2004-09-11  | F          | 00079-003 |
| 48   | 2006-01-10   | 2004-09-11  | F          | 00079-003 |
| 83   | 2011-06-03   | 2004-09-11  | F          | 00079-003 |
| 83   | 2012-07-28   | 2004-09-11  | F          | 00079-003 |
| 03   | 2012-07-22   | 2011-04-14  | F          | 00023-002 |
| 08   | 2011-04-16   | 2011-04-14  | F          | 00023-002 |
| 08   | 2011-06-18   | 2011-04-14  | F          | 00023-002 |
| 08   | 2011-10-29   | 2011-04-14  | F          | 00023-002 |
| 10   | 2011-06-18   | 2011-04-14  | F          | 00023-002 |
| 100  | 2011-06-18   | 2011-04-14  | F          | 00023-002 |
| 119  | 2011-06-18   | 2011-04-14  | F          | 00023-002 |
| 119  | 2011-08-20   | 2011-04-14  | F          | 00023-002 |
| 120  | 2011-09-11   | 2011-04-14  | F          | 00023-002 |
| 120  | 2011-10-29   | 2011-04-14  | F          | 00023-002 |
| 133  | 2011-09-11   | 2011-04-14  | F          | 00023-002 |
| 133  | 2011-10-29   | 2011-04-14  | F          | 00023-002 |
| 133  | 2012-07-22   | 2011-04-14  | F          | 00023-002 |
| 140  | 2011-10-29   | 2011-04-14  | F          | 00023-002 |
| 140  | 2011-11-29   | 2011-04-14  | F          | 00023-002 |
| 140  | 2012-10-28   | 2011-04-14  | F          | 00023-002 |
| 20   | 2011-06-18   | 2011-04-14  | F          | 00023-002 |
| 21   | 2012-04-17   | 2011-04-14  | F          | 00023-002 |
| 48   | 2011-06-18   | 2011-04-14  | F          | 00023-002 |
| 48   | 2012-07-22   | 2011-04-14  | F          | 00023-002 |
| 83   | 2012-04-17   | 2011-04-14  | F          | 00023-002 |
| 83   | 2012-10-22   | 2011-04-14  | F          | 00023-002 |
| 03   | 2012-11-21   | 2011-08-20  | F          | 00034-002 |
| 08   | 2011-08-20   | 2011-08-20  | F          | 00034-002 |
| 08   | 2011-08-20   | 2011-08-20  | F          | 00034-002 |
| 08   | 2011-08-30   | 2011-08-20  | F          | 00034-002 |
| 08   | 2011-08-31   | 2011-08-20  | F          | 00034-002 |
| 08   | 2012-02-18   | 2011-08-20  | F          | 00034-002 |
| 08   | 2013-02-19   | 2011-08-20  | F          | 00034-002 |
| 119  | 2011-10-18   | 2011-08-20  | F          | 00034-002 |
| 119  | 2011-12-30   | 2011-08-20  | F          | 00034-002 |
| 120  | 2011-10-18   | 2011-08-20  | F          | 00034-002 |
| 120  | 2011-12-30   | 2011-08-20  | F          | 00034-002 |
| 120  | 2012-02-19   | 2011-08-20  | F          | 00034-002 |
| 133  | 2011-10-18   | 2011-08-20  | F          | 00034-002 |
| 133  | 2011-12-30   | 2011-08-20  | F          | 00034-002 |
| 133  | 2012-02-19   | 2011-08-20  | F          | 00034-002 |
| 133  | 2012-11-21   | 2011-08-20  | F          | 00034-002 |
| 140  | 2012-02-19   | 2011-08-20  | F          | 00034-002 |
| 140  | 2012-03-27   | 2011-08-20  | F          | 00034-002 |
| 140  | 2012-08-28   | 2011-08-20  | F          | 00034-002 |
| 20   | 2013-02-20   | 2011-08-20  | F          | 00034-002 |
| 21   | 2012-08-28   | 2011-08-20  | F          | 00034-002 |
| 49   | 2012-11-21   | 2011-08-20  | F          | 00034-002 |
| 83   | 2012-08-28   | 2011-08-20  | F          | 00034-002 |
+------+--------------+-------------+------------+-----------+
101 rows in set (0.00 sec)

mysql> select * from HepB3DoseSeries;
+------------+-----------+--------+------------------------+---------------------+-------------------------------+----------------------------+
| TargetDose | AbsMinAge | MinAge | AbsMinIntervalLastDose | MinIntervalLastDose | AbsMinIntervalFromTargetDose1 | MinIntervalFromTargetDose1 |
+------------+-----------+--------+------------------------+---------------------+-------------------------------+----------------------------+
|          1 |         0 |      0 |                   NULL |                NULL |                          NULL |                       NULL |
|          2 |        24 |     28 |                     24 |                  28 |                          NULL |                       NULL |
|          3 |       164 |    168 |                     52 |                  56 |                           108 |                        112 |
+------------+-----------+--------+------------------------+---------------------+-------------------------------+----------------------------+
3 rows in set (0.00 sec)

Open in new window

Following are 3 queries in increasing order of complexity. Each query is followed by its result set. The problem to be solved here is to take a list of dates of doses given and match them with Target Doses. In this case there are 3 Target Doses to be satisfied. If vaccine doses have all been given at the correct times, then Dose Given 1 will match with Target Dose 1, Dose Given 2 will match with Target Dose 2, and Dose Given 3 will match with Target Dose 3. On the other hand, a dose may be given too soon, in which case it must excluded from the list of Doses Given that have corresponding Target Doses. Another constraint that follows from the rules is that the DoseGivenNumber must be less than or equal to the number of Target Doses that it could possibly satisfy. This is because Target Doses are satisfied sequentially. For example, Target Dose 2 cannot be satisfied until Target Dose 1 has been satisfied by a previous Dose Given. Finally, any Dose Given can satisfy at most one Target Dose.

In Query03 and Query04 a calculated field called DosePrematurity appears. The idea is to compare the DoseGivenNumber with the highest value of Target Dose number that the Dose Given could potentially satisfy. Ideally, for DoseGivenNumber = 2, for example, the highest value of Target Dose satisfied would be 2. If it is only 1, then that dose was given prematurely by 1 Target Dose. Similarly, for DoseGivenNumber = 3, the highest value of Target Dose satisfied should be 3. If it is only 1, then that dose was given prematurely by 2 Target Doses. By calculating a running maximum for the DosePrematurity value for each Dose Given, Query04 assigns the correct dose given to the correct Target dose. For each Dose Given, the Target Dose is assigned to be the DoseGivenNumber minus the running maximum of DosePrematurity. In Query04 the fields of interest to me are EarliestDate (when the dose was given), DoseGivenNumber, and TargetDoseMatch.

Thanks in advance for any more help.

Query01:

SELECT
  t1.CVX,
  t1.ImmGivenDate,
  t1.PtBirthDate,
  t1.PatientSex,
  t1.PtID,
  COUNT(t2.ImmGivenDate) AS DoseGivenNumber
from 
    (SELECT DISTINCT * FROM VaccinesGiven
    WHERE CVX = 08
    AND PtID = '00034-002') t1,
    (SELECT DISTINCT * FROM VaccinesGiven
    WHERE CVX = 08
    AND PtID = '00034-002') t2
    WHERE t1.ImmGivenDate >= t2.ImmGivenDate
    GROUP BY t1.ImmGivenDate
    ORDER BY t1.ImmGivenDate;

+------+--------------+-------------+------------+-----------+-----------------+
| CVX  | ImmGivenDate | PtBirthDate | PatientSex | PtID      | DoseGivenNumber |
+------+--------------+-------------+------------+-----------+-----------------+
| 08   | 2011-08-20   | 2011-08-20  | F          | 00034-002 |               1 |
| 08   | 2011-08-30   | 2011-08-20  | F          | 00034-002 |               2 |
| 08   | 2011-08-31   | 2011-08-20  | F          | 00034-002 |               3 |
| 08   | 2012-02-18   | 2011-08-20  | F          | 00034-002 |               4 |
| 08   | 2013-02-19   | 2011-08-20  | F          | 00034-002 |               5 |
+------+--------------+-------------+------------+-----------+-----------------+
5 rows in set (0.00 sec)



/*Query03: This query finds the maximum TargetDose for each DoseGivenNumber.*/
SELECT 
  t3.CVX,
  t3.ImmGivenDate
  t3.PtBirthDate,
  DATEDIFF(t3.ImmGivenDate, t3.PtBirthDate) AS Elapsed,
  t3.PtID,
  t3.DoseGivenNumber,
  MAX(t4.TargetDose),
  t3.DoseGivenNumber - MAX(t4.TargetDose) AS DosePrematurity
FROM
(SELECT
    t1.CVX,
    t1.ImmGivenDate,
    t1.PtBirthDate,
    t1.PtID,
    COUNT(t2.ImmGivenDate) AS DoseGivenNumber
       FROM 
      (SELECT DISTINCT * FROM VaccinesGiven
       WHERE CVX = 08
       AND PtID = '00034-002') t1,
      (SELECT DISTINCT * FROM VaccinesGiven
       WHERE CVX = 08
       AND PtID = '00034-002') t2
   WHERE t1.ImmGivenDate >= t2.ImmGivenDate
   GROUP BY t1.ImmGivenDate, t1.CVX, t1.PtBirthDate, t1.PtID
) t3,
HepB3DoseSeries t4
WHERE DATEDIFF(t3.ImmGivenDate, t3.PtBirthDate) >= t4.MinAge 
AND t3.DoseGivenNumber >= t4.TargetDose
GROUP BY t3.DoseGivenNumber, t3.CVX, t3.ImmGivenDate, t3.PtBirthDate, t3.PtID, Elapsed;

+------+--------------+-------------+---------+-----------+-----------------+--------------------+-----------------+
| CVX  | ImmGivenDate | PtBirthDate | Elapsed | PtID      | DoseGivenNumber | MAX(t4.TargetDose) | DosePrematurity |
+------+--------------+-------------+---------+-----------+-----------------+--------------------+-----------------+
| 08   | 2011-08-20   | 2011-08-20  |       0 | 00034-002 |               1 |                  1 |               0 |
| 08   | 2011-08-30   | 2011-08-20  |      10 | 00034-002 |               2 |                  1 |               1 |
| 08   | 2011-08-31   | 2011-08-20  |      11 | 00034-002 |               3 |                  1 |               2 |
| 08   | 2012-02-18   | 2011-08-20  |     182 | 00034-002 |               4 |                  3 |               1 |
| 08   | 2013-02-19   | 2011-08-20  |     549 | 00034-002 |               5 |                  3 |               2 |
+------+--------------+-------------+---------+-----------+-----------------+--------------------+-----------------+
5 rows in set (0.00 sec)


/*Query04 produces a list of given doses with the matching Target Doses. Extraneous doses are excluded.*/

SELECT
t9.CVX,
MIN(t9.ImmGivenDate) AS EarliestDate,
t9.PtBirthDate,
t9.Elapsed,
t9.PtID,
t9.DoseGivenNumber,
t9.MaxTargetDose,
t9.DosePrematurity,
t9.TargetDoseMatch
FROM
  (SELECT 
  t7.CVX,
  t7.ImmGivenDate,
  t7.PtBirthDate,
  t7.Elapsed,
  t7.PtID,
  t7.DoseGivenNumber,
  t7.MaxTargetDose,
  t7.DosePrematurity,
  t7.DoseGivenNumber - (SELECT
    MAX(t8.DosePrematurity)
    FROM
/*Query 03: This query started out as Query 02. It finds the maximum TargetDose for each DoseGivenNumber.*/
    (SELECT 
      t3.CVX,
      t3.ImmGivenDate,
      t3.PtBirthDate,
      DATEDIFF(t3.ImmGivenDate, t3.PtBirthDate) AS Elapsed,
      t3.PtID,
      t3.DoseGivenNumber,
      MAX(t4.TargetDose) AS MaxTargetDose,
      t3.DoseGivenNumber - MAX(t4.TargetDose) AS DosePrematurity
      FROM
        (SELECT
          t1.CVX,
          t1.ImmGivenDate,
          t1.PtBirthDate,
          t1.PtID,
          COUNT(t2.ImmGivenDate) AS DoseGivenNumber
           FROM 
          (SELECT DISTINCT * FROM VaccinesGiven
           WHERE CVX = 08
           AND PtID = '00034-002') t1,
          (SELECT DISTINCT * FROM VaccinesGiven
           WHERE CVX = 08
           AND PtID = '00034-002') t2
     WHERE t1.ImmGivenDate >= t2.ImmGivenDate
     GROUP BY t1.ImmGivenDate, t1.CVX, t1.PtBirthDate, t1.PtID
      ) t3,
    HepB3DoseSeries t4
  WHERE DATEDIFF(t3.ImmGivenDate, t3.PtBirthDate) >= t4.MinAge 
  AND t3.DoseGivenNumber >= t4.TargetDose
  GROUP BY t3.DoseGivenNumber, t3.CVX, t3.ImmGivenDate, t3.PtBirthDate, t3.PtID, Elapsed) t8
WHERE t8.DoseGivenNumber <= t7.DoseGivenNumber) AS TargetDoseMatch
FROM
/*Query 03: This query started out as Query 02. It finds the maximum TargetDose for each DoseGivenNumber.*/
(SELECT 
  t3.CVX,
  t3.ImmGivenDate,
  t3.PtBirthDate,
  DATEDIFF(t3.ImmGivenDate, t3.PtBirthDate) AS Elapsed,
  t3.PtID,
  t3.DoseGivenNumber,
  MAX(t4.TargetDose) AS MaxTargetDose,
  t3.DoseGivenNumber - MAX(t4.TargetDose) AS DosePrematurity
FROM
(SELECT
    t1.CVX,
    t1.ImmGivenDate,
    t1.PtBirthDate,
    t1.PtID,
    COUNT(t2.ImmGivenDate) AS DoseGivenNumber
       FROM 
      (SELECT DISTINCT * FROM VaccinesGiven
       WHERE CVX = 08
       AND PtID = '00034-002') t1,
      (SELECT DISTINCT * FROM VaccinesGiven
       WHERE CVX = 08
       AND PtID = '00034-002') t2
   WHERE t1.ImmGivenDate >= t2.ImmGivenDate
   GROUP BY t1.ImmGivenDate, t1.CVX, t1.PtBirthDate, t1.PtID
) t3,
HepB3DoseSeries t4
WHERE DATEDIFF(t3.ImmGivenDate, t3.PtBirthDate) >= t4.MinAge 
AND t3.DoseGivenNumber >= t4.TargetDose
GROUP BY t3.DoseGivenNumber, t3.CVX, t3.ImmGivenDate, t3.PtBirthDate, t3.PtID, Elapsed) t7) t9
GROUP BY t9.TargetDoseMatch;


+------+--------------+-------------+---------+-----------+-----------------+---------------+-----------------+-----------------+
| CVX  | EarliestDate | PtBirthDate | Elapsed | PtID      | DoseGivenNumber | MaxTargetDose | DosePrematurity | TargetDoseMatch |
+------+--------------+-------------+---------+-----------+-----------------+---------------+-----------------+-----------------+
| 08   | 2011-08-20   | 2011-08-20  |       0 | 00034-002 |               1 |             1 |               0 |               1 |
| 08   | 2012-02-18   | 2011-08-20  |     182 | 00034-002 |               4 |             3 |               1 |               2 |
| 08   | 2013-02-19   | 2011-08-20  |     549 | 00034-002 |               5 |             3 |               2 |               3 |
+------+--------------+-------------+---------+-----------+-----------------+---------------+-----------------+-----------------+
3 rows in set (0.00 sec)

Open in new window

Avatar of PortletPaul
PortletPaul
Flag of Australia image
wow, not bad for a 'newbie' I will take a look if time permits - and I can understand it ;)

very glad to see you making progress.
Avatar of KenBurke
KenBurke
Flag of United States of America image

ASKER

I think I need to call this question solved. Thanks to all who expressed an interest.
Avatar of KenBurke
KenBurke
Flag of United States of America image

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for KenBurke's comment #a39023554

for the following reason:

My code seems to give the desired result. I suspect someone with more expertise in SQL could make the code shorter, clearer, or more efficient. If anyone is still interested enough to improve upon it I would be grateful.
Avatar of PortletPaul
PortletPaul
Flag of Australia image
One quick observation:

Please stop using "select distinct"

and ABSOLUTELY stop using

"select distinct *"

DISTINCT works across the whole row and is performed after all joins/where conditions - it will slow you down - potentially a lot.

I'll take a deeper look if time permits.

Sorry I had overlooked the suggestion for coding hints.
Avatar of PortletPaul
PortletPaul
Flag of Australia image
for example, I don't see any need to the 2 subqueries in Query1, I think this would produce the same result: 
--Query01:
SELECT
  vg.CVX
, vg.ImmGivenDate
, vg.PtBirthDate
, vg.PatientSex
, vg.PtID
, COUNT(vg.ImmGivenDate) AS DoseGivenNumber
FROM VaccinesGiven as VG
WHERE vg.CVX = 08
AND vg.PtID = '00034-002'
GROUP BY vg.ImmGivenDate
ORDER BY vg.ImmGivenDate;

Open in new window

Avatar of PortletPaul
PortletPaul
Flag of Australia image
and query 3. This "troubles me" a little as there is no join specified between the 2 sets of data and I'm not sure if table HepB3DoseSeries  does have a field for joining to table VaccinesGiven. This will give rise to what is known as a "cartesian product" which is a matrix sized by the number of rows on both tables. Generally these are causes of slowness (there are occasions where they are needed, but this does not seem likely here).

So, without further detail of possible joins I have left that bit 'as is', but I think this will produce the same result: 
/*Query03: This query finds the maximum TargetDose for each DoseGivenNumber.*/
SELECT t3.CVX
    , t3.ImmGivenDate t3.PtBirthDate
    , DATEDIFF(t3.ImmGivenDate, t3.PtBirthDate) AS Elapsed
    , t3.PtID
    , t3.DoseGivenNumber
    , MAX(t4.TargetDose)
    , t3.DoseGivenNumber - MAX(t4.TargetDose) AS DosePrematurity
FROM (
        /* derived from Query01: */
        SELECT
          vg.ImmGivenDate
        , vg.CVX
        , vg.PtBirthDate
        , vg.PtID
        , COUNT(vg.ImmGivenDate) AS DoseGivenNumber
        FROM VaccinesGiven as VG
        WHERE vg.CVX = 08
        AND vg.PtID = '00034-002'
        GROUP BY
          vg.ImmGivenDate
        , vg.CVX
        , vg.PtBirthDate
        , vg.PtID
      ) t3
    , HepB3DoseSeries t4
WHERE DATEDIFF(t3.ImmGivenDate, t3.PtBirthDate) >= t4.MinAge
    AND t3.DoseGivenNumber >= t4.TargetDose
GROUP BY t3.DoseGivenNumber
    , t3.CVX
    , t3.ImmGivenDate
    , t3.PtBirthDate
    , t3.PtID
    , Elapsed;

Open in new window

Avatar of PortletPaul
PortletPaul
Flag of Australia image
Query 4, oh.

It repeats Query 3 structure in 2 places

BUT
the first of these is inside a calculation - so it will be repeated for every row - which isn't good technique

This would require more analysis than I can devote to this right now I'm afraid.

In mysql there is a way to output table definitions and data for simple reproduction (afraid I know it exists but not where or how). If you would like further assistance on sql techniques getting some table definitions and date - in this format - would be beneficial..

2 sites that may be helpful:
www.sqlfiddle.com
www.poorsql.com

no inference should be drawn from the latter one, it's just a formatting facility.

sqlfiddle allows simple databases to be built so folks can 'collaborate' - MySQL is provided.

nb: only 8000 chars are allowed to build a database and data, so its "small"

ou may want a new question for more sql assistance - if you have the time/interest
Avatar of PortletPaul
PortletPaul
Flag of Australia image
please see the following in action at: http://sqlfiddle.com/#!9/8b56d/14

I think your Query 1 produces an incorrect result, which counts 5 immunizations
But there are 6 records in your sample for PtID = '00034-002' (refer the third query below)

Result comparison 
Proposed:
CVX  IMMGIVENDATE      PTBIRTHDATE    PATIENTSEX    PTID    DOSEGIVENNUMBER
8    August, 20 2011   August, 20 2011         F    00034-002    2
8    August, 30 2011   August, 20 2011         F    00034-002    3
8    August, 31 2011   August, 20 2011         F    00034-002    4
8    February, 18 2012 August, 20 2011         F    00034-002    5
8    February, 19 2013 August, 20 2011         F    00034-002    6

Original:
CVX  IMMGIVENDATE      PTBIRTHDATE    PATIENTSEX    PTID    DOSEGIVENNUMBER
8    August, 20 2011   August, 20 2011         F    00034-002    1
8    August, 30 2011   August, 20 2011         F    00034-002    2
8    August, 31 2011   August, 20 2011         F    00034-002    3
8    February, 18 2012 August, 20 2011         F    00034-002    4
8    February, 19 2013 August, 20 2011         F    00034-002    5

Open in new window

I'm suggesting the first query below as the alternative for Query 1: 
/* Query 1 alternative 2, with corrected calculation */
select
cvx
, ImmGivenDate
, PtBirthDate
, PatientSex
, PtID
, max(DoseGivenNumber) as DoseGivenNumber
from (
      SELECT
        vg.CVX
      , vg.ImmGivenDate
      , vg.PtBirthDate
      , vg.PatientSex
      , vg.PtID
      , @row_num := IF(@prev_value=vg.PtID,@row_num+1,1) AS DoseGivenNumber
      , @prev_value := vg.PtID AS Prev_Value
      FROM VaccinesGiven as VG
      , (SELECT @row_num := 1) as x
      , (SELECT @prev_value := '') as y
      WHERE vg.CVX = 08
      AND vg.PtID = '00034-002'
      ORDER BY vg.ImmGivenDate ASC
      ) as VG
GROUP BY vg.ImmGivenDate
ORDER BY vg.ImmGivenDate
;

/* query 1 original */
SELECT
  t1.CVX,
  t1.ImmGivenDate,
  t1.PtBirthDate,
  t1.PatientSex,
  t1.PtID,
  COUNT(t2.ImmGivenDate) AS DoseGivenNumber
from 
    (SELECT DISTINCT * FROM VaccinesGiven
    WHERE CVX = 08
    AND PtID = '00034-002') t1,
    (SELECT DISTINCT * FROM VaccinesGiven
    WHERE CVX = 08
    AND PtID = '00034-002') t2
    WHERE t1.ImmGivenDate >= t2.ImmGivenDate
    GROUP BY t1.ImmGivenDate
    ORDER BY t1.ImmGivenDate
;

/* provides MySQL equivalent for "row_number()" */
SELECT
  vg.CVX
, vg.ImmGivenDate
, vg.PtBirthDate
, vg.PatientSex
, vg.PtID
, @row_num := IF(@prev_value=vg.PtID,@row_num+1,1) AS DoseGivenNumber
, @prev_value := vg.PtID AS Prev_Value
FROM VaccinesGiven as VG
, (SELECT @row_num := 1) as x
, (SELECT @prev_value := '') as y
WHERE vg.CVX = 08
AND vg.PtID = '00034-002'
ORDER BY vg.ImmGivenDate ASC
;

/* first given alternative, it is not adequate, needs more */
SELECT
  vg.CVX
, vg.ImmGivenDate
, vg.PtBirthDate
, vg.PatientSex
, vg.PtID
, COUNT(vg.ImmGivenDate) AS DoseGivenNumber
FROM VaccinesGiven as VG
WHERE vg.CVX = 08
AND vg.PtID = '00034-002'
GROUP BY vg.ImmGivenDate
ORDER BY vg.ImmGivenDate
;

Open in new window

Avatar of PortletPaul
PortletPaul
Flag of Australia image
I think I will object here so this does not auto-close, hope you don't mind but there are some important issues here - where accuracy really does matter.

(I may be wrong of course, but let's make certain of that?)
Avatar of PortletPaul
PortletPaul
Flag of Australia image
before I lose this, I'll post it.

The logic of this variant includes BOTH patientid and CVX and it will produce a running total of each CVX provided per patient.

Ideally (assuming its correctness) this would be stored as "view" so it could be easily reused in other queries. However MySQL will not permit queries referencing @variable as views, so that option is closed off.

available to see at: http://sqlfiddle.com/#!9/c2d39/4

/* provides MySQL equivalent for "row_number()" */
/* and, adding a second layer for CVX */
SELECT
  vg.CVX
, vg.ImmGivenDate
, vg.PtBirthDate
, vg.PatientSex
, vg.PtID
, @row_num   := IF(@prev_ptid=vg.PtID AND @prev_cvx=vg.CVX, @row_num + 1, 1) AS DoseGivenNumber
, @prev_ptid := vg.PtID AS prev_ptid
, @prev_cvx  := vg.CVX  AS prev_ptid
FROM VaccinesGiven as VG
, (SELECT @row_num   := 1 ) as r
, (SELECT @prev_ptid := '') as prevptid
, (SELECT @prev_cvx  := '') as prevcvx
ORDER BY vg.PtID, vg.CVX, vg.ImmGivenDate ASC
;

Open in new window

Avatar of KenBurke
KenBurke
Flag of United States of America image

ASKER

Paul,
You have given this novice a lot to chew on! I am not familiar with how to use user-defined variables (if that is the correct term) in MySQL, so I am working hard to follow you here. I did look at your sqlfiddle.com post and tried using the site myself.

Including a duplicate dose of a vaccine in the test database was something I put in deliberately as a sort of edge case. Giving the same vaccine twice in the same day would usually be considered a medical error; for purposes of scheduling future doses the 2 doses given on the same day should be treated as one. Can you help with code that would take that into account?

I am still working on your later posts. If you have time, please look at
 http://sqlfiddle.com/#!2/ede74/4

Thank you!
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Thanks for the clarification, it did seem odd to have 2 doses on one day.

Should such incidents in the data be ignored? or corrected?

I would suggest this as a possible approach.

periodically scan this vaccination table for data oddities (such as the 2 in one day), and 'flag' those records. Then in normal operational queries exclude those flagged recotrds from consideration.

This way your source data is held intact, have a record of 'oddities', and a way to trace why some records are ignored in calculations.

in essence this would require having at least one new field in the table, then an 'update query' which sets a value into that new field.

Regarding the variables, regrettably this is something you are going to have to learn. It's the 'MySQL way' of achieving certain types of calculation and you will bump into this often I suspect.


Here is a really nice explanation
http://www.explodybits.com/2011/11/mysql-row-number/
and I'll admit now, this is exactly where I discovered the 'MySQL way' when researching for a question. Ask away if you have questions on this.

I'll look a sqlfiddle soonish and report back.
Avatar of PortletPaul
PortletPaul
Flag of Australia image
forgive numerous spelling/grammar errors :) my typing sucks
Avatar of PortletPaul
PortletPaul
Flag of Australia image
I've worked on the sqlfiddle, see http://sqlfiddle.com/#!2/abe66b/17

all queries now working, but I don't know if /* ? Query 02:p is it 2 or 3? */
is producing correct results.

for record, queries below:
/* notice an additional auto increment ID field on the VaccinesGiven table 
   this is going to be essential going forward. 
   Such a thing should exist on all tables really 
BUT
   select distinct *
   will no longer work because each row has a unique ID value

+ CVX was an integer, it's now a varchar this alters 08 to '08' in selections - I hadn't noticed this before

*/
select *, 'New ID Field!' from VaccinesGiven limit 3
;

/* Query 01:k */
SELECT
  t1.CVX,
  t1.ImmGivenDate,
  t1.PtBirthDate,
  t1.PatientSex,
  t1.PtID,
  COUNT(t2.ImmGivenDate) AS DoseGivenNumber
from 
    (SELECT DISTINCT `CVX`, `ImmGivenDate`, `PtBirthDate`, `PatientSex`, `PtID`
     FROM VaccinesGiven
    WHERE CVX = '08'
    AND PtID = '00034-002') t1,
    (SELECT DISTINCT `CVX`, `ImmGivenDate`, `PtBirthDate`, `PatientSex`, `PtID`
     FROM VaccinesGiven
    WHERE CVX = '08'
    AND PtID = '00034-002') t2
    WHERE t1.ImmGivenDate >= t2.ImmGivenDate
    GROUP BY t1.ImmGivenDate
    ORDER BY t1.ImmGivenDate
;

/* Query 01:p */
select
  f.cvx
, f.ImmGivenDate
, f.PtBirthDate
, f.PatientSex
, f.PtID
, max(f.DoseGivenNumber) as DoseGivenNumber
from (
      SELECT
        vgs.CVX
      , vgs.ImmGivenDate
      , vgs.PtBirthDate
      , vgs.PatientSex
      , vgs.PtID
      , @row_num    := IF(@prev_ptid=vgs.PtID AND @prev_cvx=vgs.CVX, @row_num + 1, 1 ) AS DoseGivenNumber
      , @prev_ptid  := vgs.PtID  AS Prev_Value
      , @prev_cvx   := vgs.CVX   AS prev_cvx
      FROM (    
              SELECT
                vg.CVX
              , vg.ImmGivenDate
              , vg.PtBirthDate
              , vg.PatientSex
              , vg.PtID
              FROM VaccinesGiven as VG
              WHERE vg.CVX = '08'
              AND vg.PtID = '00034-002'
              GROUP BY   
                vg.CVX
              , vg.ImmGivenDate
              , vg.PtBirthDate
              , vg.PatientSex
              , vg.PtID
            ) as VGS
      , (SELECT @row_num    := 1)  as r
      , (SELECT @prev_ptid  := '') as prevptid
      , (SELECT @prev_cvx   := '') as prevcvx
      ORDER BY vgs.ImmGivenDate ASC
     ) as F
GROUP BY f.ImmGivenDate
ORDER BY f.ImmGivenDate
;

/* ? Query 02:p is it 2 or 3? */
SELECT t3.CVX
    , t3.ImmGivenDate
    , t3.PtBirthDate
    , DATEDIFF(t3.ImmGivenDate, t3.PtBirthDate) AS Elapsed
    , t3.PtID
    , t3.DoseGivenNumber
    , MAX(t4.TargetDose)
    , t3.DoseGivenNumber - MAX(t4.TargetDose) AS DosePrematurity
FROM (
      /* derived from Query 01:p */
      SELECT
        vgs.CVX
      , vgs.ImmGivenDate
      , vgs.PtBirthDate
      , vgs.PatientSex
      , vgs.PtID
      , @row_num    := IF(@prev_ptid=vgs.PtID AND @prev_cvx=vgs.CVX, @row_num + 1, 1 ) AS DoseGivenNumber
      , @prev_ptid  := vgs.PtID  AS Prev_Value
      , @prev_cvx   := vgs.CVX   AS prev_cvx
      FROM (    
              SELECT
                vg.CVX
              , vg.ImmGivenDate
              , vg.PtBirthDate
              , vg.PatientSex
              , vg.PtID
              FROM VaccinesGiven as VG
              WHERE vg.CVX = '08'
              AND vg.PtID = '00034-002'
              GROUP BY   
                vg.CVX
              , vg.ImmGivenDate
              , vg.PtBirthDate
              , vg.PatientSex
              , vg.PtID
            ) as VGS
      , (SELECT @row_num    := 1)  as r
      , (SELECT @prev_ptid  := '') as prevptid
      , (SELECT @prev_cvx   := '') as prevcvx
      ORDER BY vgs.ImmGivenDate ASC
      ) t3
CROSS JOIN HepB3DoseSeries t4
WHERE DATEDIFF(t3.ImmGivenDate, t3.PtBirthDate) >= t4.MinAge
    AND t3.DoseGivenNumber >= t4.TargetDose
GROUP BY t3.DoseGivenNumber
    , t3.CVX
    , t3.ImmGivenDate
    , t3.PtBirthDate
    , t3.PtID
    , Elapsed
;

/* purely experimental */
select
*
from VaccinesGiven as t3
INNER JOIN HepB3DoseSeries t4
ON DATEDIFF(t3.ImmGivenDate, t3.PtBirthDate) >= t4.MinAge
    AND 5 >= t4.TargetDose
              WHERE t3.CVX = '08'
              AND t3.PtID = '00034-002'
limit 3
;

Open in new window

I had to trim back several rows of data to remain within the 8000 char limit too.
Avatar of KenBurke
KenBurke
Flag of United States of America image

ASKER

Paul,

Your query, /* ? Query 02:p is it 2 or 3? */ does give the correct result. I just realized the CDC's vaccination schema should easily be able to handle 2 of the same vaccine doses given on the same day. For simplicity's sake I have been having my queries look only at the minimum age requirement for each dose of vaccine. However, the schema also includes minimum intervals between doses. A second dose of a vaccine given on a given day would have an interval of 0 days from its immediately preceding dose, which would make it an invalid dose. I will have to figure out how this criterion gets implemented.

Thanks again for your help.
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Ken,

Hi. Just wondering if you need more from me on this question?

I do have an observation (or maybe its a question) regarding table 'HepB3DoseSeries'. This does not seem to share a column with the 'VaccinesGiventable'. Should 'HepB3DoseSeries' have CVX as a column?

mysql> select * from HepB3DoseSeries;
+------------+-----------+--------+------------------------+---------------------+-------------------------------+----------------------------+
| TargetDose | AbsMinAge | MinAge | AbsMinIntervalLastDose | MinIntervalLastDose | AbsMinIntervalFromTargetDose1 | MinIntervalFromTargetDose1 |
+------------+-----------+--------+------------------------+---------------------+-------------------------------+----------------------------+
|          1 |         0 |      0 |                   NULL |                NULL |                          NULL |                       NULL |
|          2 |        24 |     28 |                     24 |                  28 |                          NULL |                       NULL |
|          3 |       164 |    168 |                     52 |                  56 |                           108 |                        112 |
+

Open in new window

The distinct CVX values (so far) are :03,08,10,20,21,48,49,83,100,119,120,133,140

My expectation would be that each of these will have its own dose regime (pattern), but I would not expect each CVX regime to be in its own unique table.

Just wondering what you were planning in this regard.

Anyway, I shall wait for further news. If the question goes 'cold' however it may go into a close down. That's OK of course you can always open new ones.

Cheers,
Paul
Avatar of KenBurke
KenBurke
Flag of United States of America image

ASKER

Paul,

The table 'HepB3DoseSeries' definitely needs to be reworked. I have not yet worked out all the details, but I expect that there would be either a single table or a set of related tables that would encompass the dosing requirements for all of the vaccines. That table or tables would include a field for the CVX code. I agree that having a separate table for each vaccine (ie. each CVX) does not make sense, as it would make it difficult to accommodate new vaccines and changes in vaccination rules. My goal is a database structure that would be able to take account of new vaccines and of changes in vaccination rules by updates to the content of the tables rather than by requiring rewriting SQL code.

I think I will be closing this question soon. If I could ask one more favor of you, could you point me to some references on the use of user-defined variables in MySQL? I am thinking of code like

@row_num    := IF(@prev_ptid=vgs.PtID AND @prev_cvx=vgs.CVX, @row_num

I own 3 books on SQL, and none of them cover this type of syntax. The MySQL manual covers user-defined variables, of course. I am looking for introductory material. Do you have any suggestions?

Thank you again. Your help has pointed me in directions that never would have occurred to me.
Avatar of PortletPaul
PortletPaul
Flag of Australia image
did you try the URL I provided?
http://www.explodybits.com/2011/11/mysql-row-number/

I have to admit  I did add something to this
@row_num    := IF(@prev_ptid=vgs.PtID AND @prev_cvx=vgs.CVX, @row_num

I'll write up an explanation
Avatar of PortletPaul
PortletPaul
Flag of Australia image
OK, I have a "special" sqlfiddle for this: http://sqlfiddle.com/#!2/91e1e/7

I have stripped down a query to the bare bones and re-sequenced lines of code so they might make more sense. There is also a terse "explanation" there, which if you read it row by row might also make sense.

I'm also trying to write-up the logic a little more completely, but perhaps that sqlfiddle will help you see how the data and the variables interact.

One item you may need guidance with is the IF() function

 IF(    expr1   ,expr2    ,expr3  )

expr1 is any "expression"  to "evaluate" e.g. IF( your_name='Ken' ...

should expr1 be true, then perform "expr2"

otherwise when expr1 is false, then perform "expr3"

Now in the code I have provided "expr1" is a little more complex because it contains AND
- but it is still "expr1" please note that

my "expr1" is

 IF( @prev_ptid=vg.PtID AND @prev_cvx=vg.CVX

what this means is BOTH variables must equal the data of the current row
if BOTH are equal, then "expr1" is true and "expr2" is performed, and "expr2" here is:

@row_num + 1

which just adds 1 to an existing value (1, 1+1=2, 2+1=3, 3+1.....)

if "expr1" is false (either one of PtID , CVX "different" to the previous row, then "expr3" is:

1

i.e. make @row_num := 1 to start counting the next group of data rows, commencing at 1

I'm hoping you scientific brain will deduce what it needs from my shorthand notes
Avatar of PortletPaul
PortletPaul
Flag of Australia image
regarding reference material:

I'm not that familiar with MySQL tutorials or introductory materials (to be honest I'm not that familiar with MySQL, much more familiar with Oracle). However I did find this page that introduces variables that you may find interesting.

http://www.yaldex.com/mysql_tutorial_2/ch10lev1sec4.html

Variables are common practice in most programming languages which is why I don't have trouble adopting them in MySQL queries (as it's just kind of familiar to me).

You will find them very helpful I think.

This reference covers more than MySQL
http://www.functionx.com/sql/Lesson03.htm

but it seems quite good if you just concentrate of the MySQL references.

Quite often the MySQL documentation has user contributions and the end of pages that can be quite useful too.

You can also ask questions here, others here may be better at explaining them than I have been.
Avatar of PortletPaul
PortletPaul
Flag of Australia image
http://sqlfiddle.com/#!2/2b65e/14

perhaps this alternative use of syntax will be easier to understand - I actually prefer this style (particularly the cross join) as you can 'see' that the variables get a initial value prior to the query - and what they are. 
SET @prev_ptid  := '';
SET @prev_cvx   := '';
SET @row_num    := 1;

select
  vgs.PtID
, vgs.CVX
, vgs.ImmGivenDate
                                                              /* "layer 3" */
, @row_num    := IF(@prev_ptid=vgs.PtID AND @prev_cvx=vgs.CVX, @row_num + 1, 1 ) AS DoseGivenNumber
, @prev_cvx   := vgs.CVX   AS prev_cvx  /* "layer 2" */
, @prev_ptid  := vgs.PtID  AS prev_ptid /* "layer 1" */
from VaccinesGiven as VGS

cross join (select @prev_ptid, @prev_cvx, @row_num) as vars

ORDER BY
  vgs.PtID                              /* "layer 1" */
, vgs.CVX                               /* "layer 2" */
, vgs.ImmGivenDate ASC                  /* "layer 3" */
;

Open in new window

do tell me if this just confuses more:
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of KenBurke
KenBurke
Flag of United States of America image

ASKER

I can't believe you gave me so much help here, Paul. Thank you so much! I have looked at the references, the sqlfiddle.com links, and the spreadsheet. I will need to study them further, but they have already been a great help.
Avatar of PortletPaul
PortletPaul
Flag of Australia image
:) let's see:

a pediatrician trying to figure out how to get the right doses of the right stuff at the right time to the right kid.

seems like a civic duty to me, and anything a parent would volunteer for