Merge and aggregate datasets: Difference between revisions
Content added Content deleted
(Harbour: bug fixes) |
|||
Line 820: | Line 820: | ||
pStruct := {{"patient_id", "n", 8, 0}, {"lastname", "c", 10, 0 }} |
pStruct := {{"patient_id", "n", 8, 0}, {"lastname", "c", 10, 0 }} |
||
dbCreate( "patient", pStruct,, .T., "patient" ) |
dbCreate( "patient", pStruct,, .T., "patient" ) |
||
INSERT INTO patient (patient_id, lastname) VALUES (1001, "Hopper"), ( |
INSERT INTO patient (patient_id, lastname) VALUES (1001, "Hopper"), (4004, "Wirth"), ; |
||
(3003, "Kemeny"), ( |
(3003, "Kemeny"), (2002, "Gosling"), (5005, "Kurtz") |
||
INDEX ON patient_id TO pat_id |
INDEX ON patient_id TO pat_id |
||
Line 828: | Line 828: | ||
dbCreate( "visit", vStruct,, .T., "visit" ) |
dbCreate( "visit", vStruct,, .T., "visit" ) |
||
INSERT INTO visit (patient_id, visit_date, score) VALUES (2002, ctod("2020-09-10"), 6.8) |
INSERT INTO visit (patient_id, visit_date, score) VALUES (2002, ctod("2020-09-10"), 6.8) |
||
INSERT INTO visit (patient_id, visit_date, score) VALUES (1001, ctod("2020-09- |
INSERT INTO visit (patient_id, visit_date, score) VALUES (1001, ctod("2020-09-17"), 5.5) |
||
INSERT INTO visit (patient_id, visit_date, score) VALUES (4004, ctod("2020-09-24"), 8.4) |
INSERT INTO visit (patient_id, visit_date, score) VALUES (4004, ctod("2020-09-24"), 8.4) |
||
INSERT INTO visit (patient_id, visit_date) VALUES (2002, ctod("2020-10-08")) |
INSERT INTO visit (patient_id, visit_date) VALUES (2002, ctod("2020-10-08")) |
||
INSERT INTO visit (patient_id, score) VALUES (1001, 6.6) |
INSERT INTO visit (patient_id, score) VALUES (1001, 6.6) |
||
INSERT INTO visit (patient_id, visit_date) VALUES (3003, ctod("2020-11-12")) |
INSERT INTO visit (patient_id, visit_date) VALUES (3003, ctod("2020-11-12")) |
||
INSERT INTO visit (patient_id, visit_date, score) VALUES (4004, ctod("2020-11- |
INSERT INTO visit (patient_id, visit_date, score) VALUES (4004, ctod("2020-11-05"), 7.0) |
||
INSERT INTO visit (patient_id, visit_date, score) VALUES (1001, ctod("2020-11-19"), 5.3) |
INSERT INTO visit (patient_id, visit_date, score) VALUES (1001, ctod("2020-11-19"), 5.3) |
||
INDEX ON patient_id TO visit_id |
INDEX ON patient_id TO visit_id |
||
Line 846: | Line 846: | ||
DO WHILE ! Eof() |
DO WHILE ! Eof() |
||
xCurId := patient_id && grouping variable |
xCurId := patient_id && grouping variable |
||
aAgg := {0, 0.0, ctod("19000101")} && initial values |
aAgg := {0, 0, 0.0, ctod("19000101")} && initial values |
||
DO WHILE ! Eof() .AND. xCurId == patient_id |
DO WHILE ! Eof() .AND. xCurId == patient_id |
||
aAgg := {1+aAgg[1], iif(score==0.0,aAgg[2],1+aAgg[2]), score+aAgg[3], max(visit_date, aAgg[4])} && update |
|||
SKIP |
|||
ENDDO |
ENDDO |
||
INSERT INTO report (patient_id, n, sum_score, avg_score, max_date) ; |
INSERT INTO report (patient_id, n, sum_score, avg_score, max_date) ; |
||
VALUES (xCurId, aAgg[1], aAgg[ |
VALUES (xCurId, aAgg[1], aAgg[3], aAgg[3]/aAgg[2], aAgg[4]) |
||
ENDDO |
ENDDO |
||
Line 868: | Line 868: | ||
<pre>NUM PATIENT_ID LASTNAME N SUM_SCORE AVG_SCORE MAX_DATE |
<pre>NUM PATIENT_ID LASTNAME N SUM_SCORE AVG_SCORE MAX_DATE |
||
1 1001 Hopper 3 17.4 5.8 2020-11-19 |
1 1001 Hopper 3 17.4 5.8 2020-11-19 |
||
4 2002 Gosling 2 6.8 6.8 2020-10-08 |
|||
3 3003 Kemeny 1 0.0 0.0 2020-11-12 |
3 3003 Kemeny 1 0.0 0.0 2020-11-12 |
||
2 4004 Wirth 2 15.4 7.7 2020-11-05 |
|||
5 5005 Kurtz 0 0.0 0.0 - - </pre> |
5 5005 Kurtz 0 0.0 0.0 - - </pre> |
||