Merge and aggregate datasets: Difference between revisions

Content added Content deleted
(Harbour task - initial implementation)
Line 802:
#include "dbstruct.ch"
 
//&& 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
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
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
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
DO WHILE ! Eof()
 
xCurId := patient_id //&& grouping variable
aAgg := {0, 0.0, ctod("19000101")} //&& initial values
 
//&& within a group:
DO WHILE ! Eof() .AND. xCurId == patient_id
aAgg := {1+aAgg[1], score+aAgg[2], max(visit_date, aAgg[3])} //&& update
SKIP //&& skip to next row
ENDDO
 
Retrieved from "https://rosettacode.org/wiki/Merge_and_aggregate_datasets"