EastRidgesSql query examples

for Mountain Meadow 3

 

--Tests Signed Today SELECT Tests.TestName, Tests.DateOfTest, Tests.WhenInitialed, Tests.WhoInitialedID, Patients.DisplayName
FROM Tests JOIN Patients ON Tests.PatientID = Patients.PatientID
WHERE Tests.WhenInitialed > DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
ORDER BY Tests.WhenInitialed DESC

--Prescriptions for phentermine since given date
DECLARE @FromDate DATETIME;
DECLARE @MedicationName VARCHAR(50);
SET @FromDate = '2011-1-1';
Set @MedicationName = '%phentermine%';

SELECT DISTINCT Patients.DisplayName, Medications.WhenPrescribed, Patients.ChartNum, Patients.DateOfBirth
FROM Patients JOIN Medications ON Patients.PatientID = Medications.PatientID
WHERE
Medications.MedicationText LIKE @MedicationName
AND Medications.WhenPrescribed >= @FromDate
ORDER BY DisplayName, WhenPrescribed

---Patients with Diabetes with no A1c in so many months
DECLARE @NumMonths int;
SET @NumMonths = -12; -- for no a1c in past -x months
SELECT DISTINCT Patients.DisplayName, Patients.ChartNum, Patients.DateOfBirth
FROM Patients JOIN Problems ON Patients.PatientID = Problems.PatientID
WHERE
--active patients
Patients.IsActive = 1
AND Patients.IsDeceased = 0
AND Problems.ICDx LIKE '250.%'
--not x'd out problem...
AND (Problems.XOutID IS NULL OR Problems.XOutID < 1)
-- not inactive problem
AND NOT Problems.Status = 2
--and have had any test in 3 years so they are likely active patients
AND Patients.PatientID IN
(
SELECT Tests.PatientID
FROM Tests
WHERE Tests.DateOfTest > DATEADD(MONTH, -36 , GETDATE())
)
--but no a1c in @NumMonths month
AND Patients.PatientID NOT IN
(SELECT Patients.PatientID
FROM Patients LEFT JOIN Tests ON Patients.PatientID = Tests.PatientID
WHERE Tests.TestNameID = '49'
AND Tests.DateOfTest > DATEADD(MONTH, @NumMonths, GETDATE())
)

--Weight Trends
SELECT
PATS.DisplayName
,(SELECT MIN(DateOfVitals) FROM VitalSigns
WHERE VitalSigns.PatientId = VS.PatientID
AND (VitalSigns.VitalsNameID = 4 OR VitalSigns.VitalsNameID = 3))
AS FirstDate
,VS.DateOfVitals as ThisDate
,0+
(SELECT TOP 1 FloatValue FROM VitalSigns
WHERE PatientId = VS.PatientID
AND (VitalsNameID = 3 or VitalsNameID = 4)
AND DateOfVitals =
(SELECT MIN(DateOfVitals) FROM VitalSigns
WHERE VitalSigns.PatientId = VS.PatientID
AND (VitalSigns.VitalsNameID = 4 OR VitalSigns.VitalsNameID = 3))
)
AS FirstValue
,VS.FloatValue AS ThisValue
,DATEDIFF(DAY
,(SELECT MIN(DateOfVitals) FROM VitalSigns
WHERE VitalSigns.PatientId = VS.PatientID
AND (VitalSigns.VitalsNameID = 4 OR VitalSigns.VitalsNameID = 3))
,VS.DateOfVitals
) AS Span
,VS.FloatValue -
(SELECT TOP 1 FloatValue FROM VitalSigns
WHERE PatientId = VS.PatientID
AND (VitalsNameID = 3 or VitalsNameID = 4)
AND DateOfVitals =
(SELECT MIN(DateOfVitals) FROM VitalSigns
WHERE VitalSigns.PatientId = VS.PatientID
AND (VitalSigns.VitalsNameID = 4 OR VitalSigns.VitalsNameID = 3))
)
AS Diff
from VitalSigns VS
left join Patients PATS on VS.PatientId = PATS.PatientID
where (VitalsNameID = 3 or VitalsNameID = 4)
AND VS.FloatValue < 200 -- omit int.MaxValue
order by VS.PatientId, VS.DateOfVitals

--PATIENTS (seen since @WasSeenSinceDate but ) NOT SEEN SINCE @NotSeenSinceDate
--optionally specifying in SubGroup x by specifying @SubGroup value
--e.g. could say @SubGroup int = 1 for standard medicare patients (group 1)
-- or leave @SubGroup int = 0 to ignore
DECLARE @SubGroup int = 0;
DECLARE @NotSeenSinceDate DATETIME = '2008-04-01';
DECLARE @WasSeenSinceDate DATETIME = '2007-01-1';
SELECT DISTINCT Patients.DisplayName, Patients.PrimaryDocID, ChartNum --, PatientCommunicationAddresses.Value
from chartnotes left join patients
on ChartNotes.PatientID = Patients.PatientID
left join PatientCommunicationAddresses on Patients.PatientID = PatientCommunicationAddresses.LinkID
where DateOfEncounter > @WasSeenSinceDate
AND ChartNotes.NoteCategoryID = 1
and Patients.IsActive = 1
and Patients.IsDeceased = 0
and ((@SubGroup = 0) OR (Patients.SubGroup & @SubGroup) = @SubGroup)
AND Patients.PatientID NOT IN
(
SELECT Patients.PatientID
FROM ChartNotes LEFT JOIN patients
ON ChartNotes.PatientID = Patients.PatientID
WHERE DateOfEncounter > @NotSeenSinceDate
AND ChartNotes.NoteCategoryID = 1
)
order by PrimaryDocID, DisplayName

--Whose chart did I sign today?

SELECT Patients.DisplayName, LEN(ChartNotes.Text ) as length, ChartNotes.Text
FROM ChartNotes JOIN ChartNoteSignatures ON ChartNotes.ChartNoteID = ChartNoteSignatures.ChartNoteID
JOIN Patients ON ChartNotes.PatientID = Patients.PatientID
WHERE ChartNotes.AuthorID = 1 and ChartNoteSignatures.WhenSigned > '2012-02-17'

-- visits today or some days back
-- which doctor ID or use -1 to list all docs
DECLARE @AuthorID int = -1
-- from how many days back (zero = today)
DECLARE @StartDaysBack int = 0
-- up through how many days back (zero = today)
DECLARE @StopDaysBack int = 0
SELECT DISTINCT DisplayName, ChartNum, AuthorID, NoteCategoryID
FROM ChartNotes LEFT JOIN Patients ON ChartNotes.PatientID = Patients.PatientID
WHERE ChartNotes.DateOfEncounter >= DATEADD(day, DATEDIFF(day, 0, GETDATE())- @StartDaysBack, 0 )
AND ChartNotes.DateOfEncounter < DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1 - @StopDaysBack, 0)
AND ChartNotes.NoteCategoryID = 1
AND ChartNotes.IsDeleted = 0
AND ((ChartNotes.XOutID IS NULL) OR (ChartNotes.XOutID < 1))
AND (@AuthorID < 0 OR ChartNotes.AuthorID = @AuthorID)
ORDER BY ChartNotes.AuthorID;

 

--kids under 1 not seen in 4 months
SELECT Patients.DisplayName, Patients.DateOfBirth, Patients.PrimaryDocID
FROM Patients
WHERE Patients.DateOfBirth > DATEADD(YEAR, -1, GETDATE())
AND Patients.IsActive = 1
AND PatientID NOT in
(
SELECT ChartNotes.PatientID
FROM ChartNotes
WHERE ChartNotes.DateOfEncounter > DATEADD(MONTH, -4, GETDATE())
AND ChartNotes.NoteCategoryID = '1'
)
ORDER BY Patients.DisplayName ASC

 

--kids 1-2 not seen in 6 months
SELECT Patients.DisplayName, Patients.DateOfBirth, Patients.PrimaryDocID
FROM Patients
WHERE
Patients.DateOfBirth < DATEADD(YEAR, -1,GETDATE())
AND Patients.DateOfBirth > DATEADD(YEAR, -2, GETDATE())
AND Patients.IsActive = 1
AND PatientID NOT in
(
SELECT ChartNotes.PatientID
FROM ChartNotes
WHERE ChartNotes.DateOfEncounter > DATEADD(MONTH, -6, GETDATE())
AND ChartNotes.NoteCategoryID = '1'
)
ORDER BY Patients.DisplayName ASC

 

--kids 2-6 not seen in 12 months
SELECT Patients.DisplayName, Patients.DateOfBirth, Patients.PrimaryDocID
FROM Patients
WHERE
Patients.DateOfBirth < DATEADD(YEAR, -2,GETDATE())
AND Patients.DateOfBirth > DATEADD(YEAR, -6, GETDATE())
AND Patients.IsActive = 1
AND PatientID NOT in
(
SELECT ChartNotes.PatientID
FROM ChartNotes
WHERE ChartNotes.DateOfEncounter > DATEADD(MONTH, -12, GETDATE())
AND ChartNotes.NoteCategoryID = '1'
)
ORDER BY Patients.DisplayName ASC

--Patients on omeprazole and Plavix
SELECT DISTINCT Patients.DisplayName, Patients.ChartNum, Patients.DateOfBirth
FROM Patients JOIN Medications ON Patients.PatientID = Medications.PatientID
WHERE
(
Medications.MedicationText LIKE '%omeprazole%'
OR Medications.MedicationText LIKE '%prilosec%'
)
AND Patients.PatientID IN
(SELECT Patients.PatientID
FROM Patients JOIN Medications ON Patients.PatientID = Medications.PatientID
WHERE MedicationText LIKE '%plavix%'
OR MedicationText LIKE '%clopidogrel%')

 

---Patients with Diabetes
SELECT DISTINCT Patients.DisplayName, Patients.ChartNum, Patients.DateOfBirth, Problems.Text, Problems.XOutID
FROM Patients JOIN Problems ON Patients.PatientID = Problems.PatientID
WHERE
Problems.ICDx LIKE '250%'
--not x'd out problem...
AND (Problems.XOutID IS NULL OR Problems.XOutID < 1)
-- not inactive problem
AND NOT Problems.Status = 2

--find last diabetic patient encounter and last a1c value (of those having any a1c values)
SELECT DISTINCT Patients.PatientID, Patients.ChartNum,
ChartNotes.ChartNoteID, ChartNotes.DateOfEncounter
,DateOfTest,
DATEDIFF(DD,DateOfTest,DateOfEncounter) AS Difference,
Tests.TextValue
FROM ChartNotes
LEFT JOIN Problems ON ChartNotes.PatientID = Problems.PatientID
LEFT JOIN Patients ON Patients.PatientID = ChartNotes.PatientID
LEFT JOIN Tests ON Tests.PatientID = Patients.PatientID
WHERE
(
((Problems.ICDx LIKE '%250.%' OR
Problems.Text LIKE '%DIABETES%' OR
Problems.Text LIKE '%DM %' )
AND (NOT Problems.ICDx LIKE '%V18.0%'))
)
AND
(
--cat 1 = office visit
NoteCategoryID = 1
)
AND
(
ChartNotes.DateOfEncounter = (SELECT MAX(DateOfEncounter) FROM ChartNotes WHERE
(ChartNotes.PatientID = Patients.PatientID) AND
(NoteCategoryID = 1) AND
((Problems.ICDx LIKE '%250.%' OR
Problems.Text LIKE '%DIABETES%' OR
Problems.Text LIKE '%DM %' )
AND (NOT Problems.ICDx LIKE '%V18.0%')))
)
AND
(
(Tests.TestNameID = 49) AND
(Tests.DateOfTest = (SELECT MAX(DateOfTest) FROM Tests WHERE
(Tests.PatientID = Patients.PatientID) AND
(Tests.TestNameID = 49)))
)
ORDER BY Patients.PatientID

