Ich habe eine SQL entworfen, kann sie aber leider nicht testen, weil ich feststellen musste, dass bei mir sowieso 0 Patienten die Kriterien erfüllen. Vielleicht hilft es ja.
WITH
zeitraum AS (
SELECT
date_trunc('quarter', CURRENT_DATE) AS q_start,
date_trunc('quarter', CURRENT_DATE) - INTERVAL '9 months' AS q_minus_3_start,
date_trunc('quarter', CURRENT_DATE) - INTERVAL '3 months' AS vorquartal_start,
date_trunc('quarter', CURRENT_DATE) + INTERVAL '3 months' AS naechstes_quartal_start,
EXTRACT(YEAR FROM CURRENT_DATE)::int AS aktuelles_jahr,
EXTRACT(QUARTER FROM CURRENT_DATE)::int AS aktuelles_quartal
),
quartale AS (
SELECT
EXTRACT(YEAR FROM Q.datum)::int AS jahr,
EXTRACT(QUARTER FROM Q.datum)::int AS quartal
FROM (
SELECT
Z.q_start - INTERVAL '9 months' AS datum
FROM zeitraum Z
UNION ALL
SELECT
Z.q_start - INTERVAL '6 months'
FROM zeitraum Z
UNION ALL
SELECT
Z.q_start - INTERVAL '3 months'
FROM zeitraum Z
UNION ALL
SELECT
Z.q_start
FROM zeitraum Z
) Q
),
-- KV-Scheine des aktuellen Quartals und der drei Vorquartale
scheine_4q AS (
SELECT
A.Ident,
A.Patient_ident,
A.Jahr,
A.Quartal
FROM kvschein A
JOIN quartale Q
ON Q.jahr = A.Jahr
AND Q.quartal = A.Quartal
WHERE A.visible = true
AND A.Patient_ident IS NOT NULL
AND A.Patient_ident != 0
),
-- Diagnosen der vier relevanten Quartale
diagnosen_basis AS (
SELECT
S.Patient_ident AS patient_id,
S.Jahr AS jahr,
S.Quartal AS quartal,
UPPER(TRIM(I.Code::text)) AS icd,
UPPER(TRIM(COALESCE(D.Kennzeichen::text, ''))) AS kennzeichen,
COALESCE(D.Isddi, false) AS isddi,
CASE
WHEN UPPER(TRIM(I.Code::text)) IN (
'E03.0',
'E03.1',
'E03.4',
'E03.8',
'E03.9',
'E06.3'
)
THEN 'Schilddrüse'
WHEN
UPPER(TRIM(I.Code::text)) IN (
'E78.0',
'E78.2',
'E78.4',
'E78.5',
'E78.6',
'E78.9'
)
OR UPPER(TRIM(I.Code::text)) LIKE 'E78.8%'
THEN 'Fettstoffwechsel'
WHEN
(
UPPER(TRIM(I.Code::text)) LIKE 'I10.0%'
AND UPPER(TRIM(I.Code::text)) NOT LIKE 'I10.01%'
)
OR
(
UPPER(TRIM(I.Code::text)) LIKE 'I10.9%'
AND UPPER(TRIM(I.Code::text)) NOT LIKE 'I10.91%'
)
THEN 'Hypertonie'
WHEN UPPER(TRIM(I.Code::text)) LIKE 'M10.0%'
THEN 'Gicht'
ELSE NULL
END AS zielgruppe
FROM scheine_4q S
JOIN kvschein_quartalsdiagnosen KD
ON KD.Kvschein_ident = S.Ident
JOIN diagnose D
ON D.Ident = KD.Quartalsdiagnosen_ident
JOIN icdkatalogeintrag I
ON I.Ident = D.Icdkatalogeintrag_ident
),
-- Für 03100 geeignete gesicherte Diagnosen
ziel_diagnosen AS (
SELECT DISTINCT
patient_id,
jahr,
quartal,
zielgruppe,
icd
FROM diagnosen_basis
WHERE zielgruppe IS NOT NULL
AND kennzeichen = 'G'
),
ziel_diagnose_quartale AS (
SELECT DISTINCT
patient_id,
jahr,
quartal,
zielgruppe
FROM ziel_diagnosen
),
-- Genau eine zulässige Krankheitsgruppe.
-- Die Diagnose muss auch im aktuell laufenden Quartal vorliegen.
ziel_patienten AS (
SELECT
D.patient_id,
MIN(D.zielgruppe) AS zielgruppe
FROM ziel_diagnosen D
CROSS JOIN zeitraum Z
GROUP BY D.patient_id
HAVING COUNT(DISTINCT D.zielgruppe) = 1
AND MAX(
CASE
WHEN D.jahr = Z.aktuelles_jahr
AND D.quartal = Z.aktuelles_quartal
THEN 1
ELSE 0
END
) = 1
),
ziel_icds AS (
SELECT
D.patient_id,
string_agg(DISTINCT D.icd, ', ') AS icds
FROM ziel_diagnosen D
JOIN ziel_patienten Z
ON Z.patient_id = D.patient_id
AND Z.zielgruppe = D.zielgruppe
GROUP BY D.patient_id
),
-- Weitere chronische Erkrankungen
andere_diagnosen_basis AS (
SELECT
Z.patient_id,
Z.zielgruppe AS erlaubte_zielgruppe,
D.icd,
D.zielgruppe,
MAX(
CASE
WHEN D.isddi = true THEN 1
ELSE 0
END
) AS ist_dauerdiagnose,
COUNT(
DISTINCT D.jahr::text || 'Q' || D.quartal::text
) AS diagnose_quartale
FROM ziel_patienten Z
JOIN diagnosen_basis D
ON D.patient_id = Z.patient_id
WHERE D.isddi = true
OR D.kennzeichen = 'G'
GROUP BY
Z.patient_id,
Z.zielgruppe,
D.icd,
D.zielgruppe
),
weitere_chronische_erkrankung AS (
SELECT DISTINCT
patient_id
FROM andere_diagnosen_basis
WHERE
(
zielgruppe IS NULL
OR zielgruppe <> erlaubte_zielgruppe
)
AND
(
ist_dauerdiagnose = 1
OR diagnose_quartale >= 3
)
),
-- Kontakte der vier Quartale
kontakte_roh AS (
SELECT
S.Patient_ident AS patient_id,
S.Jahr AS jahr,
S.Quartal AS quartal,
UPPER(TRIM(E.Code::text)) AS code
FROM scheine_4q S
JOIN leistung L
ON L.Invkvschein_ident = S.Ident
JOIN ebmkatalogeintrag E
ON E.Ident = L.Ebmkatalogeintrag_ident
WHERE L.visible = true
AND L.Datum IS NOT NULL
),
kontakte_quartal AS (
SELECT
patient_id,
jahr,
quartal,
MAX(
CASE
WHEN code IN (
'03000',
'03001',
'03002',
'03003',
'03004',
'03005',
'03000V',
'03001V',
'03002V',
'03003V',
'03004V',
'03005V',
'01430',
'01430V',
'01435',
'01435V',
'01450',
'01450V'
)
THEN 1
ELSE 0
END
) AS hat_kontakt,
MAX(
CASE
WHEN code IN (
'03000',
'03001',
'03002',
'03003',
'03004',
'03005',
'03000V',
'03001V',
'03002V',
'03003V',
'03004V',
'03005V',
'01450',
'01450V'
)
THEN 1
ELSE 0
END
) AS hat_persoenlich_oder_video,
MAX(
CASE
WHEN code IN (
'03000',
'03001',
'03002',
'03003',
'03004',
'03005'
)
THEN 1
ELSE 0
END
) AS hat_praesenzkontakt
FROM kontakte_roh
GROUP BY
patient_id,
jahr,
quartal
),
-- Kontakt zählt nur in Quartalen mit passender gesicherter Diagnose
kontakte_mit_diagnose AS (
SELECT
Z.patient_id,
Z.zielgruppe,
D.jahr,
D.quartal,
COALESCE(K.hat_kontakt, 0)
AS hat_kontakt,
COALESCE(K.hat_persoenlich_oder_video, 0)
AS hat_persoenlich_oder_video,
COALESCE(K.hat_praesenzkontakt, 0)
AS hat_praesenzkontakt
FROM ziel_patienten Z
JOIN ziel_diagnose_quartale D
ON D.patient_id = Z.patient_id
AND D.zielgruppe = Z.zielgruppe
LEFT JOIN kontakte_quartal K
ON K.patient_id = D.patient_id
AND K.jahr = D.jahr
AND K.quartal = D.quartal
),
kontakt_auswertung AS (
SELECT
K.patient_id,
K.zielgruppe,
SUM(K.hat_kontakt)
AS kontaktquartale,
SUM(K.hat_persoenlich_oder_video)
AS persoenlich_video_quartale,
SUM(K.hat_praesenzkontakt)
AS praesenz_quartale,
MAX(
CASE
WHEN K.jahr = Z.aktuelles_jahr
AND K.quartal = Z.aktuelles_quartal
THEN K.hat_persoenlich_oder_video
ELSE 0
END
) AS aktueller_pers_video_kontakt
FROM kontakte_mit_diagnose K
CROSS JOIN zeitraum Z
GROUP BY
K.patient_id,
K.zielgruppe
),
-- Erkrankungsspezifische GKV-Dauermedikation.
-- Betrachtet werden aktuelles Quartal und direktes Vorquartal.
medikamente_roh AS (
SELECT
P.Ident AS patient_id,
A.datum,
UPPER(
TRIM(
SPLIT_PART(
COALESCE(A.atcBeiVerordnung::text, ''),
'(',
1
)
)
) AS atc,
TRIM(
CASE
WHEN A.isWirkstoffverordnung = true
THEN COALESCE(
A.nameBeiWirkstoffverordnung::text,
''
)
ELSE COALESCE(
A.nameBeiVerordnung::text,
''
)
END
) AS medikament_name
FROM medikamentenverordnung A
JOIN patientendetailsrelationen_verordnungen R
ON R.verordnungen_ident = A.Ident
JOIN patientendetails PD
ON PD.patientendetailsrelationen_ident =
R.patientendetailsrelationen_ident
JOIN patient P
ON P.patientendetails_ident = PD.Ident
CROSS JOIN zeitraum Z
WHERE A.visible = true
AND A.rezepttyp = 1
AND A.dauermedikation = true
AND A.datum >= Z.vorquartal_start
AND A.datum < Z.naechstes_quartal_start
),
medikamente_ziel AS (
SELECT
Z.patient_id,
Z.zielgruppe,
M.atc,
M.medikament_name,
CASE
WHEN M.atc <> ''
THEN M.atc
ELSE 'NAME:' || UPPER(M.medikament_name)
END AS med_key
FROM ziel_patienten Z
JOIN medikamente_roh M
ON M.patient_id = Z.patient_id
WHERE
(
Z.zielgruppe = 'Schilddrüse'
AND M.atc LIKE 'H03AA%'
)
OR
(
Z.zielgruppe = 'Fettstoffwechsel'
AND M.atc LIKE 'C10%'
)
OR
(
Z.zielgruppe = 'Hypertonie'
AND (
M.atc LIKE 'C02%'
OR M.atc LIKE 'C03%'
OR M.atc LIKE 'C07%'
OR M.atc LIKE 'C08%'
OR M.atc LIKE 'C09%'
)
)
OR
(
Z.zielgruppe = 'Gicht'
AND M.atc LIKE 'M04A%'
)
),
medikament_auswertung AS (
SELECT
patient_id,
zielgruppe,
COUNT(DISTINCT med_key)
AS anzahl_medikamente,
string_agg(
DISTINCT medikament_name,
' | '
) AS medikamente,
string_agg(
DISTINCT atc,
' | '
) AS atc_codes
FROM medikamente_ziel
WHERE med_key IS NOT NULL
AND med_key <> ''
AND med_key <> 'NAME:'
GROUP BY
patient_id,
zielgruppe
),
-- HZV im aktuell laufenden Quartal ausschließen
hzv_aktuell AS (
SELECT DISTINCT
HD.patient_ident AS patient_id
FROM hzvschein HS
JOIN hzvdetails HD
ON HD.Ident = HS.Hzvdetails_ident
CROSS JOIN zeitraum Z
WHERE HS.Jahr = Z.aktuelles_jahr
AND HS.Quartal = Z.aktuelles_quartal
AND HS.visible = true
AND HD.patient_ident IS NOT NULL
AND HD.patient_ident != 0
),
hzv_symbol_aktuell AS (
SELECT DISTINCT
S.Patient_ident AS patient_id
FROM scheine_4q S
JOIN leistung L
ON L.Invkvschein_ident = S.Ident
JOIN ebmkatalogeintrag E
ON E.Ident = L.Ebmkatalogeintrag_ident
CROSS JOIN zeitraum Z
WHERE S.Jahr = Z.aktuelles_jahr
AND S.Quartal = Z.aktuelles_quartal
AND L.visible = true
AND UPPER(TRIM(E.Code::text)) IN (
'88192',
'88194',
'88196'
)
),
-- Geburtsdatum
patienten_daten AS (
SELECT
P.Ident AS patient_id,
P.nachname,
P.vorname,
CASE
WHEN P.Geburtsdatum IS NULL
THEN NULL
WHEN P.Geburtsdatum::text \~
'^[0-9]{4}-[0-9]{2}-[0-9]{2}'
THEN SUBSTRING(
P.Geburtsdatum::text FROM 1 FOR 10
)::date
WHEN P.Geburtsdatum::text \~
'^[0-9]{1,2}/[0-9]{1,2}/[0-9]{2}'
THEN make_date(
CASE
WHEN
SPLIT_PART(
SPLIT_PART(
P.Geburtsdatum::text,
' ',
1
),
'/',
3
)::int <=
MOD(EXTRACT(YEAR FROM CURRENT_DATE)::int, 100)
THEN
2000 +
SPLIT_PART(
SPLIT_PART(
P.Geburtsdatum::text,
' ',
1
),
'/',
3
)::int
ELSE
1900 +
SPLIT_PART(
SPLIT_PART(
P.Geburtsdatum::text,
' ',
1
),
'/',
3
)::int
END,
SPLIT_PART(
SPLIT_PART(
P.Geburtsdatum::text,
' ',
1
),
'/',
1
)::int,
SPLIT_PART(
SPLIT_PART(
P.Geburtsdatum::text,
' ',
1
),
'/',
2
)::int
)
ELSE NULL
END AS geburtsdatum
FROM patient P
)
SELECT
P.patient_id::text
AS patient_id,
P.nachname::text
AS nachname,
P.vorname::text
AS vorname,
P.geburtsdatum::text
AS geburtsdatum,
EXTRACT(
YEAR FROM age(
CURRENT_DATE,
P.geburtsdatum
)
)::int::text
AS alter,
Z.zielgruppe::text
AS chronische_erkrankung,
I.icds::text
AS icd,
M.medikamente::text
AS dauermedikament,
M.atc_codes::text
AS atc,
K.kontaktquartale::text
AS kontaktquartale_von_4,
K.persoenlich_video_quartale::text
AS persoenlich_video_quartale,
K.praesenz_quartale::text
AS davon_praesenz_quartale,
(
EXTRACT(YEAR FROM CURRENT_DATE)::int::text
|| 'Q'
|| EXTRACT(QUARTER FROM CURRENT_DATE)::int::text
)::text
AS quartal,
'03100'::text
AS gop
FROM ziel_patienten Z
JOIN patienten_daten P
ON P.patient_id = Z.patient_id
JOIN ziel_icds I
ON I.patient_id = Z.patient_id
JOIN kontakt_auswertung K
ON K.patient_id = Z.patient_id
AND K.zielgruppe = Z.zielgruppe
JOIN medikament_auswertung M
ON M.patient_id = Z.patient_id
AND M.zielgruppe = Z.zielgruppe
LEFT JOIN weitere_chronische_erkrankung C
ON C.patient_id = Z.patient_id
LEFT JOIN hzv_aktuell H
ON H.patient_id = Z.patient_id
LEFT JOIN hzv_symbol_aktuell HS
ON HS.patient_id = Z.patient_id
WHERE
P.geburtsdatum IS NOT NULL
AND EXTRACT(
YEAR FROM age(
CURRENT_DATE,
P.geburtsdatum
)
) BETWEEN 18 AND 74
AND C.patient_id IS NULL
AND K.kontaktquartale >= 3
AND K.persoenlich_video_quartale >= 2
AND K.praesenz_quartale >= 1
AND K.aktueller_pers_video_kontakt = 1
AND M.anzahl_medikamente = 1
AND H.patient_id IS NULL
AND HS.patient_id IS NULL
ORDER BY
P.nachname::text,
P.vorname::text,
P.patient_id::text
;
