How to perform MAX date in data entity

(1) Share

Report

Posted on by Alex C

Hello,

I am attempting to create a data entity with the SysUserLog table to export the latest login date for a user (createdDateTime field). I am unsure how to perform the MAX date with a data entity to represent the following SQL query:

select UserId, Max(createdDateTime) AS 'LastLogin'
from SYSUSERLOG
group by UserId

Here is how I made the structure of the entity:

Is there a range I can apply for the MAX(createdDateTime) or should I go via a SysComputedColumn?

Thanks

All responses (4)

Answers (1)

Andrew Baker Idahoan 7 on at

Like (0)

Report

How to perform MAX date in data entity

**EDIT**
Solved: I had to create a data view with the aggregation and then use that view in the entity instead of sysuserlog table itself.

**Original:
I'm trying to do the exact same thing, (SysUserLog and all), but Aggregation property is not available.

What am I missing?
Verified answer

Layan Jwei 7,820 Super User 2025 Season 1 on at

Like (2)

Report

How to perform MAX date in data entity

Hi Alex,

Create a view with SysUserLog table. Add the field UserId and CreatedDateTime.
Put userId in the groupBy node -- just as u did in the entity

go to the createdDateTime field and change the aggregation property to Max.

Now go to your entity and add this view as a datasource instead of the table.

If you go to SQL, this view will give you this query:
SELECT MAX(CREATEDDATETIME) AS CREATEDDATETIME1, USERID, PARTITION, 1010 AS RECID
FROM dbo.SYSUSERLOG AS T1
GROUP BY USERID, PARTITION

Which is what you exactly want :)

Thanks,
Layan Jweihan
Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future
Alex C 84 on at

Like (0)

Report

How to perform MAX date in data entity

@Layan Jwei
Do you have an example of what you mean? Not sure I understand...

Thanks
Layan Jwei 7,820 Super User 2025 Season 1 on at

Like (1)

Report

How to perform MAX date in data entity

Hi,

Did you check the aggregate property on the field in the entity where you can choose "Max" and see if it helps?