--but need second query to find
--find last diabetic patient encounter who have no a1c value
SELECT DISTINCT Patients.PatientID, Patients.ChartNum,
ChartNotes.ChartNoteID, ChartNotes.DateOfEncounter
FROM ChartNotes
LEFT JOIN Problems ON ChartNotes.PatientID = Problems.PatientID
LEFT JOIN Patients ON Patients.PatientID = ChartNotes.PatientID
LEFT JOIN Tests ON Tests.PatientID = Patients.PatientID
WHERE
(
((Problems.ICDx LIKE '%250.%' OR
Problems.Text LIKE '%DIABETES%' OR
Problems.Text LIKE '%DM %' )
AND (NOT Problems.ICDx LIKE '%V18.0%'))
)
AND
(
--cat 1 = office visit
NoteCategoryID = 1
)
AND
(
ChartNotes.DateOfEncounter = (SELECT MAX(DateOfEncounter) FROM ChartNotes WHERE
(ChartNotes.PatientID = Patients.PatientID) AND
(NoteCategoryID = 1) AND
((Problems.ICDx LIKE '%250.%' OR
Problems.Text LIKE '%DIABETES%' OR
Problems.Text LIKE '%DM %' )
AND (NOT Problems.ICDx LIKE '%V18.0%')))
)
AND
(
(SELECT COUNT(*) FROM Tests
WHERE Tests.PatientID = Patients.PatientID
AND Tests.TestNameID = 49 ) = 0
)
ORDER BY Patients.PatientID

 

 

--show patients over 65 seen over 5 times in the past year for PrimaryDocID of 1
SELECT DISTINCT Pats.DisplayName
FROM Patients Pats LEFT JOIN ChartNotes Chats ON Pats.PatientID = Chats.PatientID
WHERE Pats.DateOfBirth < DATEADD(YEAR,-65, GETDATE()) AND
Pats.PrimaryDocID = 1 AND
(SELECT COUNT(*) FROM Chartnotes C
WHERE C.PatientID = Pats.PatientID AND
C.NoteCategoryID = 1 AND --office visit
C.DateOfEncounter > DATEADD(YEAR,-1,GETDATE())) > 5 -- seen x times past year

 

 

Papsmears

--females 20-65 without hysterectomy seen in past @SeenInYYears years
DECLARE @SeenInYYears int = 5;

select count(distinct Patients.PatientID) As PapDenominator
from Patients
left join ChartNotes on Patients.PatientID = Chartnotes.PatientID
where
Patients.IsActive = 1
and Patients.DateOfBirth >= DATEADD(YEAR, -65, GETDATE())
and Patients.DateOfBirth <= DATEADD(YEAR, -20, GETDATE())
and Patients.Gender = 2 -- 2= female
and chartnotes.DateOfEncounter > DATEADD(YEAR, 0-@SeenInYYears, GETDATE())
and Patients.PatientID NOT IN (select distinct PastMedSurgHx.PatientID from PastMedSurgHx
-- look for hyst abbreviation but not hysteroscopy
where (PastMedSurgHx.Text LIKE '%hyst%' and PastMedSurgHx.Text not like '%hystero%')
or PastMedSurgHx.Text LIKE '%tah%')


