Calculate Age as on date

[jiwaniakbiar]

I have 3 sql tables

MProgram, MParticipant and MProgramParticipant. I want to add new calculated column ParticipantAgeAttended using ProgramStartDate from MProgram and DateOfBirth from MParticipant.

load
ProgramID,
ProgramCode,
ProgramStartDate;

SELECT
ProgramID,
ProgramCode,
ProgramStartDate
FROM xxxx.xxx.MProgram;

load
ParticipantID,
FirstName,
LastName,
DateOfBirth;

SELECT
ParticipantID,
FirstName,
LastName,
DateOfBirth
FROM xxxx.xxx.MParticipant;

LOAD ProgramCenterId as ProgramEventID,
ParticipantID,
age(ProgramStartDate,DateOfBirth) as ParticipantAgeAttended;

[MProgramParticipant]:
SELECT ProgramCenterId,
ParticipantID
FROM xxxx.xxx.MProgramParticipant;

[jiwaniakbiar]

Reposting with some edits:

I have 3 sql tables

MProgram, MParticipant and MProgramParticipant. I want to add new calculated column ParticipantAgeAttended using ProgramStartDate from MProgram and DateOfBirth from MParticipant.

load
ProgramID  as ProgramEventID,
ProgramCode,
ProgramStartDate;

SELECT
ProgramID,
ProgramCode,
ProgramStartDate
FROM xxxx.xxx.MProgram;

load
ParticipantID,
FirstName,
LastName,
DateOfBirth;

SELECT
ParticipantID,
FirstName,
LastName,
DateOfBirth
FROM xxxx.xxx.MParticipant;

LOAD ProgramCenterId as ProgramEventID,
ParticipantID,
age(ProgramStartDate,DateOfBirth) as ParticipantAgeAttended;

[MProgramParticipant]:
SELECT ProgramCenterId,
ParticipantID
FROM xxxx.xxx.MProgramParticipant;

[JordyWegman · Accepted solution]

HI,

Try this:

Table1:
load
ProgramID  as ProgramEventID,
ProgramCode,
ProgramStartDate
;
SELECT
ProgramID,
ProgramCode,
ProgramStartDate
FROM xxxx.xxx.MProgram;

Left Join(Table1)
LOAD
   *
;
LOAD ProgramCenterId as ProgramEventID,
ParticipantID,
;
[MProgramParticipant]:
SELECT ProgramCenterId,
ParticipantID
FROM xxxx.xxx.MProgramParticipant;

Left Join(Table1)

load
ParticipantID,
FirstName,
LastName,
DateOfBirth;

SELECT
ParticipantID,
FirstName,
LastName,
DateOfBirth
FROM xxxx.xxx.MParticipant;

FinalTable:
Load
  ProgramEventID,
  ProgramCode,
  ProgramStartDate,
  ParticipantID,
  FirstName,
  LastName,
  DateOfBirth
  Age(ProgramStartDate,DateOfBirth) as ParticipantAgeAttended
Resident from Table1;

Drop table Table1;

Jordy

Climber

[jiwaniakbiar]

Thanks. Just to clarify, we are merging all the 3 tables and then creating 4rth table which has calculated field. then dropping the big merged table. right?

[JordyWegman]

Exactly that! Good luck!

Jordy

Climber