Merge and aggregate datasets: Difference between revisions
Harbour: simplify example using SET RELATION TO
(Harbour: simplify example using SET RELATION TO) |
|||
Line 832:
INSERT INTO visit (patient_id, visit_date, score) VALUES (1001, ctod("2020-11-19"), 5.3)
INDEX ON patient_id TO visit_id
&& create the result table
rStruct := { {"patient_id", "n", 8
{"n", "i", 8, 0}, {"sum_score", "n", 8, 1}, ;
{"avg_score", "n", 8, 1}, {"max_date", "d", 10, 0}}
dbCreate("report", rStruct,, .T., "report")
SELECT patient▼
DO WHILE ! Eof()
xCurId := patient_id && grouping variable
▲ SELECT visit
aAgg := {1+aAgg[1], score+aAgg[2], max(visit_date, aAgg[3])} && update
▲ aAgg := {0, 0.0, ctod("19000101")} && initial values
▲ DO WHILE ! Eof() .AND. xCurId == patient_id
▲ ENDDO
▲ INSERT INTO report (patient_id, lastname, n, sum_score, avg_score, max_date) ;
▲ SKIP
ENDDO
SELECT report
INDEX ON patient_id TO report_id
▲ SELECT patient
SET RELATION TO patient_id INTO report
? "NUM", "PATIENT_ID", "LASTNAME", "N", "SUM_SCORE", "AVG_SCORE", "MAX_DATE"
LIST patient_id, lastname, report->n, report->sum_score, report->avg_score, report->max_date
RETURN</syntaxhighlight>
| |||