--females 20-65 without hysterectomy seen in past @SeenInYYears years...
-- ...who had pap in past @x years
DECLARE @SeenInYYears int = 5;
--in this particular case subqueries much faster than joins
DECLARE @x int = 3;
select count(distinct Patients.PatientID) As PapNumerator
from Patients
left join ChartNotes on Patients.PatientID = Chartnotes.PatientID
--left join PreventiveEntries on Patients.PatientID = PreventiveEntries.PatientID
--left join Tests on Tests.PatientID = Patients.PatientID
where
Patients.IsActive = 1
and Patients.DateOfBirth >= DATEADD(YEAR, -65, GETDATE())
and Patients.DateOfBirth <= DATEADD(YEAR, -20, GETDATE())
and Patients.Gender = 2 -- 2= female
and chartnotes.DateOfEncounter > DATEADD(YEAR, 0-@SeenInYYears, GETDATE())
and Patients.PatientID NOT IN (select distinct PastMedSurgHx.PatientID from PastMedSurgHx
-- look for hyst abbreviation but not hysteroscopy
where (PastMedSurgHx.Text LIKE '%hyst%' and PastMedSurgHx.Text not like '%hystero%')
or PastMedSurgHx.Text LIKE '%tah%')

and ((Patients.PatientID IN (Select PreventiveEntries.PatientID from PreventiveEntries
where PreventiveEntries.PreventiveTaskID = 25
and PreventiveEntries.WhenDone > DATEADD(YEAR, 0-@x, GETDATE())))
or (Patients.PatientID IN (Select Tests.PatientID from Tests
where ((Tests.TestName LIKE '%pap%')
and (Tests.DateOfTest > DATEADD(YEAR, 0-@x, GETDATE()))))))


--females 20-65 without hysterectomy seen in past @SeenInPastYYears...
-- ...who haven't had pap in past @x years
DECLARE @SeenInYYears int = 5;
DECLARE @x int = 3;
select distinct Patients.DisplayName, Patients.PrimaryDocID
from Patients
left join ChartNotes on Patients.PatientID = Chartnotes.PatientID
left join PreventiveEntries on Patients.PatientID = PreventiveEntries.PatientID
left join Tests on Tests.PatientID = Patients.PatientID
where
Patients.IsActive = 1
and Patients.DateOfBirth >= DATEADD(YEAR, -65, GETDATE())
and Patients.DateOfBirth <= DATEADD(YEAR, -20, GETDATE())
and Patients.Gender = 2 -- 2= female
and chartnotes.DateOfEncounter > DATEADD(YEAR, 0-@SeenInYYears, GETDATE())
and Patients.PatientID NOT IN (select distinct PastMedSurgHx.PatientID from PastMedSurgHx
-- look for hyst abbreviation but not hysteroscopy
where (PastMedSurgHx.Text LIKE '%hyst%' and PastMedSurgHx.Text not like '%hystero%')
or PastMedSurgHx.Text LIKE '%tah%')
and Patients.PatientID not in (select PreventiveEntries.PatientID from PreventiveEntries
where PreventiveEntries.PreventiveTaskID = 25 -- 25=pap
and PreventiveEntries.WhenDone > DATEADD(YEAR, 0-@x, GETDATE()))
and Patients.PatientID not in (select Tests.PatientID from Tests
where (Tests.TestName LIKE '%pap%')
and (Tests.DateOfTest > DATEADD(YEAR, 0-@x, GETDATE())))

 

 

Pneumococcal Vaccine

--patients 65+ seen in past @SeenInYYears years
DECLARE @SeenInYYears int = 5;

select count(distinct Patients.PatientID) As Denominator
from Patients
left join ChartNotes on Patients.PatientID = Chartnotes.PatientID
where
Patients.IsActive = 1 and
Patients.DateOfBirth <= DATEADD(YEAR, -65, GETDATE()) and
chartnotes.DateOfEncounter >= DATEADD(YEAR, 0-@SeenInYYears, GETDATE())

--patients 65+ seen in past @SeenInYYears years...
-- ...who have had Pneumococcal vaccine
DECLARE @SeenInYYears int = 5;

select count(distinct Patients.PatientID) As Numerator
from Patients
left join ChartNotes on Patients.PatientID = Chartnotes.PatientID
left join PreventiveEntries on Patients.PatientID = PreventiveEntries.PatientID
where
Patients.IsActive = 1 and
Patients.DateOfBirth <= DATEADD(YEAR, -65, GETDATE()) and
chartnotes.DateOfEncounter >= DATEADD(YEAR, 0-@SeenInYYears, GETDATE())

and
PreventiveEntries.PreventiveTaskID = 8 -- 8 = pneumococcal vaccine

A1c > 9%

--select all patients who have had HgbA1c
select count(distinct Patients.PatientID) as denominator
from Patients
left join Tests on Patients.PatientID = tests.PatientID
where Tests.TestNameID = 49
and ((Tests.XOutID is null) or (Tests.XOutID < 1))

--select all patients who have had HgbA1c...
-- ... whose most recent was > 9%
select count(*) as numerator from
(select m.*,
row_number() over (
partition by m.PatientID
order by m.DateOfTest desc) as rn
from
(Select t.PatientID, t.DateOfTest, t.TestNameID, t.FloatValue from Tests t
where t.TestNameID = 49
and ((t.XOutID is null) or (t.XOutID < 1)))
m
) m2
where m2.rn = 1
and m2.FloatValue > 9

BMI

--Active patients @a age and over seen in the past @y years
DECLARE @y int = 1;
DECLARE @a int = 18

select count(distinct Patients.PatientID) As Denominator
from Patients
left join ChartNotes on Patients.PatientID = Chartnotes.PatientID
where
Patients.IsActive = 1 and
Patients.DateOfBirth <= DATEADD(YEAR, 0-@a, GETDATE()) and
chartnotes.DateOfEncounter >= DATEADD(YEAR, 0-@y, GETDATE())

--Active patients @a age and over seen in the past @y years...
-- ...Who have had bmi measured in past @m months including
-- ...a height measurement in the past @mHt months (can be longer that @m if desired)
DECLARE @y int = 1; --years
DECLARE @a int = 18; --age
DECLARE @m int = 6; -- months weight done within, e.g. 6
DECLARE @mHt int = 12; -- months height done within, e.g. 12
Select distinct recentHts.PatientID, htDate, ht, wtDate, wt,
wt/ht/ht as bmi
from
--hts
(Select * from
(Select v.PatientID, v.DateOfVitals as htDate, v.VitalsNameID, v.FloatValue as ht ,
row_number() over (
partition by v.PatientID
order by v.DateOfVitals desc) as rnHts

from VitalSigns v
where v.FloatValue > 0 and
(v.VitalsNameID = 1
or v.VitalsNameID = 2)
and ((v.XOutID is null ) or (v.XOutID < 1))
) as hts
where hts.rnHts = 1) as recentHts

join
--wts
(Select * from
(Select v.PatientID, v.DateOfVitals as wtDate, v.VitalsNameID, v.FloatValue as wt ,
row_number() over (
partition by v.PatientID
order by v.DateOfVitals desc) as rnWts

from VitalSigns v
where v.FloatValue > 0 and
(v.VitalsNameID = 3
or v.VitalsNameID = 4)
and ((v.XOutID is null ) or (v.XOutID < 1))
) as wts
where wts.rnWts = 1) as recentWts

on recentHts.PatientId = recentWts.PatientId
inner join Patients on Patients.PatientID = recentHts.PatientId
left join ChartNotes on Patients.PatientID = Chartnotes.PatientID

where
Patients.IsActive = 1 and
Patients.DateOfBirth <= DATEADD(YEAR, 0-@a, GETDATE()) and
chartnotes.DateOfEncounter >= DATEADD(YEAR, 0-@y, GETDATE()) and
recentWts.wtDate >= DATEADD(MONTH, 0-@m, GETDATE()) and
recentHts.htDate >= DATEADD(MONTH, 0-@mHt, GETDATE())

order by PatientID

--Active patients @a age and over seen in the past @y years...
-- ...Who have had bmi measured in past @m months including
-- ...a height measurement in the past @mHt months (can be longer that @m if desired)
-- ...and whose BMI is OUT OF NORMAL RANGE
DECLARE @y int = 1; --years
DECLARE @a int = 18; --age
DECLARE @m int = 6; -- months weight done within, e.g. 6
DECLARE @mHt int = 12; -- months height done within, e.g. 12
Select distinct recentHts.PatientID, htDate, ht, wtDate, wt,
wt/ht/ht as bmi
from
--hts
(Select * from
(Select v.PatientID, v.DateOfVitals as htDate, v.VitalsNameID, v.FloatValue as ht ,
row_number() over (
partition by v.PatientID
order by v.DateOfVitals desc) as rnHts

from VitalSigns v
where v.FloatValue > 0 and
(v.VitalsNameID = 1
or v.VitalsNameID = 2)
and ((v.XOutID is null ) or (v.XOutID < 1))
) as hts
where hts.rnHts = 1) as recentHts

join
--wts
(Select * from
(Select v.PatientID, v.DateOfVitals as wtDate, v.VitalsNameID, v.FloatValue as wt ,
row_number() over (
partition by v.PatientID
order by v.DateOfVitals desc) as rnWts

from VitalSigns v
where v.FloatValue > 0 and
(v.VitalsNameID = 3
or v.VitalsNameID = 4)
and ((v.XOutID is null ) or (v.XOutID < 1))
) as wts
where wts.rnWts = 1) as recentWts

on recentHts.PatientId = recentWts.PatientId
inner join Patients on Patients.PatientID = recentHts.PatientId
left join ChartNotes on Patients.PatientID = Chartnotes.PatientID

where
Patients.IsActive = 1 and
Patients.DateOfBirth <= DATEADD(YEAR, 0-@a, GETDATE()) and
chartnotes.DateOfEncounter >= DATEADD(YEAR, 0-@y, GETDATE()) and
recentWts.wtDate >= DATEADD(MONTH, 0-@m, GETDATE()) and
recentHts.htDate >= DATEADD(MONTH, 0-@mHt, GETDATE()) and
(
(Patients.DateOfBirth > DATEADD(YEAR, 0-65, GETDATE()) and (wt/ht/ht >= 25)) or
(Patients.DateOfBirth > DATEADD(YEAR, 0-65, GETDATE()) and (wt/ht/ht < 18.5)) or
(Patients.DateOfBirth <= DATEADD(YEAR, 0-65, GETDATE()) and (wt/ht/ht >= 30)) or
(Patients.DateOfBirth <= DATEADD(YEAR, 0-65, GETDATE()) and (wt/ht/ht < 22))
)

order by PatientID

--Active patients @a age and over seen in the past @y years...
-- ...Who have had bmi measured in past @m months including
-- ...a height measurement in the past @mHt months (can be longer that @m if desired)
-- ...and whose BMI is out of normal range
-- ... and intervention was DOCUMENTED in Plan, BMIDECLARE @y int = 1; --years
DECLARE @y int = 1; --years
DECLARE @a int = 18; --age
DECLARE @m int = 6; -- months weight done within, e.g. 6
DECLARE @mHt int = 12; -- months height done within, e.g. 12
Select distinct recentHts.PatientID, htDate, ht, wtDate, wt,
wt/ht/ht as bmi
from
--hts
(Select * from
(Select v.PatientID, v.DateOfVitals as htDate, v.VitalsNameID, v.FloatValue as ht ,
row_number() over (
partition by v.PatientID
order by v.DateOfVitals desc) as rnHts

from VitalSigns v
where v.FloatValue > 0 and
(v.VitalsNameID = 1
or v.VitalsNameID = 2)
and ((v.XOutID is null ) or (v.XOutID < 1))
) as hts
where hts.rnHts = 1) as recentHts

