08fadfd4-1b24-45dc-b852-a7faeaf17433SQLData Source=SQLSERVER\SQLSERVER2005;Initial Catalog=MPNetMD111intrue8.5in0.25cmtrue2.5cm2.5cm2.5cmbbea7f1c-e3ea-48e2-8c79-664ee32d026121cmNotGivenCountSystem.Int32GivenCountSystem.Int32NotGivenCapitatedSystem.Int32GivenCapitatedSystem.Int32NotGivenRegularAbove65System.Int32GivenRegularAbove65System.Int32NotGivenCapitatedAbove65System.Int32GivenCapitatedAbove65System.Int32PartyGUIDSystem.GuidFirstNameSystem.StringLastNameSystem.StringNational_RegistrationSystem.StringDOBSystem.DateTimeIsCapitatedSystem.StringAgeSystem.Int32MPNetMD1Declare @RegularNotGiven TABLE
(
PartyGUID UniqueIdentifier,
FirstName varchar(50),
LastName varchar(50),
National_Registration varchar(50),
DOB datetime,
IsCapitated varchar(10),
Age int
)
INSERT INTO @RegularNotGiven ( PartyGUID , FirstName , LastName , National_Registration , DOB , IsCapitated , Age )
select distinct p.PartyGUID,p.firstname,p.lastname,p.NationalRegistration as National_Registration,p.dob,
case when s.column7 = 'Capitation' AND ( GetDate() >= s.Column5 OR s.Column5 = '' OR s.Column5 IS NULL) AND ( GetDate() <= s.Column6 OR s.Column6 = '' OR s.Column6 IS NULL)
Then 'Yes'
Else 'No'
End IsCapitated,
DateDiff(yy, DOB, GetDate()) - CASE WHEN (100 * MONTH(GetDate()) + DAY(GetDate())) < (100 * MONTH(DOB) + DAY(DOB)) THEN 1 ELSE 0 END As Age
from patients p
inner join record r on p.partyguid = r.subjectguid
left outer join currentproblems cp on cp.recordguid = p.recordguid
left outer join recorddata rd on rd.recordguid = p.recordguid
left outer join schemes s on s.partyguid = p.partyguid and s.column7= 'Capitation'
and s.column5 = (select max(column5) from schemes where schemes.partyguid = p.partyguid and schemes.column7 = 'Capitation')
where
(
(r.chartstatusId = 101366)
AND (
(cp.Description LIKE '%diab%') OR
(cp.Description LIKE '%asthma%') OR
(cp.Description LIKE '%addison%') OR
(cp.Description LIKE '%bipolar mood disorder%') OR
(cp.Description LIKE '%Bronchiectasis%') OR
(cp.Description LIKE '%Cardiac Failure%') OR
(cp.Description LIKE '%Cardiomyopathy%') OR
(cp.Description LIKE '%Chronic Renal%') OR
(cp.Description LIKE '%Chronic Obstructive Pulmonary%') OR
(cp.Description LIKE '%Coronary Artery%') OR
(cp.Description LIKE '%Crohn%') OR
(cp.Description LIKE '%Dysrhythmias%') OR
(cp.Description LIKE '%Dysrhythmias%') OR
(cp.Description LIKE '%Epilepsy%') OR
(cp.Description LIKE '%Glaucoma%') OR
(cp.Description LIKE '%Haemophilia%') OR
(cp.Description LIKE '%HIV%') OR
(cp.Description LIKE '%Aids%') OR
(cp.Description LIKE '%Hyperlipidaemia%') OR
(cp.Description LIKE '%Hypertension%') OR
(cp.Description LIKE '%Hypothyroidism%') OR
(cp.Description LIKE '%Multiple Sclerosis%') OR
(cp.Description LIKE '%Parkinson%') OR
(cp.Description LIKE '%Rheumatoid Arthritis%') OR
(cp.Description LIKE '%Schizophrenia%') OR
(cp.Description LIKE '%Systemic Lupus Erythematosus%') OR
(cp.Description LIKE '%Ulcerative Colitis%') OR
((DateDiff(yy, DOB, GetDate()) - CASE WHEN (100 * MONTH(GetDate()) + DAY(GetDate())) < (100 * MONTH(DOB) + DAY(DOB)) THEN 1 ELSE 0 END >= 65) )
)
AND (p.partyguid NOT IN (
select p.partyguid from patients p
Left outer join recorddata r on p.recordguid = r.recordguid
where usetypeid = 100009 and formattypeid = 100013 and column1 > DateAdd(Year,DateDiff(Year,0,GetDate()),0) and column2 like '%flu%'
and r.removeddate is null
)
)
)
Declare @RegularGiven TABLE
(
PartyGUID UniqueIdentifier,
FirstName varchar(50),
LastName varchar(50),
National_Registration varchar(50),
DOB datetime,
IsCapitated varchar(10),
Age int
)
INSERT INTO @RegularGiven ( PartyGUID , FirstName , LastName , National_Registration , DOB , IsCapitated , Age )
select distinct p.PartyGUID,p.firstname,p.lastname,p.NationalRegistration as National_Registration,p.dob,
case when s.column7 = 'Capitation' AND ( GetDate() >= s.Column5 OR s.Column5 = '' OR s.Column5 IS NULL) AND ( GetDate() <= s.Column6 OR s.Column6 = '' OR s.Column6 IS NULL)
Then 'Yes'
Else 'No'
End IsCapitated,
DateDiff(yy, DOB, GetDate()) - CASE WHEN (100 * MONTH(GetDate()) + DAY(GetDate())) < (100 * MONTH(DOB) + DAY(DOB)) THEN 1 ELSE 0 END As Age
from patients p
inner join record r on p.partyguid = r.subjectguid
left outer join currentproblems cp on cp.recordguid = p.recordguid
left outer join recorddata rd on rd.recordguid = p.recordguid
left outer join schemes s on s.partyguid = p.partyguid and s.column7= 'Capitation'
and s.column5 = (select max(column5) from schemes where schemes.partyguid = p.partyguid and schemes.column7 = 'Capitation')
where
(
(r.chartstatusId = 101366)
AND (
(cp.Description LIKE '%diab%') OR
(cp.Description LIKE '%asthma%') OR
(cp.Description LIKE '%addison%') OR
(cp.Description LIKE '%bipolar mood disorder%') OR
(cp.Description LIKE '%Bronchiectasis%') OR
(cp.Description LIKE '%Cardiac Failure%') OR
(cp.Description LIKE '%Cardiomyopathy%') OR
(cp.Description LIKE '%Chronic Renal%') OR
(cp.Description LIKE '%Chronic Obstructive Pulmonary%') OR
(cp.Description LIKE '%Coronary Artery%') OR
(cp.Description LIKE '%Crohn%') OR
(cp.Description LIKE '%Dysrhythmias%') OR
(cp.Description LIKE '%Dysrhythmias%') OR
(cp.Description LIKE '%Epilepsy%') OR
(cp.Description LIKE '%Glaucoma%') OR
(cp.Description LIKE '%Haemophilia%') OR
(cp.Description LIKE '%HIV%') OR
(cp.Description LIKE '%Aids%') OR
(cp.Description LIKE '%Hyperlipidaemia%') OR
(cp.Description LIKE '%Hypertension%') OR
(cp.Description LIKE '%Hypothyroidism%') OR
(cp.Description LIKE '%Multiple Sclerosis%') OR
(cp.Description LIKE '%Parkinson%') OR
(cp.Description LIKE '%Rheumatoid Arthritis%') OR
(cp.Description LIKE '%Schizophrenia%') OR
(cp.Description LIKE '%Systemic Lupus Erythematosus%') OR
(cp.Description LIKE '%Ulcerative Colitis%') OR
((DateDiff(yy, DOB, GetDate()) - CASE WHEN (100 * MONTH(GetDate()) + DAY(GetDate())) < (100 * MONTH(DOB) + DAY(DOB)) THEN 1 ELSE 0 END >= 65) )
)
AND (p.partyguid IN (
select p.partyguid from patients p
Left outer join recorddata r on p.recordguid = r.recordguid
where usetypeid = 100009 and formattypeid = 100013 and column1 > DateAdd(Year,DateDiff(Year,0,GetDate()),0) and column2 like '%flu%'
and r.removeddate is null
)
)
)
select
(
select count(*) as NotGiven from @RegularNotGiven
)NotGivenCount,
(
select count(*) as Given from @RegularGiven
) GivenCount,
(
SELECT COUNT(*)
FROM @RegularNotGiven where IsCapitated = 'Yes'
) NotGivenCapitated,
(
SELECT COUNT(*)
FROM @RegularGiven where IsCapitated = 'Yes'
)GivenCapitated,
(
SELECT COUNT(*)
FROM @RegularNotGiven where Age >= 65
) NotGivenRegularAbove65,
(
SELECT COUNT(*)
FROM @RegularGiven where Age >= 65
)GivenRegularAbove65,
(
SELECT COUNT(*)
FROM @RegularNotGiven where Age >= 65 and IsCapitated = 'Yes'
) NotGivenCapitatedAbove65,
(
SELECT COUNT(*)
FROM @RegularGiven where Age >= 65 and IsCapitated = 'Yes'
)GivenCapitatedAbove65,
*
from @RegularNotGiven order by Lastname,FirstName;true38.12709cm1cmtextbox2718.25cm16cm6true0.25cm12.25cmThe covered chronic diseases are :
Addison’s Disease
Asthma
Bipolar Mood Disorder
Bronchiectasis
Cardiac Failure
Cardiomyopathy
Chronic Renal Disease
Chronic Obstructive Pulmonary Disease
Coronary Artery Disease
Crohn’s Disease
Diabetes Insipidus
Diabetes Mellitus Type I
Diabetes Mellitus Type II
Dysrhythmias
Epilepsy
Glaucoma
Haemophilia
HIV/Aids
Hyperlipidaemia
Hypertension
Hypothyroidism
Multiple Sclerosis
Parkinson’s Disease
Rheumatoid Arthritis
Schizophrenia
Systemic Lupus Erythematosus
Ulcerative Colitistextbox101.25cm15.75cm5true0.25cm0.63492cmRegular Patients either over 65y or has a chronic disease in their current problems.
textbox415.25cm8cm3true0.75cmNot Vaccinated this year. Outside0true2MPNetMD13030Simple5006.5cmPlaintrueOutside0truetruetruePie11.86905cmDefault=Round(Fields!GivenCount.Value/(Fields!NotGivenCount.Value+Fields!GivenCount.Value)*100,2)ToptrueAuto6pt=Round(Fields!NotGivenCount.Value/(Fields!NotGivenCount.Value+Fields!GivenCount.Value)*100,2)ToptrueAuto6pt