AnsweredAssumed Answered

Calculate most recent non-null value?

Question asked by todfiste on Nov 20, 2018
Latest reply on Nov 21, 2018 by todfiste

I have a client table and related sessions table.  The sessions table contains a date field called MyDate and a field called ZZZ that holds a numeric value. I want to have a calculated field in the client table (LastZZZ) that contains the value of field ZZZ for the most recent related sessions record  (based on MyDate) where ZZZ is not null. So

 

Client

Name

LastZZZ

Joe

5

 

 

 

 

 

Sessions

MyDate

ZZZ

1/2/17

8

1/3/17

5

1/4/17

 

 

 

I have been able to do this using ExecuteSQL:

 

ExecuteSQL ( "select S.ZZZ from Sessions S

join Client C on S.ClientID = C.ClientID

where S.Date1 = (select max (S.MyDate) from Sessions S

join Client C on S.ClientID = C.ClientID

where S.ZZZ is not null and C.ClientID = ?)

and C.ClientID = ?" ;

"" ; "" ; Client::ClientID ; Client::ClientID)

 

 

However, it slows everything way down, even though the Sessions table only has about 800 records.

 

Can anyone suggest a better way to get the result for LastZZZ?

 

BTW, I also modified the ExecuteSQL this way, but it seems to work exactly the same:

 

ExecuteSQL ( "select S.ZZZ from Sessions S

join Client C on S.ClientID = C.ClientID

where S.Date1 = (select max (S2.MyDate) from Sessions S2

join Client C on S2.ClientID = C.ClientID

where S2.ZZZ is not null and C.ClientID = ?)

and C.ClientID = ?" ;

"" ; "" ; Client::ClientID ; Client::ClientID)

Outcomes