join
--wts
(Select * from
(Select v.PatientID, v.DateOfVitals as wtDate, v.VitalsNameID, v.FloatValue as wt ,
row_number() over (
partition by v.PatientID
order by v.DateOfVitals desc) as rnWts

from VitalSigns v
where v.FloatValue > 0 and
(v.VitalsNameID = 3
or v.VitalsNameID = 4)
and ((v.XOutID is null ) or (v.XOutID < 1))
) as wts
where wts.rnWts = 1) as recentWts

on recentHts.PatientId = recentWts.PatientId
inner join Patients on Patients.PatientID = recentHts.PatientId
inner join ChartNotes on Patients.PatientID = Chartnotes.PatientID
inner join QualityMeasurements on Patients.PatientID = QualityMeasurements.PatientID

where
Patients.IsActive = 1 and
Patients.DateOfBirth <= DATEADD(YEAR, 0-@a, GETDATE()) and
chartnotes.DateOfEncounter >= DATEADD(YEAR, 0-@y, GETDATE()) and
recentWts.wtDate >= DATEADD(MONTH, 0-@m, GETDATE()) and
recentHts.htDate >= DATEADD(MONTH, 0-@mHt, GETDATE()) and
(
(Patients.DateOfBirth > DATEADD(YEAR, 0-65, GETDATE()) and (wt/ht/ht >= 25)) or
(Patients.DateOfBirth > DATEADD(YEAR, 0-65, GETDATE()) and (wt/ht/ht < 18.5)) or
(Patients.DateOfBirth <= DATEADD(YEAR, 0-65, GETDATE()) and (wt/ht/ht >= 30)) or
(Patients.DateOfBirth <= DATEADD(YEAR, 0-65, GETDATE()) and (wt/ht/ht < 22))
)

and
QualityMeasurements.SpecDeveloper = 2 and
QualityMeasurements.SpecID = 421 and
QualityMeasurements.Date >= DATEADD(MONTH, 0-@m, GETDATE())
order by PatientID

DM Eye exam

--number of patients with Diabetes seen in last @y years
DECLARE @y int = 1;

select count(distinct Patients.PatientID) as denominator
from Patients
left join ChartNotes on Patients.PatientID = Chartnotes.PatientID
left join Problems on Patients.PatientID = Problems.PatientID
where
Patients.IsActive = 1 and
(Problems.XOutID is null or Problems.XOutID < 1) and
chartnotes.DateOfEncounter >= DATEADD(YEAR, 0-@y, GETDATE()) and
--has diabetes:
((Problems.Text LIKE '%diabetes%') or
(Problems.Text LIKE '%aodm%') or
(Problems.Text LIKE 'dm%') or
(Problems.ICDx LIKE '250.%') or
(Problems.ICD10 LIKE 'E1%'))

--number of patients with Diabetes seen in last @y years
-- who have had an eye exam in the past @m months
DECLARE @y int = 1; -- years seen within
DECLARE @m int = 12; -- months eye exam within

select count(distinct Patients.PatientID) as denominator
from Patients
left join ChartNotes on Patients.PatientID = Chartnotes.PatientID
left join Problems on Patients.PatientID = Problems.PatientID
left join PreventiveEntries on Patients.PatientID = PreventiveEntries.PatientID
where
Patients.IsActive = 1 and
(Problems.XOutID is null or Problems.XOutID < 1) and
chartnotes.DateOfEncounter >= DATEADD(YEAR, 0-@y, GETDATE()) and
--has diabetes:
(
(Problems.ICDx LIKE '250.%') or
(Problems.ICD10 LIKE 'E1%') or
(Problems.Text LIKE '%diabetes%') or
(Problems.Text LIKE '%aodm%') or
(Problems.Text LIKE 'dm%')
) and
-- has had eye exam documented (PreventiveTaskID=44)
(
(PreventiveEntries.WhenDone >= DATEADD(MONTH, 0-@m, GETDATE())) and
(PreventiveEntries.PreventiveTaskID = 44) and
(PreventiveEntries.XOutID is null)
)