Dates of MMR vaccine

  • A member of the reporting team came to me with this business problem because it was out of their league, unfortunately I think it's out of my league as well after beating my head against it for about a week without coming up with anything that I would feel OK about running in production (hundreds of thousands of patients). I've simplified the problem down to its most basic form which I have outlined below in the code block. If anyone has any ideas, I'd be most grateful.

    Thanks,

    Alan

    /*

    Goal:

    A query that will identify the dates that an MMR vaccine, or in which the full

    combination of individual vaccines that makes up a completed MMR vaccine,

    was received by each patient.

    I have full access to the database, and can create supporting tables as needed to store rules/metadata/etc for the query

    */

    --Patient table

    DECLARE @Patient TABLE (PatientID int NOT NULL, PatName varchar(25) NOT NULL)

    --Immunization List

    DECLARE @Immunization TABLE (ImmunizationID int NOT NULL, ImmunizationName varchar(30) NOT NULL)

    --Patient immunization List

    DECLARE @PatientImmunization TABLE (PatientImmunizationID int NOT NULL IDENTITY(1,1), PatientID int NOT NULL, ImmunizationID int NOT NULL, ImmunizationDate date NOT NULL)

    INSERT INTO @Patient

    SELECT 1, 'Patient 1' UNION ALL

    SELECT 2, 'Patient 2' UNION ALL

    SELECT 3, 'Patient 3' UNION ALL

    SELECT 4, 'Patient 4'

    INSERT INTO @Immunization

    SELECT 1, 'MMRV' UNION ALL

    SELECT 2, 'MMR' UNION ALL

    SELECT 3, 'MEASLES' UNION ALL

    SELECT 4, 'MEASLES/RUBELLA' UNION ALL

    SELECT 5, 'MUMPS' UNION ALL

    SELECT 6, 'RUBELLA'

    --Patient 1 Sample Data

    INSERT INTO @PatientImmunization

    SELECT 1, 1, '1/1/2005' UNION ALL--MMRV (stands on its own)

    SELECT 1, 2, '6/1/2005'--MMR (stands on its own)

    --Patient 2 sample Data

    INSERT INTO @PatientImmunization

    SELECT 2, 3, '1/1/2006' UNION ALL--Measles

    SELECT 2, 5, '1/15/2006' UNION ALL--Mumps

    SELECT 2, 2, '2/1/2006' UNION ALL--MMR (stands on its own)

    SELECT 2, 6, '6/1/2006'--Rubella, completes the Measles/Mumps set from earlier

    --Patient 3 sample data

    INSERT INTO @PatientImmunization

    SELECT 3, 3, '1/1/2007' UNION ALL--Measles

    SELECT 3, 4, '2/1/2007' UNION ALL--Measles/Rubella

    SELECT 3, 6, '2/15/2007' UNION ALL--Rubella

    SELECT 3, 5, '3/1/2007' UNION ALL--Mumps (finishes ONE of the earler measles/rubella sets)

    SELECT 3, 1, '4/1/2007' UNION ALL--MMRV (stands on its own)

    SELECT 3, 5, '5/1/2007'--Mumps (finishes the other remaining measles/rubella set)

    --Patient 4 sample data

    INSERT INTO @PatientImmunization

    SELECT 4, 3, '1/1/2008' UNION ALL--Measles

    SELECT 4, 5, '1/15/2008' UNION ALL--Mumps

    SELECT 4, 4, '2/1/2008' UNION ALL--Measles/Rubella (Rubella portion finishes off the earlier set, measles unattached at this time)

    SELECT 4, 5, '3/1/2008' UNION ALL--Mumps

    SELECT 4, 6, '9/1/2008'--Rubella(finishes the 2nd full MMR set)

    /*

    The result set should look something like the following

    =========================================================

    PatientIDMMRDate

    11/1/2005

    16/1/2005

    22/1/2006

    26/1/2006

    33/1/2007

    34/1/2007

    35/1/2007

    42/1/2008

    49/1/2008

    */

  • The problem is there's nothing in the data that would allow to define "the full combination of individual vaccines that makes up a completed MMR vaccine".

    Can you please elaborate?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • LutzM (4/6/2012)


    The problem is there's nothing in the data that would allow to define "the full combination of individual vaccines that makes up a completed MMR vaccine".

    Can you please elaborate?

    Sure thing.

    An MMR vaccine is a standard "combination" vaccine, meaning it provides vaccination effects for multiple diseases in a single dose, for three diseases Measles, Mumps, and Reubella. However, some patients do not necessarily receive a full-fledged MMR vaccine in a single dose (MMR, MMRV); they may, instead, have been vaccinated for each disease individually. For example they may have received their Measles, Mumps, and Reubella shots as single vaccines over a stretch of time; therefore their considered completion date for a full-fledged MMR would be the date of the last vaccine in the series. Additionally, it is absolutely possible for a patient to have multiple MMR vaccines, in which case I need to identify the date of completion for each one. Also, as you may infer from the sample data, there is a combination vaccine for just Measles and Rubella; a patient would still need to receive a Mumps vaccine in order for them to be considered "MMR complete." For the purpose of this query, the MMRV vaccine is considered synonymous with MMR.

    I hope this helps clarify.

    Thanks,

    Alan

  • The less complicated way to get the total number of MMR vaccines:

    ;

    WITH cte AS

    (

    SELECT

    PatientID,

    SUM(CASE WHEN ImmunizationID IN(1,2,3,4) THEN 1 ELSE 0 END) AS MEASLES,

    SUM(CASE WHEN ImmunizationID IN(1,2,4,6) THEN 1 ELSE 0 END) AS RUBELLA,

    SUM(CASE WHEN ImmunizationID IN(1,2,5) THEN 1 ELSE 0 END) AS MUMPS

    FROM @PatientImmunization

    GROUP BY PatientID

    )

    SELECT cte.PatientId,MMR.cnt AS MMR_cnt

    FROM cte

    CROSS APPLY

    (

    SELECT MIN(x) cnt

    FROM (VALUES (MEASLES),(RUBELLA),(MUMPS))a(x)

    )MMR

    Unfortunately, calculating the date values is "slightly" more difficult.

    I'm not sure if there's a way for a set based solution. Maybe one of the gurus around here has an idea....

    As a side note: this solution will work on SQL 2008 and up.

    If you're using SQL 2000 (as indicated by the forum you've posted in), please confirm.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Ok, here's the only way I can think of: to use the "Quirky update" as described in Jeff Modens article[/url]

    It is absolutely required to read the article and fully understand all requirements involved in using this method. Consider it as being the chainsaw in your tool box: extremely powerful but dangerous at the same time!

    Ok, end of the "Warnings and Disclaimer" section 🙂

    I've added 4 more columns: m1, m2, and r for the diseases. Those columns are not really required but should help to follow the logic behind.

    [res] is used to indicate that this column will be part of the final result set.

    The logic behind:

    Count each dose per disease. If all three diseases are covered by at least one dose, mark this row as relevant and subtract 1 from the counters.

    Here's the code:

    -- modified definition of @PatientImmunization

    DECLARE @PatientImmunization TABLE (

    PatientImmunizationID int NOT NULL IDENTITY(1,1),

    PatientID int NOT NULL,

    ImmunizationID int NOT NULL,

    ImmunizationDate date NOT NULL,

    m1 INT DEFAULT 0,

    m2 INT DEFAULT 0,

    r INT DEFAULT 0,

    res INT DEFAULT 0

    )

    -- declare and intialize required variables for the quirky update

    DECLARE @Sequence INT = 0,

    @MEASLES INT,

    @MUMPS INT,

    @RUBELLA INT,

    @PatId INT,

    @PatIdPrev INT,

    @relevant INT

    ;

    -- the quirky update

    SELECT

    @PatId = 0,

    @PatIdPrev = 1,

    @MEASLES = 0,

    @MUMPS = 0,

    @relevant = 0,

    @RUBELLA =0;

    WITH SafetyCheck AS

    (

    SELECT

    Patientid,

    immunizationDate,

    PatientimmunizationId,

    m1,

    m2,

    r,

    ImmunizationID,

    res,

    Sequence = ROW_NUMBER() OVER (ORDER BY Patientid,immunizationDate, PatientimmunizationId)

    FROM @PatientImmunization

    )

    UPDATE t

    SET

    @Sequence = CASE WHEN Sequence = @Sequence + 1 THEN @Sequence + 1

    ELSE 1/0 END,

    @relevant = CASE WHEN Patientid <> @PatIdPrev THEN 0 ELSE @relevant END,

    @MEASLES= - @relevant +

    CASE WHEN Patientid <> @PatIdPrev AND ImmunizationID IN(1,2,3,4)

    THEN 1

    WHEN Patientid = @PatIdPrev AND ImmunizationID IN(1,2,3,4)

    THEN @MEASLES + 1

    WHEN Patientid = @PatIdPrev AND ImmunizationID NOT IN(1,2,3,4)

    THEN @MEASLES

    ELSE 0 END,

    m1 = @MEASLES,

    @MUMPS= - @relevant +

    CASE WHEN Patientid <> @PatIdPrev AND ImmunizationID IN (1,2,5)

    THEN 1

    WHEN Patientid = @PatIdPrev AND ImmunizationID IN(1,2,5)

    THEN @MUMPS + 1

    WHEN Patientid = @PatIdPrev AND ImmunizationID NOT IN(1,2,5)

    THEN @MUMPS

    ELSE 0 END,

    m2 = @MUMPS,

    @RUBELLA= - @relevant +

    CASE WHEN Patientid <> @PatIdPrev AND ImmunizationID IN (1,2,4,6)

    THEN 1

    WHEN Patientid = @PatIdPrev AND ImmunizationID IN(1,2,4,6)

    THEN @RUBELLA + 1

    WHEN Patientid = @PatIdPrev AND ImmunizationID NOT IN(1,2,4,6)

    THEN @RUBELLA

    ELSE 0 END,

    r = @RUBELLA,

    @relevant = CASE WHEN @MEASLES >=1 AND @MUMPS >=1 AND @RUBELLA >=1 THEN 1 ELSE 0 END,

    res = @relevant,

    @PatIdPrev = Patientid

    FROM SafetyCheck t WITH (TABLOCKX)

    OPTION (MAXDOP 1);

    -- and the final result:

    SELECT Patientid,ImmunizationDate

    FROM @PatientImmunization

    WHERE res = 1



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thanks LutzM, I'll have to check out the quirky update.

    I did come up with a solution that seems to work also (Props to LutzM for the idea of counting the vaccines), I used an inline table-valued function, so it necessitated changing all of my table variable in the test script to actual tables (I'll post the script at the end of this post). I'm guessing performance won't scale too well, I'm standing up a test environment that matches my production data just to see how it works out. Any initial opinions of my solution? I'm sure you'll all want to give me a flogging for using row_number() ....

    And yes, I am running SQL Server 2008; apologies for posting in the wrong forum..

    New database objects:

    dbo.ImmunizationBits - This table isn't really necessary for the test data, since there are only 6 different vaccines. I mostly added it because in actuality in the real scenario there are dozens of vaccines (different manufacturers, etc) that equate to the same thing.

    dbo.GetMMRDatesByPatientID - Inline table valued function that looks up MMR 'completion' dates given a PatientID

    Alright, here is my full script. It creates the database objects, populates them with test data, and removes them at the end. All the standard warnings, don't run this in a production database yadda yadda. Thanks all for your help, and let my flogging begin.

    /*

    Goal:

    A query that will identify the dates that an MMR vaccine, or in which the full

    combination of individual vaccines that makes up a completed MMR vaccine,

    was received by each patient.

    I have full access to the database, and can create supporting tables as needed to store rules/metadata/etc for the query

    */

    --Patient table

    --DECLARE @Patient TABLE (PatientID int NOT NULL, PatName varchar(25) NOT NULL)

    CREATE TABLE dbo.Patient

    (

    PatientID int NOT NULL

    , PatName varchar(25) NOT NULL

    )

    --Immunization List

    --DECLARE @Immunization TABLE (ImmunizationID int NOT NULL, ImmunizationName varchar(30) NOT NULL)

    CREATE TABLE dbo.Immunization

    (

    ImmunizationID int NOT NULL

    , ImmunizationName varchar(30) NOT NULL

    )

    --Patient immunization List

    --DECLARE @PatientImmunization TABLE (PatientImmunizationID int NOT NULL IDENTITY(1,1), PatientID int NOT NULL, ImmunizationID int NOT NULL, ImmunizationDate date NOT NULL)

    CREATE TABLE dbo.PatientImmunization

    (

    PatientImmunizationID int IDENTITY(1,1) NOT NULL

    , PatientID int NOT NULL

    , ImmunizationID int NOT NULL

    , ImmunizationDate date NOT NULL

    )

    INSERT INTO dbo.Patient

    SELECT 1, 'Patient 1' UNION ALL

    SELECT 2, 'Patient 2' UNION ALL

    SELECT 3, 'Patient 3' UNION ALL

    SELECT 4, 'Patient 4' UNION ALL

    SELECT 5, 'Patient 5'

    INSERT INTO dbo.Immunization

    SELECT 1, 'MMRV' UNION ALL

    SELECT 2, 'MMR' UNION ALL

    SELECT 3, 'MEASLES' UNION ALL

    SELECT 4, 'MEASLES/RUBELLA' UNION ALL

    SELECT 5, 'MUMPS' UNION ALL

    SELECT 6, 'RUBELLA'

    --Patient 1 Sample Data

    INSERT INTO dbo.PatientImmunization

    SELECT 1, 1, '1/1/2005' UNION ALL--MMRV (stands on its own)

    SELECT 1, 2, '6/1/2005'--MMR (stands on its own)

    --Patient 2 sample Data

    INSERT INTO dbo.PatientImmunization

    SELECT 2, 3, '1/1/2006' UNION ALL--Measles

    SELECT 2, 5, '1/15/2006' UNION ALL--Mumps

    SELECT 2, 2, '2/1/2006' UNION ALL--MMR (stands on its own)

    SELECT 2, 6, '6/1/2006'--Rubella, completes the Measles/Mumps set from earlier

    --Patient 3 sample data

    INSERT INTO dbo.PatientImmunization

    SELECT 3, 3, '1/1/2007' UNION ALL--Measles

    SELECT 3, 4, '2/1/2007' UNION ALL--Measles/Rubella

    SELECT 3, 6, '2/15/2007' UNION ALL--Rubella

    SELECT 3, 5, '3/1/2007' UNION ALL--Mumps (finishes ONE of the earler measles/rubella sets)

    SELECT 3, 1, '4/1/2007' UNION ALL--MMRV (stands on its own)

    SELECT 3, 5, '5/1/2007'--Mumps (finishes the other remaining measles/rubella set)

    --Patient 4 sample data

    INSERT INTO dbo.PatientImmunization

    SELECT 4, 3, '1/1/2008' UNION ALL--Measles

    SELECT 4, 5, '1/15/2008' UNION ALL--Mumps

    SELECT 4, 4, '2/1/2008' UNION ALL--Measles/Rubella (Rubella portion finishes off the earlier set, measles unattached at this time)

    SELECT 4, 5, '3/1/2008' UNION ALL--Mumps

    SELECT 4, 6, '9/1/2008'--Rubella(finishes the 2nd full MMR set)

    INSERT INTO dbo.PatientImmunization

    SELECT 5, 3, '1/1/2009'--Patient 5 has only received a single Measles vaccine, so he shouldn't show up in our result set

    /*

    The result set should look something like the following

    =========================================================

    PatientIDMMRDate

    11/1/2005

    16/1/2005

    22/1/2006

    26/1/2006

    33/1/2007

    34/1/2007

    35/1/2007

    42/1/2008

    49/1/2008

    */

    --An MMR has 3 parts, so lets use 3 bits of a tinyint and represent the components of each vaccine

    --00000001 (1)Measles

    --00000010 (2)Mumps

    --00000100 (4)Reubella

    --DECLARE @ImmunizationBits TABLE (ImmunizationID int NOT NULL, ImmunizationBits tinyint NOT NULL)

    CREATE TABLE dbo.ImmunizationBits

    (

    ImmunizationID int NOT NULL

    , ImmunizationBits tinyint NOT NULL

    )

    INSERT INTO dbo.ImmunizationBits (ImmunizationID, ImmunizationBits) VALUES (1, 7)--MMRV Represents all vaccinations, 4+2+1 = 7

    INSERT INTO dbo.ImmunizationBits (ImmunizationID, ImmunizationBits) VALUES (2, 7)--MMR Represents all vaccinations, 4+2+1 = 7

    INSERT INTO dbo.ImmunizationBits (ImmunizationID, ImmunizationBits) VALUES (3, 1) --Measles (1)

    INSERT INTO dbo.ImmunizationBits (ImmunizationID, ImmunizationBits) VALUES (5, 2) --Mumps (2)

    INSERT INTO dbo.ImmunizationBits (ImmunizationID, ImmunizationBits) VALUES (6, 4) --Reubella (4)

    INSERT INTO dbo.ImmunizationBits (ImmunizationID, ImmunizationBits) VALUES (4, 5) --Measles/Reubella 4+1 = (5)

    GO

    CREATE FUNCTION dbo.GetMMRDatesByPatientID(@PatientID int)

    RETURNS TABLE

    AS

    RETURN

    (

    WITH PatientVaccines

    AS

    (

    SELECT

    ImmunizationID

    , ImmunizationDate

    , ROW_NUMBER() OVER (ORDER BY ImmunizationDate ASC, PatientImmunizationID ASC) AS [RowNum]

    from dbo.PatientImmunization

    where

    PatientID = @PatientID

    ),

    VaccineProgress (ImmunizationID, ImmunizationDate, RowNum, MeaslesCnt, MumpsCnt, ReubellaCnt, MMRCount, IsMMR)

    AS

    (

    SELECT

    pv.ImmunizationID

    , pv.ImmunizationDate

    , pv.RowNum

    , CASE WHEN ib.ImmunizationBits & 1 = 1 THEN 1 ELSE 0 END AS [MeaslesCnt]

    , CASE WHEN ib.ImmunizationBits & 2 = 2 THEN 1 ELSE 0 END AS [MumpsCnt]

    , CASE WHEN ib.ImmunizationBits & 4 = 4 THEN 1 ELSE 0 END AS [ReubellaCnt]

    , CASE WHEN ib.ImmunizationBits & 7 = 7 THEN 1 ELSE 0 END AS [MMRCount]

    , CASE WHEN ib.ImmunizationBits & 7 = 7 THEN 1 ELSE 0 END AS [IsMMR]

    FROM PatientVaccines pv

    JOIN dbo.ImmunizationBits ib ON ib.ImmunizationID = pv.ImmunizationID

    WHERE

    RowNum = 1

    UNION ALL

    SELECT

    X.ImmunizationID

    , X.ImmunizationDate

    , X.RowNum

    , X.MeaslesCnt

    , X.MumpsCnt

    , X.ReubellaCnt

    , CASE WHEN x.MeaslesCnt - X.MMRCount > 0 and X.MumpsCnt - X.MMRCount > 0 and X.ReubellaCnt - X.MMRCount > 0 THEN X.MMRCount + 1 ELSE 0 END AS [MMRCount]

    , CASE WHEN x.MeaslesCnt - X.MMRCount > 0 and X.MumpsCnt - X.MMRCount > 0 and X.ReubellaCnt - X.MMRCount > 0 THEN 1 ELSE 0 END AS [IsMMR]

    FROM

    (

    SELECT

    pv.ImmunizationID

    , pv.ImmunizationDate

    , pv.RowNum

    , vp.MeaslesCnt + CASE WHEN ib.ImmunizationBits & 1 = 1 THEN 1 ELSE 0 END AS [MeaslesCnt]

    , vp.MumpsCnt + CASE WHEN ib.ImmunizationBits & 2 = 2 THEN 1 ELSE 0 END AS [MumpsCnt]

    , vp.ReubellaCnt + CASE WHEN ib.ImmunizationBits & 4 = 4 THEN 1 ELSE 0 END AS [ReubellaCnt]

    , vp.MMRCount

    FROM PatientVaccines pv

    JOIN dbo.ImmunizationBits ib ON ib.ImmunizationID = pv.ImmunizationID

    JOIN VaccineProgress vp ON pv.RowNum = vp.RowNum+1

    ) X

    )

    SELECT ImmunizationDate FROM VaccineProgress where IsMMR = 1

    )

    GO

    SELECT

    pti.PatientID

    , mmrD.ImmunizationDate

    FROM (SELECT DISTINCT PatientID FROM dbo.PatientImmunization) AS ptI

    CROSS APPLY dbo.GetMMRDatesByPatientID(ptI.PatientID) AS mmrD

    DROP TABLE dbo.ImmunizationBits

    DROP TABLE dbo.Patient

    DROP TABLE dbo.Immunization

    DROP TABLE dbo.PatientImmunization

    DROP FUNCTION dbo.GetMMRDatesByPatientID

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply