CREATE SEQUENCE TMPSEQGROUP INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
CREATE TABLE PATIENT
(
SSN VARCHAR2(9),
EXIT_DATE DATE
);
CREATE TABLE CONSUMPTION
(
SSN VARCHAR2(9),
DRUG_ID VARCHAR2(9),
SELL_DATE DATE
);
CREATE TABLE SEARCHED_DRUG
(
DRUG_ID VARCHAR2(9)
);
CREATE TABLE TMPGROUP
(
GROUP_ID NUMBER(5) NOT NULL,
GROUP_DEFINITION VARCHAR2(1500),
INSERTED DATE
);
CREATE TABLE TMPMEMBER
(
GROUP_ID NUMBER(5),
SSN VARCHAR2(9),
QUARTER VARCHAR2(6),
INSERTED DATE
);
CREATE TABLE RESULTGROUP
(
PREV_QUARTER VARCHAR2(6),
ACT_QUARTER VARCHAR2(6),
PREV_GROUP VARCHAR2(1500),
ACT_GROUP VARCHAR2(1500),
SSN_COUNT NUMBER(8),
INSERTED DATE
);
PROCEDURE PREPARE_QUARTER(ABEGIN IN DATE, AEND IN DATE, AQUARTER VARCHAR2) IS
/*
Creates all combinations for a specific quarter, and put each patient in a group for that quarter.
*/
varSSN VARCHAR2(9);
varDRUG_ID VARCHAR2(9);
varPREV_SSN VARCHAR2(9);
varGROUP_DEFINITION VARCHAR2(1500);
varGROUP_ID NUMBER(5, 0);
varEXISTS NUMBER(1, 0);
CURSOR CUR_SSN_DRUG_ID IS
SELECT SSN, CONSUMPTION.DRUG_ID
FROM CONSUMPTION
INNER JOIN SEARCHED_DRUG ON CONSUMPTION.DRUG_ID = SEARCHED_DRUG.DRUG_ID
WHERE (SELL_DATE BETWEEN ABEGIN AND AEND)
GROUP BY SSN, CONSUMPTION.DRUG_ID
ORDER BY SSN, CONSUMPTION.DRUG_ID;
BEGIN
varGROUP_DEFINITION := '-';
OPEN CUR_SSN_DRUG_ID;
LOOP
FETCH CUR_SSN_DRUG_ID INTO varSSN, varDRUG_ID;
EXIT WHEN CUR_SSN_DRUG_ID%NOTFOUND;
varEXISTS := -1;
IF varPREV_SSN IS NULL THEN
varPREV_SSN := varSSN;
END IF;
--Same patient as previous
IF varSSN = varPREV_SSN THEN
varGROUP_DEFINITION := varGROUP_DEFINITION || varDRUG_ID || '-';
ELSE
--Is it an existing group?
SELECT CASE WHEN EXISTS(SELECT 1 FROM tmpGROUP WHERE GROUP_DEFINITION = varGROUP_DEFINITION) THEN 1 ELSE 0 END INTO varEXISTS FROM DUAL;
IF varEXISTS > 0 THEN
SELECT GROUP_ID INTO varGROUP_ID
FROM tmpGROUP
WHERE GROUP_DEFINITION = varGROUP_DEFINITION;
ELSE
varGROUP_ID := NULL;
END IF;
IF varGROUP_ID IS NULL THEN
--New group
SELECT tmpseqGROUP.NEXTVAL INTO varGROUP_ID FROM DUAL;
INSERT INTO tmpGROUP VALUES (varGROUP_ID, varGROUP_DEFINITION, CURRENT_DATE);
END IF;
--Inserting a member in the group
INSERT INTO tmpMEMBER VALUES (varGROUP_ID, varPREV_SSN, AQUARTER, CURRENT_DATE);
varPREV_SSN := varSSN;
varGROUP_DEFINITION := '-' || varDRUG_ID || '-';
END IF;
END LOOP;
--Maybe the last one wasn't inserted
IF varEXISTS = -1 THEN
--Is it an existing group?
SELECT CASE WHEN EXISTS(SELECT 1 FROM tmpGROUP WHERE GROUP_DEFINITION = varGROUP_DEFINITION) THEN 1 ELSE 0 END INTO varEXISTS FROM DUAL;
IF varEXISTS > 0 THEN
SELECT GROUP_ID INTO varGROUP_ID
FROM tmpGROUP
WHERE GROUP_DEFINITION = varGROUP_DEFINITION;
ELSE
varGROUP_ID := NULL;
END IF;
IF varGROUP_ID IS NULL THEN
--New group
SELECT tmpseqGROUP.NEXTVAL INTO varGROUP_ID FROM DUAL;
INSERT INTO tmpGROUP VALUES (varGROUP_ID, varGROUP_DEFINITION, CURRENT_DATE);
END IF;
--Inserting a member in the group
INSERT INTO tmpMEMBER VALUES (varGROUP_ID, varPREV_SSN, AQUARTER, CURRENT_DATE);
END IF;
CLOSE CUR_SSN_DRUG_ID;
END;
PROCEDURE COUNT_GROUP(APREV_QUARTER VARCHAR2, APREV_BEGIN IN DATE, APREV_END IN DATE, AACT_QUARTER VARCHAR2, ABEGIN IN DATE, AEND IN DATE)IS
/*
Counts the patients' movements between groups for the sepcified quarters.
*/
varACT_GROUP_DEFINITION VARCHAR2(1500);
varPREV_GROUP_DEFINITION VARCHAR2(1500);
varSSN_COUNT NUMBER(8, 0);
CURSOR CUR_GROUP_OUTER IS
SELECT GROUP_DEFINITION
FROM tmpGROUP
ORDER BY GROUP_DEFINITION;
CURSOR CUR_GROUP_INNER IS
SELECT GROUP_DEFINITION
FROM tmpGROUP
ORDER BY GROUP_DEFINITION;
BEGIN
--Steps through all groups
OPEN CUR_GROUP_OUTER;
LOOP
FETCH CUR_GROUP_OUTER INTO varACT_GROUP_DEFINITION;
EXIT WHEN CUR_GROUP_OUTER%NOTFOUND;
--Steps through all groups again to create all pairs for the movements
OPEN CUR_GROUP_INNER;
LOOP
FETCH CUR_GROUP_INNER INTO varPREV_GROUP_DEFINITION;
EXIT WHEN CUR_GROUP_INNER%NOTFOUND;
--Counts the movements between the two groups
--(if the two groups are the same, the count means patients who consume the same set of drugs as in the previous quarter)
SELECT COUNT(DISTINCT ACT_MEMBER.SSN)
INTO varSSN_COUNT
FROM tmpMEMBER ACT_MEMBER
INNER JOIN tmpMEMBER PREV_MEMBER ON ACT_MEMBER.SSN = PREV_MEMBER.SSN
INNER JOIN tmpGROUP ACT_GROUP ON ACT_MEMBER.GROUP_ID = ACT_GROUP.GROUP_ID
INNER JOIN tmpGROUP PREV_GROUP ON PREV_MEMBER.GROUP_IDE = PREV_GROUP.GROUP_ID
WHERE (ACT_MEMBER.QUARTER = AACT_QUARTER) AND (PREV_MEMBER.QUARTER = APREV_QUARTER) AND
(ACT_GROUP.GROUP_DEFINITION = varACT_GROUP_DEFINITION) AND
(PREV_GROUP.GROUP_DEFINITION = varPREV_GROUP_DEFINITION);
INSERT INTO RESULTGROUP VALUES (
APREV_QUARTER, AACT_QUARTER, varPREV_GROUP_DEFINITION, varACT_GROUP_DEFINITION, varSSN_COUNT, CURRENT_DATE);
END LOOP;
CLOSE CUR_GROUP_INNER;
--Counts patients, who were in the group in the previous quarter, but now they aren't in any of the groups
SELECT COUNT(DISTINCT SSN)
INTO varSSN_COUNT
FROM tmpMEMBER
INNER JOIN tmpGROUP ON tmpMEMBER.GROUP_ID = tmpGROUP.GROUP_ID
INNER JOIN PATIENT ON PATIENT.SSN = tmpMEMBER.SSN
WHERE (tmpMEMBER.QUARTER = APREV_QUARTER) AND
(tmpGROUP.GROUP_DEFINITION = varACT_GROUP_DEFINITION) AND
(NOT EXISTS(SELECT 1 FROM tmpMEMBER INNER_MEMBER
WHERE (INNER_MEMBER.QUARTER = AACT_QUARTER) AND
(INNER_MEMBER.SSN = tmpMEMBER.SSN))) AND
(PATIENT.EXIT_DATE IS NULL OR PATIENT.EXIT_DATE > AEND);
INSERT INTO RESULTGROUP VALUES (
APREV_QUARTER, AACT_QUARTER, varACT_GROUP_DEFINITION, 'NOMORE', varSSN_COUNT, CURRENT_DATE);
--Counts patients, who were in the group in the previous quarter, but now are dead
SELECT COUNT(DISTINCT SSN)
INTO varSSN_COUNT
FROM tmpMEMBER
INNER JOIN tmpGROUP ON tmpMEMBER.GROUP_ID = tmpGROUP.GROUP_ID
INNER JOIN PATIENT ON PATIENT.SSN = tmpMEMBER.SSN
WHERE (tmpMEMBER.QUARTER = APREV_QUARTER) AND
(tmpGROUP.GROUP_DEFINITION = varACT_GROUP_DEFINITION) AND
(NOT EXISTS(SELECT 1 FROM tmpMEMBER INNER_MEMBER
WHERE (INNER_MEMBER.QUARTER = AACT_QUARTER) AND
(INNER_MEMBER.SSN = tmpMEMBER.SSN))) AND
(PATIENT.EXIT_DATE BETWEEN ABEGIN AND AEND);
INSERT INTO RESULTGROUP VALUES (
APREV_QUARTER, AACT_QUARTER, varACT_GROUP_DEFINITION, 'EXIT', varSSN_COUNT, CURRENT_DATE);
--Counts new patients in this group (they weren't in any of the groups in the previous quarter)
SELECT COUNT(DISTINCT SSN)
INTO varSSN_COUNT
FROM tmpMEMBER
INNER JOIN tmpGROUP ON tmpMEMBER.GROUP_ID = tmpGROUP.GROUP_ID
WHERE (tmpMEMBER.QUARTER = AACT_QUARTER) AND
(tmpGROUP.GROUP_DEFINITION = varACT_GROUP_DEFINITION) AND
(NOT EXISTS(SELECT 1 FROM tmpMEMBER INNER_MEMBER
WHERE (INNER_MEMBER.QUARTER = APREV_QUARTER) AND
(INNER_MEMBER.SSN = tmpMEMBER.SSN)));
INSERT INTO RESULTGROUP VALUES (
APREV_QUARTER, AACT_QUARTER, 'NEW', varACT_GROUP_DEFINITION, varSSN_COUNT, CURRENT_DATE);
END LOOP;
CLOSE CUR_GROUP_OUTER;
END;
