April 6, 2012 at 11:07 am
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
*/
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler
April 6, 2012 at 11:22 am
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?
April 6, 2012 at 1:49 pm
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
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler
April 6, 2012 at 5:03 pm
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.
April 7, 2012 at 9:30 am
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
April 11, 2012 at 9:27 am
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
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply