Merge and aggregate datasets: Difference between revisions

Line 802:

#include "dbstruct.ch"

// SQL-like INSERT command (note the variable number of arguments)

&& SQL-like INSERT command (note the variable number of arguments)

#xcommand INSERT INTO <table> ( <uField1>[, <uFieldN> ] ) VALUES ( <uVal1>[, <uValN> ] ) => ;

<table>->(dbAppend()); <table>-><uField1> := <uVal1> [; <table>-><uFieldN> := <uValN> ]

Line 811:

SET DATE FORMAT "yyyy-mm-dd"

// create and population the patient table

&& create and population the patient table

pStruct := {{"patient_id", "n", 8, 0}, {"lastname", "c", 10, 0 }}

dbCreate( "patient", pStruct,, .T., "patient" )

Line 820:

INSERT INTO patient (patient_id, lastname) VALUES (5005, "Kurtz")

// create and population the visit table

&& create and population the visit table

vStruct := {{"patient_id", "n", 8, 0}, {"visit_date", "d", 10, 0}, {"score", "n", 8, 1}}

dbCreate( "visit", vStruct,, .T., "visit" )

Line 833:

INDEX ON patient_id TO visit_id

// create the result table

&& create the result table

rStruct := { {"patient_id", "n", 8, 0}, {"n", "i", 8, 0}, {"sum_score", "n", 8, 1}, ;

{"avg_score", "n", 8, 1}, {"max_date", "d", 10, 0}}

Line 839:

SELECT visit

// populate the result table

&& populate the result table

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, ctod("19000101")} && initial values

// within a group:

&& within a group:

DO WHILE ! Eof() .AND. xCurId == patient_id

aAgg := {1+aAgg[1], score+aAgg[2], max(visit_date, aAgg[3])} // update

aAgg := {1+aAgg[1], score+aAgg[2], max(visit_date, aAgg[3])} && update

SKIP // skip to next row

SKIP && skip to next row

ENDDO