rosetta.dl

// http://rosettacode.org/wiki/Merge_and_aggregate_datasets#Datalog

#include "datetime.dl"
#define NaN to_float("NaN")

.decl Patient(id:number, lastname:symbol)
.decl Visit(id:number, date:symbol, score:float)
.decl MissingDates(x:timestamp)
.decl SummaryDates(id:number, lastname:symbol, last_date:symbol)
.decl SummaryScores(id:number, lastname:symbol, score_sum:float, score_mean:float)
 
Patient(1001,"Hopper").
Patient(4004,"Wirth").
Patient(3003,"Kemeny").
Patient(2002,"Gosling").
Patient(5005,"Kurtz").
 
Visit(2002,"2020-09-10",6.8).
Visit(1001,"2020-09-17",5.5).
Visit(4004,"2020-09-24",8.4).
Visit(2002,"2020-10-08",NaN).
Visit(1001,"",6.6).
Visit(3003,"2020-11-12",NaN).
Visit(4004,"2020-11-05",7.0).
Visit(1001,"2020-11-19",5.3).
 
MissingDates(@from_date("")) :- true.

SummaryDates(id, lastname, last_date) :- 
     Patient(id,lastname),
     last_timestamp = max ts: {Visit(id, date, _), ts = @from_date(date), !MissingDates(ts)},
     Visit(id, last_date, _), last_timestamp = @from_date(last_date). 
SummaryScores(id, lastname, score_sum, score_mean) :- 
     Patient(id,lastname),
     score_sum = sum score: {Visit(id, _, score), score != NaN},
     score_mean = mean score: {Visit(id, _, score), score != NaN}.
.output SummaryDates
.output SummaryScores